x

Bulk Insert Data (Excel File)

Can you please advise me which is the best method of importing data into SQL from Excel (2007), Bulk Insert or OpenRowSet. Currently I've tried both and neither is working and before I continued to battle I thought I'd see which is the best option.

Many thanks

more ▼

asked May 10, 2011 at 06:51 AM in Default

avatar image

Mrs_Fatherjack
5.1k 64 66 74

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

2 answers: sort voted first

You can try to Install the 2007 Office System Driver: Data Connectivity Components. Then you should be able to

 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
 'Excel 12.0;Database=C:\pathToExcelFile.xlsx;HDR=Yes', 'Select * from [Sheet1$]')

Eventualy HDR=No if there are no columns headers.

more ▼

answered May 10, 2011 at 12:09 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

That is pretty slick.

May 10, 2011 at 12:43 PM Tim
(comments are locked)
10|1200 characters needed characters left

If it were me I would use SSIS and use an EXCEL Source and handle the import there. You could also use a data conversion task to handle any data manipulation that might be needed.

more ▼

answered May 10, 2011 at 06:55 AM

avatar image

Tim
40.4k 39 84 166

So would I as a preference, unfortunately that's not an option as it's a SQL 2000 database, it's either code or DTS, have opted for the code option curently.

May 10, 2011 at 06:58 AM Mrs_Fatherjack

Well that is no fun. How much data is in the Excel file? I am lucky that in my environment I would still use SSIS and just use my OLE Destination as my 2000 server. I have a dedicated ETL server that is 2008 R2 that I use to connect to everything from flat files, Excel, SQL, Oracle, and darn near everything in between.

May 10, 2011 at 07:28 AM Tim

Not a lot, maybe 1000 rows, the problem is that I need to write a process that can easily be utilised for many sources of data and many destinations so it's getting a bit complex.

May 10, 2011 at 07:33 AM Mrs_Fatherjack

Sounding like an even bigger reason to do this in SSIS where you can configure variables and make it much more dynamic. With that small of a data set there should be any issues with having the process run on another server with SSIS. Is that an option for you?

May 10, 2011 at 07:49 AM Tim

I put forward the proposal that we do it in SSIS on a 2008 server and just push the data into the 2000 server but essentially unless I can put forward a really good case it needs to all stay within SQL 2000.

May 10, 2011 at 07:54 AM Mrs_Fatherjack
(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:

x987
x140
x38
x14

asked: May 10, 2011 at 06:51 AM

Seen: 11272 times

Last Updated: May 10, 2011 at 06:51 AM

Copyright 2016 Redgate Software. Privacy Policy