Direct query to excel (AceOleDb.dll)

So, often you need to get excel data into SQL ‘in a hurry’ for analysis.  After you’ve done a base case analysis you can go about getting the data in a more ‘regular’ fashion.  What’s the fastest way to get an excel into SQL?  AceOleDb…that’s how.    See the distributed query below:

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0; Database= c:\PATH_TO_YOUR_EXCEL_FILE.xls’ ,’select * from [sheet1$]’)

If you have a ‘new’ box you may be lucky, and you can run the above code and it will just work.  But, if you’re the first to try this in your domain there can be a number of issues.

1st, note that if you’re operating from your own personal machine, but SSMS is connected to a remote server (a somewhat typical scenario) please pay close attention to the “path”.  The SQL is running on the remote box, so when you tell it to look on the “C” drive for a file, it looks on the server’s C drive.

Second, you may run into a number of errors that will need to be solved by your DBA running one or all of the following commands:

sp_configure ‘Ad Hoc Distributed Queries’, 1; GO

RECONFIGURE; GO

EXEC master.dbo.sp_Msset_oledb_prop N’Microsoft.ACE.OLEDB.12.0?, N’AllowInProcess’, 1

EXEC master.dbo.sp_Msset_oledb_prop N’Microsoft.ACE.OLEDB.12.0?, N’DynamicParameters’, 1

Those are the easy fixes.  One of the errors you may get is that your AceOleDB may not be ‘registered’.  If this is the case, you’ll need to install the MS Access Database Engine 2010 Redistributable, which you’ll find here.  Yes, that does say “MS Access”…and yes, done right, you’ll be able read Excel files having loaded the “MS Access” redistributable.  When your DBA installs the redistributable, note that he will have a choice 32 bit or 64 bit.  With today’s modern machines, your choice should normally be the 64 bit option.  Note that with 32 bit machines you have to choose 32 bit (duh).  But, you COULD install the 32 bit on a 64 bit machine…you just have to have a good reason…and that would be a different blog post.  We’re going to stick with the 64 bit option.

After installing the 64 bit AceOleDb.dll (that’s the primary result of doing the above install), you could check to verify what the DBA did.  Here’s how you know…you check C:\program files\Common files\Microsoft shared\OFFICEXX  (where XX corresponds to your version of office.)  If you find AceOleDb.dll in that folder, then SUCCESS.  You should be able to run the distributed query listed at the beginning of this article.

If, instead, you find AceOleDb.dll in C:\program files (x86)\Common files\Microsoft shared\OFFICEXX  your guy installed the 32 bit version.

SSMS doesn’t really care which version of the file is registered…it is ambidextrous.  But, other programs do care.  Specifically, your “Import Wizard”.  But that is also another post…for a later time.

About Steve Schneider

I was born (again) in June of 1983 when I came to know Jesus Christ as my saviour. You too, can know Him, and know Peace. We all have sinned, and fallen short of the grace of God. But, God demonstrates His love for us in this, that while we were still sinners, He died for us. Email me, and I can show you how to know Jesus. He is highly worth knowing!
This entry was posted in SSIS and tagged , , , , . Bookmark the permalink.

Leave a Reply...Really! I'll listen!