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 '11 at 06:51 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 57 62 66

(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 '11 at 12:09 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

That is pretty slick.
May 10 '11 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 '11 at 06:55 AM

Tim gravatar image

Tim
35.5k 32 40 138

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 '11 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 '11 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 '11 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 '11 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 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x672
x108
x30
x9

asked: May 10 '11 at 06:51 AM

Seen: 7022 times

Last Updated: May 10 '11 at 06:51 AM