question

rsirinek avatar image
rsirinek asked

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
sql-server-2008sqlexcel
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

swirl80 avatar image
swirl80 answered
It'll probably be down to incorrect/out of date drivers installed. [This link][1] may help.... [1]: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/33436d82-085c-43e4-b991-a2d0d701c8fc
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

not entirely ;)
0 Likes 0 ·
stevenbacks avatar image
stevenbacks answered
It's possible with repair corrupted word file [ http://www.repairword.wordrepairtoolbox.com][1]/ [1]: http://www.repairword.wordrepairtoolbox.com
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.