Bulk Insert from Excel file

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 morganBill,
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=...cel%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 Exce
l
>file?
>Thanks in advance for your help ...
>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 us
e
standardized names.
Thanks again ...
"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=...cel%2Bsqlserver
> Also, MSDN has a little more information than Books Online about this.
> -- Steve Kass
> -- Drew University
>
>
> bill_morgan wrote:
>
>|||PS - suggestion_2 worked perfectly...! Now for the loop ...
"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=...cel%2Bsqlserver
> Also, MSDN has a little more information than Books Online about this.
> -- Steve Kass
> -- Drew University
>
>
> bill_morgan wrote:
>
>