Bulk Insert from Excel file

21 Oct 2005 2:42 AM

bill_morgan

Hi ...

Having trouble getting Bulk Insert to work with .xls file.

Following code works fine for text file:
BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
with (fieldterminator ='\t', datafiletype ='char')

Anybody know what changes I need to make to above code so it works for Excel
file?

Thanks in advance for your help ...

bill morgan

21 Oct 2005 2:55 AM

Steve Kass

Bill,

BULK INSERT cannot be used to import data from Excel.
You can use OpenRowset, or you can set up a linked server
with an Excel provider.

Here is one way, assuming ReportData is a named range of
the Excel sheet.

insert into MyTable
select <columns> from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Report.xls',
'select * from ReportData'
) -- will be called F1, F2, F3, ... if there are no headers.

If it's just a sheet, try

insert into MyTable
select <columns> from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Report.xls',
'select * from [Sheet1$]'
)

The $ is added to the sheet name, which itself has no $ character.

There are a few other things to watch out for, like type conversion,
and you can find other examples in these threads if the ones above
don't help.

http://groups.google.com/groups?hl=en&q=kass%2Bexcel%2Bsqlserver

Also, MSDN has a little more information than Books Online about this.

-- Steve Kass
-- Drew University

bill_morgan wrote:

Show quote

>Hi ...
>
>Having trouble getting Bulk Insert to work with .xls file.
>
>Following code works fine for text file:
>BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
>with (fieldterminator ='\t', datafiletype ='char')
>
>Anybody know what changes I need to make to above code so it works for Excel
>file?
>
>Thanks in advance for your help ...
>
>bill morgan
>
>
>
>
>
>

21 Oct 2005 3:35 AM

bill_morgan

Steve,

Great stuff. Thank you. Since I have over 200 Excel files to import (but
only one sheet per file) I'm setting up your suggestion_2 below. If I can
get it to work for one, then I'm sure I can loop through others, as files use
standardized names.

Thanks again ...

Show quote

"Steve Kass" wrote:

> Bill,
>
> BULK INSERT cannot be used to import data from Excel.
> You can use OpenRowset, or you can set up a linked server
> with an Excel provider.
>
> Here is one way, assuming ReportData is a named range of
> the Excel sheet.
>
> insert into MyTable
> select <columns> from OpenRowset(
>   'Microsoft.Jet.OLEDB.4.0',
>   'Excel 8.0;Database=c:\Report.xls',
>   'select * from ReportData'
> ) -- will be called F1, F2, F3, ... if there are no headers.
>
> If it's just a sheet, try
>
> insert into MyTable
> select <columns> from OpenRowset(
>   'Microsoft.Jet.OLEDB.4.0',
>   'Excel 8.0;Database=c:\Report.xls',
>   'select * from [Sheet1$]'
> )
>
> The $ is added to the sheet name, which itself has no $ character.
>
> There are a few other things to watch out for, like type conversion,
> and you can find other examples in these threads if the ones above
> don't help.
>
> http://groups.google.com/groups?hl=en&q=kass%2Bexcel%2Bsqlserver
>
> Also, MSDN has a little more information than Books Online about this.
>
> -- Steve Kass
> -- Drew University
>
>
>
>
> bill_morgan wrote:
>
> >Hi ...
> >
> >Having trouble getting Bulk Insert to work with .xls file.
> >
> >Following code works fine for text file:
> >BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
> >with (fieldterminator ='\t', datafiletype ='char')
> >
> >Anybody know what changes I need to make to above code so it works for Excel
> >file?
> >
> >Thanks in advance for your help ...
> >
> >bill morgan
> >
> >
> >
> >
> >
> >
>

21 Oct 2005 3:38 AM

bill_morgan

PS - suggestion_2 worked perfectly...! Now for the loop ...

Show quote

"Steve Kass" wrote:

> Bill,
>
> BULK INSERT cannot be used to import data from Excel.
> You can use OpenRowset, or you can set up a linked server
> with an Excel provider.
>
> Here is one way, assuming ReportData is a named range of
> the Excel sheet.
>
> insert into MyTable
> select <columns> from OpenRowset(
>   'Microsoft.Jet.OLEDB.4.0',
>   'Excel 8.0;Database=c:\Report.xls',
>   'select * from ReportData'
> ) -- will be called F1, F2, F3, ... if there are no headers.
>
> If it's just a sheet, try
>
> insert into MyTable
> select <columns> from OpenRowset(
>   'Microsoft.Jet.OLEDB.4.0',
>   'Excel 8.0;Database=c:\Report.xls',
>   'select * from [Sheet1$]'
> )
>
> The $ is added to the sheet name, which itself has no $ character.
>
> There are a few other things to watch out for, like type conversion,
> and you can find other examples in these threads if the ones above
> don't help.
>
> http://groups.google.com/groups?hl=en&q=kass%2Bexcel%2Bsqlserver
>
> Also, MSDN has a little more information than Books Online about this.
>
> -- Steve Kass
> -- Drew University
>
>
>
>
> bill_morgan wrote:
>
> >Hi ...
> >
> >Having trouble getting Bulk Insert to work with .xls file.
> >
> >Following code works fine for text file:
> >BULK INSERT MyTable FROM 'C:\bulkinsert.txt'
> >with (fieldterminator ='\t', datafiletype ='char')
> >
> >Anybody know what changes I need to make to above code so it works for Excel
> >file?
> >
> >Thanks in advance for your help ...
> >
> >bill morgan
> >
> >
> >
> >
> >
> >
>