Bulk Insert from Excel
in [Programming SQL]
|
Prev: Dateformat
Next: Reading .LDF
From: Manuel on 28 Jul 2006 06:48 I´m new to this subject. As a matter of a fact, the first time I´m trying this. I need to run a BULK INSERT query where the datasource is an Excel file and the destination is a table in SQL 2000 Server. After looking at the Books on Line, I´ve tried first: BULK INSERT Database.dbo.Table_tbl FROM 'c:\files\File.xls' It returns an error "column 2 truncated". I need directions on this subject. Thanks -- Manuel
From: Steve Kass on 28 Jul 2006 07:28 Manuel, BULK INSERT is only for text files. Usually, files with the extension .xls are not text files, but Excel format files that are in a more complicated format than text. In order to import an Excel file to SQL Server, use OPENQUERY or OPENROWSET. Something like this is what you want: insert into D.dbo.T select * from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\excel\Workbook.xls;HDR=YES;IMEX=1' ,'select * from [SheetName$]' ) There are a number of issues regarding type guessing, column names, and importing regions of a worksheet, some of which are answered in previous news threads: http://groups.google.com/groups/search?q=kass+sqlserver+excel+openrowset It might also be the case that you can obtain your Excel files in comma-separated value format (CSV). If you can, then you should be able to use BULK INSERT with a format file or appropriate choices of delimiters. -- Steve Kass -- Drew University -- http://www.stevekass.com -- C50C2FA7-D789-49F8-A919-ABE0A32D754D Manuel wrote: >I´m new to this subject. As a matter of a fact, the first time I´m trying >this. >I need to run a BULK INSERT query where the datasource is an Excel file and >the destination is a table in SQL 2000 Server. >After looking at the Books on Line, I´ve tried first: >BULK INSERT Database.dbo.Table_tbl > FROM 'c:\files\File.xls' >It returns an error "column 2 truncated". >I need directions on this subject. >Thanks > > >
From: Manuel on 28 Jul 2006 07:39 Steve, Thank you so much for your help. I´ll be studying your references, trying your suggestion or trying the csv file. I´m sure I´ll get back to you with more questions. Thanks in advance. -- Manuel "Steve Kass" wrote: > Manuel, > > BULK INSERT is only for text files. Usually, files with the > extension .xls are not text files, but Excel format files that are > in a more complicated format than text. > > In order to import an Excel file to SQL Server, use OPENQUERY > or OPENROWSET. Something like this is what you want: > > insert into D.dbo.T > select * from OpenRowSet( > 'Microsoft.Jet.OLEDB.4.0', > 'Excel 8.0;Database=c:\excel\Workbook.xls;HDR=YES;IMEX=1' > ,'select * from [SheetName$]' > ) > > There are a number of issues regarding type guessing, column names, > and importing regions of a worksheet, some of which are answered in > previous news threads: > > http://groups.google.com/groups/search?q=kass+sqlserver+excel+openrowset > > It might also be the case that you can obtain your Excel files in > comma-separated value format (CSV). If you can, then you should > be able to use BULK INSERT with a format file or appropriate > choices of delimiters. > > -- Steve Kass > -- Drew University > -- http://www.stevekass.com > -- C50C2FA7-D789-49F8-A919-ABE0A32D754D > > Manuel wrote: > > >I´m new to this subject. As a matter of a fact, the first time I´m trying > >this. > >I need to run a BULK INSERT query where the datasource is an Excel file and > >the destination is a table in SQL 2000 Server. > >After looking at the Books on Line, I´ve tried first: > >BULK INSERT Database.dbo.Table_tbl > > FROM 'c:\files\File.xls' > >It returns an error "column 2 truncated". > >I need directions on this subject. > >Thanks > > > > > > >
From: Manuel on 28 Jul 2006 08:03 Steve, I´ve tried this: insert into iterartis.dbo.projectoItems_tbl select * from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1' ,'select * from [Folha1$]' ) It returned this error: Insert Error: Column name or number of supplied values does not match table definition. How do I accomplish this match. Thanks again -- Manuel "Steve Kass" wrote: > Manuel, > > BULK INSERT is only for text files. Usually, files with the > extension .xls are not text files, but Excel format files that are > in a more complicated format than text. > > In order to import an Excel file to SQL Server, use OPENQUERY > or OPENROWSET. Something like this is what you want: > > insert into D.dbo.T > select * from OpenRowSet( > 'Microsoft.Jet.OLEDB.4.0', > 'Excel 8.0;Database=c:\excel\Workbook.xls;HDR=YES;IMEX=1' > ,'select * from [SheetName$]' > ) > > There are a number of issues regarding type guessing, column names, > and importing regions of a worksheet, some of which are answered in > previous news threads: > > http://groups.google.com/groups/search?q=kass+sqlserver+excel+openrowset > > It might also be the case that you can obtain your Excel files in > comma-separated value format (CSV). If you can, then you should > be able to use BULK INSERT with a format file or appropriate > choices of delimiters. > > -- Steve Kass > -- Drew University > -- http://www.stevekass.com > -- C50C2FA7-D789-49F8-A919-ABE0A32D754D > > Manuel wrote: > > >I´m new to this subject. As a matter of a fact, the first time I´m trying > >this. > >I need to run a BULK INSERT query where the datasource is an Excel file and > >the destination is a table in SQL 2000 Server. > >After looking at the Books on Line, I´ve tried first: > >BULK INSERT Database.dbo.Table_tbl > > FROM 'c:\files\File.xls' > >It returns an error "column 2 truncated". > >I need directions on this subject. > >Thanks > > > > > > >
From: Steve Kass on 28 Jul 2006 09:06
There is apparently a mismatch between the columns of the source and destination. Look at the column lists of the table and the Excel sheet: select top 1 * from iterartis.dbo.projectoItems_tbl select top 1 * from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1' ,'select * from [Folha1$]' ) You can probably solve this problem by specifying the column names explicitly. For example, you might have to write: insert into iterartis.dbo.projectoItems_tbl(columnname1,columnname2, ...) select somecolumn, othercolumn, thirdcolumn, ... from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1' ,'select * from [Folha1$]' ) SK Manuel wrote: >Steve, > >I´ve tried this: > >insert into iterartis.dbo.projectoItems_tbl >select * from OpenRowSet( > 'Microsoft.Jet.OLEDB.4.0', > 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1' > ,'select * from [Folha1$]' >) > >It returned this error: > >Insert Error: Column name or number of supplied values does not match table >definition. > >How do I accomplish this match. > >Thanks again > > > |