x

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!

more ▼

asked May 18 at 08:51 PM in Default

avatar image

caoneill
11 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered May 18 at 09:45 PM

avatar image

Jeff Moden
2.3k 3 7 13

Thank you!

May 19 at 11:37 AM caoneill

@caoneil

If the SQL Server instance does not yet have the ACE drivers then install them from Microsoft Access DB Engine 2010 redistributable. 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".

May 21 at 02:10 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53

asked: May 18 at 08:51 PM

Seen: 48 times

Last Updated: May 21 at 02:10 AM

Copyright 2017 Redgate Software. Privacy Policy