x

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
go
reconfigure 
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure 
go

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

rsirinek gravatar image

rsirinek
30 2 2 3

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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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

swirl80 gravatar image

swirl80
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

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

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

http://www.repairword.wordrepairtoolbox.com/

more ▼

answered Aug 05, 2013 at 08:29 PM

stevenbacks gravatar image

stevenbacks
0

(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:

x1850
x738
x117

asked: Aug 10, 2012 at 03:51 PM

Seen: 2064 times

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