question

caoneill avatar image
caoneill asked

Is it possible to bcp into a table multiple tabs of an excel file?

Trying to avoid using SSIS and just bcp in sheets of a workbook. Is it possible? Thank you!
bcp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered
***Not*** BCP or BULK INSERT for Spreadsheets. OPENROWSET using the "ACE" drivers (provided by MS). They can be "hard mapped" like most folks end up doing in SSIS or, with a bit of experimentation, can be auto-magically mapped to overcome the perennial problem of people adding columns, etc, etc. I'm not at home right now and so don't have access to the links I sometimes refer people to. I'll try to remember to come back to this sometime tonight.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you!
0 Likes 0 ·
@caoneil If the SQL Server instance does not yet have the ACE drivers then install them from [Microsoft Access DB Engine 2010 redistributable][1]. This is for ACE 12 provider. They also have 2016 version available for ACE 16 provider. Make sure that the 64 bit is installed for 64 bit SQL Server. There are some configuration steps: Allow Ad Hoc queries: exec sp_configure 'Ad Hoc Distributed Queries', 1; reconfigure with override; go Ensure that after the driver is installed on the instance, it is configured to "Allow in Process". You can set this option from SSMS via property pages of the provider. Ensure that the account you use to connect to the SQL Server has permissions to the temp folder of the network account used to run the SQL Server service. This part is not well documented, but is crucial if you don't want your openrowset queries to hang. When selecting data from Excel, the sheet name with dollar sign is used as the table name, so the data from Sheet1 is located in Sheet1$ "table". [1]: https://www.microsoft.com/en-us/download/details.aspx?id=13255
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.