Load excel files as tables into SQL server

I am being asked to load data for the data quality management team. Someone else is extracting the data from the application data source for me. I receive the data as .xls/.xlsx files or sometimes as a delimited file if there are more than 300k records.

So when anywhere from 20 to 60 files are handed over to me, I am expected to load the data in a 'few hours' to a half day. I only need to load each file to it's own table (that I need to create on the load). I have used the following methods to load the data and am looking for a most efficient method to turn-around the loading quickly...

  1. 32bit SSMS Import/Export tool 'Import and Export Data (32-bit)'

  2. VB Module in MS Access, this is slow. Background (the Excel Files are on a file server (UNC location) and whether I run the VB module from the virtual server or my local workstation it loads slowly compared to the 'Import and Export Data (32-bit)' tool in SSMS

The virtual server is 64 bit and does not have MS Excel installed for some reason. I was trying to use this:

 sp_configure 'show advanced options', 1
 sp_configure 'Ad Hoc Distributed Queries', 1

 SELECT * INTO dbo.test_table
 FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
 'Data Source=C:\Users\first.last-b\Desktop\File Name.xls;Extended Properties=EXCEL 5.0')...[Sheet1$];

... but I get this error,

 Msg 7308, Level 16, State 1, Line 2

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

What is the way to work around it? Is there a way without modding the install on the virtual server. In this communities experience what do you suggest to load data in an enterprise environment where Excel files need loaded as tables?

Virtual Server: Window Server Standard, SP2 64bit

My Workstation Vista Enterprise 64 bit

more ▼

asked Aug 10, 2012 at 03:51 PM in Default

avatar image

30 2 2 4

turned out work once i download the the delimited files to my computer, then opened and resaved them. don't know why though, so weird.

Aug 28, 2012 at 05:51 PM rsirinek
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

This issue sounds like is related to 32bit Excel drivers being used on a 64bit system. You'll need to switch one to match the other ie Excel 64bit installation or run it on a 32bit server I think.

more ▼

answered Aug 15, 2012 at 07:55 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

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

It'll probably be down to incorrect/out of date drivers installed.

This link may help....

more ▼

answered Aug 14, 2012 at 05:16 PM

avatar image

60 1

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

Jonathan Allen, as usual, is completely right about why OpenDataSource isn't working.

But if the goal is to load a large amount of data, you could consider using SSIS. It is designed specifically for that and works quite well. It is also highly scriptable and can be scripted through BIDS.

more ▼

answered Aug 28, 2012 at 06:27 PM

avatar image

15.6k 22 57 38

not entirely ;)

Aug 28, 2012 at 06:38 PM rsirinek
(comments are locked)
10|1200 characters needed characters left

It's possible with repair corrupted word file


more ▼

answered Aug 05, 2013 at 08:29 PM

avatar image


(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



Answers and Comments

SQL Server Central

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



asked: Aug 10, 2012 at 03:51 PM

Seen: 2512 times

Last Updated: Aug 06, 2013 at 02:48 AM

Copyright 2018 Redgate Software. Privacy Policy