Retrieving Data From Excel

The purpose of this article is to demonstrate how to retrieve data from an Excel sheet and put it in a table in a SQL Server database.

Introduction

Anyone who’s ever used a computer for a significant amount of time has probably come into contact with Excel, the spreadsheet application part of the Microsoft Office suite. Its main purposes are to perform calculations and create charts and pivot tables for analysis.

But people have great imagination and invent new uses for it every day.  I’ve even seen it used as a picture album.  (Sorry dad, but I know you won’t be reading this anyway. :-) )  Ever since he had this specific YACI, or “Yet Another Computer Issue”, because his PC wasn’t powerful enough to open his 45 MB Excel file, uh, “picture collection”, he took some evening classes.  He’s now putting his Photoshopped pictures in PowerPoint…  Anyway, let’s get back on track now.

Another use, and the one that’s the subject of this article, is when Excel has been used as a database.  Come on, you know what I’m talking about, with the first row containing the column headers followed by possibly thousands of data rows.  The following screenshot contains an example, and is also the file that I will be using in this article.  I took all records from the Production.Product table in the AdventureWorks 2008R2 database and dumped them in Excel.

An Excel sheet used as a data store

At some point people will realize, either because someone told them or because they lost some data due to inattentiveness, that it wasn’t a really good idea to keep all that data in an Excel sheet.  And they’ll ask you to put it in a real database such as SQL Server.

That’s what I’m going to show you in the next paragraphs: how to import data from Excel into SQL Server.

Using OPENROWSET() To Query Excel Files

There are actually several different ways to achieve this.  In this article I will use the OPENROWSET() function.  This is a T-SQL function that can be used to access any OLE DB data source.  All you need is the right OLE DB driver.  The oldest version which I could confirm that contains this function is SQL Server 7.0, good enough to say that any version supports it.

My sample Excel files are located in C:\temp\.  This folder contains two files: Products.xls and Products.xlsx.  The first file is saved in the old format, Excel 97-2003, while the second file was saved from Excel 2010.  Both files contain the same data.  The sheet containing the list of products is called ProductList.

And here are the queries:

--Excel 2007-2010
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx',
    'SELECT * FROM [ProductList$]');

--Excel 97-2003
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\temp\Products.xls',
    'select * from [ProductList$]');

These queries are just returning the data from the Excel file into the Results window, when executed using the Management Studio.  To insert the data into a table, uncomment the INTO clause.  When uncommented, the statement retrieves the data from the Excel sheet and puts it into a newly-created local temporary table called #productlist.

Furthermore, the query assumes that the first row contains the header.  If that’s not the case, replace HDR=YES with HDR=NO.

Note: if you get an error message when running the query, look further down in this article.  I’ve covered a couple of them.

With the INTO clause uncommented and the query executed, the temporary table can now be queried just like any other table:

SELECT * FROM #productlist

What Type Is Your Data?

Let’s have a look if this method of using a SELECT INTO in combination with OPENROWSET and a temporary table is smart enough to interpret the correct data types of the data coming in.  Use the following command to describe the metadata of the temporary table:

USE tempdb;
GO
sp_help '#productlist';

Because a temporary table is stored in the tempdb, the sp_help command should be issued against that database.

Here’s the part of the output in which we’re interested:

The data types used when combining OPENROWSET with SELECT INTO

As you can see, anything that looks like text will be put in a field of type nvarchar(510) and anything that looks like a number (integers, floating-point numbers, datetime values, …) is put into a float(53).  Not a lot of intelligence there.  This is the result when no formatting was put on the cells in Excel.

As an experiment I’ve changed the format of some fields in the Excel file and then retried the SELECT INTO statement.  What did I change?  I identified ProductID as being a number without any decimals, changed StandardCost and ListPrice to a currency with four decimal digits and I changed SellStartDate and SellEndDate to a custom date/time format showing both date and time.

The effect on the table creation was not completely as I would have expected:

SELECT INTO with some field types changed

ProductID is still being stored into a float field, even though in Excel it’s defined as having no decimals.  And the datetime values are not recognized either.  Okay, I used a custom format there, so maybe it’s due to that.

It’s up to you of course how you use this method of importing the data.  You can put your records into a temporary table to process further, or you can create a table with the expected data types upfront and import the data directly into that one.

Some Possible Issues

Let’s cover some issues related to this method.

Enable ‘AD Hoc Distributed Queries’

The OPENROWSET() function expects that the ‘Ad Hoc Distributed Queries’ option is enabled on the server.  When that’s not the case you’ll see the following message:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

This is one of the advanced options.  To enable it you can use the following command:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

To get a good look at all the different settings, just run the sp_configure procedure without any parameters.

Note: if you’re not the administrator of the server, you should talk to the DBA who’s responsible before attempting this.

The File Needs To Be Closed

When the Excel file is not closed, you’ll end up with the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

So close the file and try the query again.

OLE DB Driver Not Installed

The OPENROWSET() function uses OLE DB, so it needs a driver for your data source, in this case for Excel.  If the right driver is not installed, you’ll see the following error (or similar, depends on the version used).

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

To solve the issue, install the right driver and try again.

How can you tell what drivers are installed?  Open up the ODBC Data Source Administrator window (Start > Run > type ODBCAD32.EXE and enter) and have a look in the Drivers tab.  The following screenshot (taken on a Dutch Windows XP) shows both the JET 4.0 driver for Excel 97-2003 and the fairly-new ACE driver for Excel 2007.

odbcad32.exe - ODBC Data Source Administrator

The drivers can be downloaded from the following pages on the Microsoft site:

Excel 97-2003 Jet 4.0 driver

Excel 2007 ACE driver – 12.00.6423.1000

Excel 2010 ACE driver (beta) – 14.00.4732.1000

Sidenote: the Excel 2010 driver is not supported on Windows XP, but I was able to query the 2010 Excel sheet using the 2007 driver.  I guess that this is the result of the Office Open XML standard which was introduced in Office 2007.

Driver backward-compatibility

The ACE drivers are backwards-compatible.  So the following queries are working perfectly:

--old Excel with new ACE driver - working query 1
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=YES;Database=C:\temp\Products.xls',
    'SELECT * FROM [ProductList$]');

--old Excel with new ACE driver - working query 2
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\temp\Products.xls',
    'SELECT * FROM [ProductList$]');

In other words, you won’t be needing that first link for the Jet driver.  For the full story have a look at this blog post by Adam Saxton of the CSS SQL Server Escalation Services team.

The 64-bit Story

So, what if you’re running a 64-bit OS?  I’ll start by saying that I had quite some issues getting OPENROWSET to work, but finally I managed it.  Following is a list of my attempts, each time with the resulting message.  And finally I’ll show you how I got it to work.  The problem was something really unexpected…

ACE 14 64-bit through SSMS

My main laptop is running Windows 7 64-bit, Office 2010 64-bit and SQL Server 2008 R2 64-bit.  So I installed the 64-bit version of the ACE 14 driver, which happens to be the first OLE DB driver for Excel that ships in 64-bit.  But when I execute my query I’m getting the following message:

Msg 7403, Level 16, State 1, Line 1

The OLE DB provider “Microsoft.ACE.OLEDB.14.0″ has not been registered.

Is this because SSMS ships only in 32-bit?  Maybe, but I’m not able to install the 32-bit driver.  It doesn’t allow me to because I’ve got Office in 64-bit installed.  The installer throws me the following error:

Microsoft Access database engine 2010 (beta) - You cannot install the 32-bit version of Access Database engine for Microsoft Office 2010 because you currently have 64-bit Office products installed...

ACE 12 32-bit on a 64-bit machine

When I check the installed drivers using the 32-bit version of the ODBC Data Source Administrator (located in C:\Windows\SysWOW64), I notice that the ACE 12 driver is installed.  However, trying to use that one from the Management Studio gives me this:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

The Results pane shows all the columns with the right column names, retrieved from Excel.  But the driver seems to have a problem retrieving the actual data.

This issue with error 7330 is mentioned in the following thread on the SQL Server MSDN forum, but unfortunately the proposed solution didn’t solve the problem in my case.

64-bit SQLCMD using ACE 14 driver

I also tried using the 64-bit version of sqlcmd.exe, but strangely enough that throws the same error.

Using sqlcmd 64-bit to query Excel

I actually expected this last method to work, after all, everything is now running in 64-bit.  But alas, it didn’t…

One more go…

After some more trial and error, I have actually found a way to get the query to work.  I don’t have a logical explanation on why it’s behaving the way it is, but, well, it is working…

This query is running fine:

SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx',
    'SELECT * FROM [ProductList$]');

But this one isn’t:

--Excel 2007-2010
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx',
    'SELECT * FROM [ProductList$]');

It’s exactly the same query, only difference is the comment line at the start.  And even weirder, if I add a space after the double-dash, the query works fine as well!

Then I decided to remove the commented INTO clause.  This made the weird behavior disappear.  So for some reason SQL Server doesn’t like the OPENROWSET function combined with comments inside the query.  The strange behavior also disappears when a space is added between the double-dash and the INTO keyword.

Uh, computers can be so much fun, right? :-)

If anyone has got an explanation on this strange behavior: please do post a comment!  For now my conclusion is: don’t use comments when creating an OPENROWSET query.

IMPORTANT UPDATE (April 11, 2010): it seems that the current installer for the ACE 14 driver contains a bug and registers it as being “Microsoft.ACE.OLEDB.12.0” instead of “Microsoft.ACE.OLEDB.14.0” .  This explains some of the issues shown above.  Some evidence on the issue:

Microsoft Connect: Access Database Engine 2010 installation issue to use with ADO access technology to access data from Jet database (.mdb files)

The ‘Microsoft.ACE.OLEDB.14.0′ provider is not registered ….. (see last comment)

Excel Services, ODC and Microsoft.ACE.OLEDB.14.0

Conclusion

The above has shown that OPENROWSET() can be a useful function, given the right circumstances.  But in the wrong setting it can be quite cumbersome to get to work.

I would recommend this method only for one-off quick imports, such as when you as a developer are given a bunch of data in a spreadsheet and need to get it into the database, one way or another.  I would not use it for an automated import process.  For that we’ve got a more interesting alternative which I’ll cover in an upcoming article.

Have fun!

Valentino.

References

BOL 2008: Special Table Types (incl. temporary tables)

BOL 2008: OPENROWSET() function

BOL 2008: the INTO clause

CSS SQL Server Engineers: How to get a x64 version of Jet?

Share

Tags: , , ,

  1. bogdan’s avatar

    Excelent article. Thank you!

    Reply

    1. CCash’s avatar

      Thanks for this page. I have been floundering for over a week with a very similar task. Your blog page brought everything together for me, as well as provided a totally new path for investigation. I have saved this to my favs because I am sure I will be referring to it in the future.
      thx again

      Reply

  2. xxaaxx’s avatar

    Thanks for posting but none of these methods did not solve my problem. I am working wtih Win 7, SQL Server 2008 x64, Excel 2007. I am getting this error:

    OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. Yes, I tried this too:

    sp_configure
    GO
    sp_configure ‘show advanced options’, 1
    GO
    reconfigure
    GO
    sp_configure ‘Ad Hoc Distributed Queries’, 1
    GO
    reconfigure
    GO

    It did not work. I check, I have the right driver. No matter what I do I get the error. Any help would be appreciated. Thanks!

    Reply

  3. Valentino Vranken’s avatar

    Could you post the query that you’re using? I’ll try to reproduce the problem then.

    Reply

  4. aasssa’s avatar

    great article..!
    really helpful..!

    Reply

  5. Real Drouin’s avatar

    Everything is working fine. I’m very happy.

    I created a store procedure with all the instructions and I’m passing the invoice number. Here is my “problem”. I need to put the invoice number in the title (header and footer).

    Do you have any idea, how I can do that ?

    Thanks
    Real

    Reply

  6. Valentino Vranken’s avatar

    Hi Real,

    I think you’ve commented on the wrong article. Your question seems to be related to Reporting while this article explains how Excel data can be retrieved into a SQL Server database.

    Could you please post your comment on the article to which you’re referring? Then I’ll have a look at it.

    Thanks,
    Valentino.

    Reply

    1. Sasha’s avatar

      Hi Valentino!

      I’m using a 64-bit Win7, a 32-bit MSSQL 2008 and a 32-bit Office 2007. And I’ve gone thru exactly the same process as you did, the only difference being that the comment thing didn’t help :(
      Do you have any suggestions?
      Thanks

      Reply

      1. Valentino Vranken’s avatar

        Hi Sasha,

        Did you install the 32 or 64-bit version of the ACE driver? I believe you need the 32-bit one.

        Regards,
        Valentino.

        Reply

        1. Sasha’s avatar

          It wouldn’t let me install the 64-bit anyway, as my office is 32-bit.

          Reply

  7. pepepaco’s avatar

    have you tested mixing text and numbers to see if they finally get it working?

    previously you can not do that without getting velues replaced by nulls.

    regards.

    Reply

    1. Noel Vargas’s avatar

      How do you solve this?

      The problem I have on a particular column is that there are both text and numbers. I tried forcing Excel to save the whole column as text, but the numbers are still imported as NULL.

      Ex: VOIP imports fine, 1024 doesnt. The whole column was marked and saved as TEXT in Excel.

      I’m using a Excel 2003 file.

      Reply

      1. mihail’s avatar

        So you are using Provider=Microsoft.Jet.OLEDB.4.0?

        Reply

  8. Dinh TG’s avatar

    Great Comments !!! That is all.

    Reply

  9. Bashir’s avatar

    Very helpful great article. :)

    Reply

  10. Ajit Pratap’s avatar

    hi,

    I am getting Following error after all Configuration:
    Msg 7308, Level 16, State 1, Line 1
    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.

    Please do the need full…..

    Regards
    Ajit Singh

    Reply

    1. Valentino Vranken’s avatar

      Would you mind posting your query?

      Reply

  11. Ajit Pratap’s avatar

    Version Info For Above is:

    Microsoft SQL Server Management Studio 10.50.1600.1
    Microsoft Data Access Components (MDAC) 3.85.1132

    Reply

    1. Valentino Vranken’s avatar

      Are you using a 32 or 64-bit SQL Server?

      Reply

  12. Nitesha’s avatar

    hi there,

    I am trying to update the SQL(sql server 2008) table using Excel 2010. I am receiving the follwoign error.
    OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Could not find installable ISAM.”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.
    .
    I have installed the Microsoft.ACE.OLEDB.12.0.

    I have tried all the bits but it gives me differnt error each time.

    Regards

    Reply

  13. Jaroslaw Kucharski’s avatar

    Hi i get the same message like Nitesha and i already setup ACE driver 64bit.

    SELECT * –INTO #productlist
    FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,
    ‘Excel 12.0 Xml;HDR=YES;Database=C:\temp\test.xlsx’,
    ‘SELECT * FROM [test$]‘);

    OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Unspecified error”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

    Best Regards,
    Jaroslaw Kucharski

    Reply

  14. Jonathan’s avatar

    Under Windows 2008 64 bit with the Microsoft Access Database Engine 2010 64bit installed, running the query as an administrator (run as) solved this error for me :

    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

    Reply

    1. Valentino Vranken’s avatar

      Thanks for mentioning that, good to know!

      Reply

  15. vini’s avatar

    hi Valentino ,
    this was a very useful article. but , I have still the same errors.

    I have put the queries none worked.

    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO

    sp_configure ‘Ad Hoc Distributed Queries’, 1;
    GO
    RECONFIGURE;
    GO

    SELECT * –INTO dbo.BulkLead
    FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,
    ‘Excel 12.0;HDR=YES;Database=C:\Users\CRM_Admin.CONTOSO\Desktop\crownap\LeadTemplate.xls’,
    ‘SELECT * FROM [Sheet1$]‘);

    I am trying to run this in windows2008 64-bit, SQL 2008 64-bit, installed AccessDatabaseEngine _x64.exe, AccessRuntime 2010, but there is no MS Office installed.

    and the Error I am getting are:
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

    Reply

    1. Valentino Vranken’s avatar

      Is your file closed when you’re running that query? Also, see above comments, using “run as administrator” would solve that issue too.

      Reply

  16. vini’s avatar

    thanks valentino.

    I was not able to open the file itself in the server as there was no Office installed. So the question of file open or closed does not exist.
    Then , i read in other forum. try to export from excel to SQL using SSIS. first that also gave the same error.
    Then,I could at least get the connection succeeded using ““Microsoft.ACE.OLEDB.12.0″.

    But, again when i tried the above procedure , i get the same error.
    Now, I have also installed the Office 64-bit , still the issue persists.

    Please help me.

    Reply

  17. Adam Stefan Costa’s avatar

    Thanks for all your info…

    I tried do all this, but with no good effect.

    I only get SUCCESS when I disable the UAC.

    This solves my problems…

    Hope this helps to all!!!

    Reply

  18. mihail’s avatar

    Can you use Ace 12.0 OleDB provider on an Excel 2010 file? Or you need Ace 14.0 and this one will work on 2007-2010 files? I am having a problem with 255 column limitations with ACE 12.0, do you have any work around for it?
    Thanks,
    Mihail

    Reply

    1. mihail’s avatar

      Hi Valentino:
      ANY chance that you can answer my question regarding ACE providers column limitation to 255?? I REALLY appreciate a suggestion here since I am using it and have this problem.

      Reply

      1. mihail’s avatar

        Hi Valentino:
        Any chance I get some help(advice) from you regarding the 255 column limitation?
        Thanks,
        Mihail

        Reply

        1. Valentino Vranken’s avatar

          Hi Mihail,

          As I’m running Windows 7 64-bit, SQL Server 2008 R2 64-bit and Office 2010 32-bit, I’m not even able to get my queries working at the moment… But the length limit is probably something which won’t be possible to avoid.

          As a solution, I would recommend you to try out SSIS. If you use the Import wizard to get you started, the process is not that complicated, and you can import longer strings using that method. It also gives you more control on handling the data.

          I’ve explained it in the following article: http://blog.hoegaerden.be/2010/04/20/importing-excel-data-using-integration-services/

          If you run into any issues with that, don’t hesitate to post a comment on that article.

          Best regards,
          Valentino.

          Reply

  19. orzzzzz’s avatar

    my story:
    windows 7(x64),mssql2008(x64),office 2007(32bit)..

    can’t install the AccessDatabaseEngine_X64–because of office 32bit.
    install AccessDatabaseEngine — mssql2008 can’t load it?…
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”. msg:7302

    too bad…..

    Reply

  20. Nitesh’s avatar

    I want to export select query data to excel but error is giving in sql server 2008

    Msg 7403, Level 16, State 1, Line 3
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered.

    Please help me

    Reply

  21. Anand’s avatar

    I have configured as per above informaiton still i am getting below error

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object “SELECT * FROM [rep_268662$]“. The OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” indicates that either the object has no columns or the current user does not have permissions on that object.

    Please help..!!

    Reply

    1. Anand’s avatar

      Anyone plz help me out of this error.. i have spent amost a day on this..!! :(

      Reply

    2. Allen’s avatar

      I fought with this error too and it turned out (ouch) that the actual Excel spreadsheet was named Sheet1 internally but I was referring to it by the name of the Excel file e.g. my_spreadsheet_data. The sheet name can be anything but the query has to use the right name.

      Reply

  22. Adam Stefan Costa’s avatar

    Have you disabled the UAC?

    Reply

  23. Don’s avatar

    Great article. Very informative. I’ve encountered an issue where all numbers imported from Excel are truncated at 4 decimal places. I’m using TypeGuessRows=0 and ImportMixedTypes=Majority Type in the registry for the ACE 14.0 OLEDB provider but it still truncates at 4. Any help would be appreciated.

    Reply

    1. pepepaco’s avatar

      if you find a solution please post it here, since I have been looking for it for more than 5 years.

      the only workaround is to use excel Activex to load the file then read cell by cell to export it to any other format.(CSV,DataSet, etc.)

      good luck.

      Reply

      1. Allen’s avatar

        5 years? Dude – get a life! You must have meant days.

        Reply

  24. zack zeeaay’s avatar

    It is very useful information to use.

    How ever I am this error “Msg 7302, Level 16, State 1, Line 1, Cannot create an instance of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.”

    Surprisingly I am able to run the same query on my Sandbox and it works like charm. But when I copy this to my production box, it gives me the above error.

    I have checked and see my Drive in ODBC Driver.

    The onther thing, I did enable the ‘Ad hock distributed queries’ but did not restart my SQL Box. Do I have to

    Reply

    1. Allen’s avatar

      AFAICT you do not need to restart your SQL Server instance. YMMV

      Reply

  25. sourabh’s avatar

    My .xlsx file is closed but i am still getting this error

    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.

    What can be the reason then. Also can i pass local file path to openrowset query. If yes then what is the syntax for that. Please help.

    Reply

  26. Allen’s avatar

    Several additional observations:
    (Environment is Win7 Ultimate, SQL Server 2008 R2 Express, Excel 2010)

    1) Leaving Excel running even if the spreadsheet is closed appeared to cause problems. Maybe it somehow monopolizes the drivers. Not sure. Shutdown Excel, and my imports worked.
    2) Naming the worksheets other than Sheet1 is OK, but update the query accordingly.
    3) Columns with mixed numbers and letters seem to cause issues, but I overcame this by forcing the column format to Text (maybe this was already mentioned). I also forced date columns to date format.
    4) Use column headers!
    5) The TryGuessRows thing matters as everyone can attest! There are 2 instances of this setting in my registry. It does beg the question why this Microsoft (un)-helpfulness is not exposed as a configurable application property rather than forcing REGEDIT mining.
    6) Putting the following script snippet at the top of each query will overcome forgetting all these tweaks next time you come back to this topic:

    USE [master]
    GO
    exec sp_configure ‘show advanced options’, 1
    GO

    RECONFIGURE WITH OverRide
    GO

    exec sp_configure ‘Ad Hoc Distributed Queries’, 1
    RECONFIGURE
    exec sp_configure ‘xp_cmdshell’, 1
    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
    GO

    7) I would really appreciate it if somebody could comment back here as to a simple means to generate CSV files from Excel that surround the values with dquotes. That would have solved a bunch of issues for me. That is way old-school I know but it unambiguously deals with mixed type columns. Maybe it is a mysterious CSV Save-As setting I did not see.

    Reply

    1. Allen’s avatar

      Oh, and the working directory problem will bite you as mentioned. It is perhaps a security nightmare, but anyone trying to use these query things must have rights to the place where the SQL Server Engine user account puts its temp files. I use an explicit user account MSSQLEngine with limited rights, as MSS recommends, so I have granted Everyone rights to the temp folder of the user MSSQLEngine, which is as I recall

      C:\Users\MSSQLEngine\AppData\Local\Temp

      With a dedicated user that runs the MSS engine, at least, you don’t have to expose a directory under Windows\System etc. I think that would be wise.

      Reply

  27. Radhi’s avatar

    I am trying to tranfer data from excel to SQL server 2008 R2 table using the below command suggested in your article:

    INSERT INTO dbo.Employee
    SELECT * –INTO #productlist
    FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;HDR=YES;Database=\\compname\d$\Emp\Appl.xlsx’,
    ‘select * from [Sheet1$]‘);

    I get the below error. I tried the sp_configure and also downloaded the exe from
    http://www.microsoft.com/en-us/download/details.aspx?id=13255
    but still getting below error. Please suggest.

    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.

    Reply

  28. pepepaco’s avatar

    believe me the best way to load excel files is using its ActiveX control follow this link

    http://csharp.net-informations.com/excel/csharp-open-excel.htm

    regards.

    Reply

  29. Mihail’s avatar

    Hi pepepaco:
    2 questions for you:
    1. Can you return a DataTable for each of the worksheets using ActiveX, similar with what the OLEDB reader does?
    2. Can you surpass the 256 column limits with ActiveX that OLEDB reader can’t?

    Thanks!

    Reply

    1. pepepaco’s avatar

      if you use excel 2007 or later, im pretty sure you will be able to surpass the limit,
      and Yes you can access all the worksheets using the activex control.

      Reply

  30. Mihail’s avatar

    Excel 2007 or later will be the intended target. At this time I am using the MS OLEDB reader that’s why I asked if you can retrieve all your worksheets via the ActiveX. Also, when you get you data via the Activex can you get them into a table for each worksheet, so that table would become a data source for a grid basically having the same columns as the worksheets? What do you know about data format, are they preserved? If you have or will work to something similar my asking, please let me/us know and post the code or a link to it.
    Thanks.

    Reply

    1. pepepaco’s avatar

      besides the link i have already provided here is another http://www.dotnetperls.com/excel
      or just google for Microsoft.Office.Interop.Excel

      regards

      Reply

  31. Aaron’s avatar

    Thank you for this article, I had a comment in the code and after removing it per this article, I finally have my query working. I’ve spent an entire day trying to solve this.

    Reply

    1. Valentino Vranken’s avatar

      Yeah, I know the feeling, it’s silly illogical things (well, bugs more like it) like that which can cost us a lot of time!. Good you got it to work!

      Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved