Best Practices to import excel data into sql server?

Question

Best Practices to import excel data into sql server?

Alan Kwan

SSCommitted

Points: 1722
More actions
July 27, 2012 at 1:27 pm

#264898

Hi all,
My sql server automatically updates daily from an excel source. What I have been doing is setup a scheduled import of excel data into a temp table, then under jobs, I use T-SQL to merge update this temp table into the actual table.
It works, but I find it having too many steps. What are some best practices out there?
I have attempted to use the OPENROWSET query, but it gives me this error (if someone can also solve this, it would be great):
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" --< this is all, nothing else.
Thanks,
Alan

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I have seen that error when EXCEL is not installed on the server you are using, or your OPENROWSET command is using the incorrect driver with a relatively new release of EXCEL which uses the "ace" driver.

What version of EXCEL are you attempting to use ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Alan Kwan SSCommitted Points: 1722 More actions · Answer 2

I am using Excel 2010, how should the query be edited?

So..the way I am importing and updating data is correct?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Here is the difference in the OPENROWSET command:

The latest version of EXCEL

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=d:\dasdgigs.xls',

'SELECT * FROM [dasdgigs$]')

For earlier version of EXCEL

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=yes', 'SELECT * FROM [Sheet1$a4:c]')

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Alan Kwan SSCommitted Points: 1722 More actions · Answer 4

Just tried the code you provided, still the same error.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database = C:\abc.eeee.xls', 'Select * FROM [Sheet1$]')

Same Error 🙁

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

Alan Kwan (7/27/2012)
Just tried the code you provided, still the same error.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database = C:\abc.eeee.xls', 'Select * FROM [Sheet1$]')
Same Error 🙁

Try using SSIS then. Its a simple transfer from xls to SQL 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

1. Install Office 2007/2012 Data Connectivity Components (http://www.microsoft.com/download/en/confirmation.aspx?id=23734)

2. Change the extension of your excel file to .xlsx

3. Enable "Ad Hoc Distributed Queries" on the server this is running on

4. Run: EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\DM list1.xlsx;HDR=Yes','SELECT * FROM [Sheet1$] WHERE [Sr No] IS NOT NULL ')

I was receiving the same errors as you, after enabling the "in process" for OLDDB, it works - Let me know how it goes for you.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

@lokesh - agree, SSIS is ideally the way to go for this type of process

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Vikrant S Patil SSCrazy Points: 2294 More actions · Answer 8

Please read the information listed on the following URL, this will help you in achieving your objective.

www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

you can also use SQLSERVERAGENT to schedule the package to execute as per your requirement.

demonfox SSCertifiable Points: 6289 More actions · Answer 9

Lokesh Vij (7/28/2012)

Alan Kwan (7/27/2012)
Just tried the code you provided, still the same error.
Try using SSIS then. Its a simple transfer from xls to SQL 🙂

not just the best, also the easiest way...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

demonfox (7/28/2012)

Lokesh Vij (7/28/2012)
Alan Kwan (7/27/2012)
Just tried the code you provided, still the same error.
Try using SSIS then. Its a simple transfer from xls to SQL 🙂
not just the best, also the easiest way...

I'm not sure how you can say that. The OPENROWSET command looks pretty easy to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 11

OP, have you tried my suggestion?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Alan Kwan SSCommitted Points: 1722 More actions · Answer 12

I tried the 4 steps you mentioned, and the error message changed this time.

The 32-bit OLE DB Provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

What to do now?

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 13

Download and use the 64-bit one

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 14

Any luck?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience