Openrowset access to Excel 2007

  • Adrian Heald

    SSCommitted

    Points: 1614

    I’m trying to create a query that can read data from an EXCEL 2007 worksheet. I’ve installed the appropriate drivers downloaded from

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    but I can’t get it to work. The following query timesout

    Select *

    from OPENROWSET(‘MSDASQL’, ‘Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=d:\dasdgigs.xls;’, ‘SELECT * FROM [dasdgigs$]’)

    Using the old 2003 driver works fine using the following query I can successfully reads the data in the dasdgigs.xls sheet.

    Select *

    from OPENROWSET(‘MSDASQL’, ‘Driver={Microsoft Excel Driver (*.xls)}; DBQ=d:\dasdgigs.xls;’, ‘SELECT * FROM [dasdgigs$]’)

    I’ve tried the obvious things, like uninstalling and reinstalling the drivers, rebooting etc. but I just can’t make any progress.

    Using SQL 2005 Developers Edition.

    Does anyone know what I may be doing wrong?

  • Ramesh Saive

    SSC-Insane

    Points: 24275

    Try the following snippet….

    SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;Database=d:\dasdgigs.xls’, ‘SELECT * FROM [dasdgigs$]’)

    --Ramesh


  • Adrian Heald

    SSCommitted

    Points: 1614

    Thanks for that…. it works like a charm. 🙂

  • Adrian Heald

    SSCommitted

    Points: 1614

    Well it worked like a charm on my development machine Windows XP, latest SP etc. I’m now trying to get it to work on windows VISTA machine latest SP etc.. I’ve installed the AccessDatabaseEngine but when trying the following query in SSMS.

    SELECT *

    FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0 XML;Database=c:\rmfintrv.xlsx;IMEX=1;HDR=Yes;’, ‘SELECT * FROM [rmfintrv$]’)

    I get the following error

    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)".

    Interestingly it does manage to determine the columns, if I look in the results tab I can see the column names, which proves that it can read the file.

    Has anyone had any simmilar issues?

  • Ramesh Saive

    SSC-Insane

    Points: 24275

    I guess it has to do with some permissions on file, though you said it can access the schema.

    Try granting the read/write access to the folder to the SQL service account..

    --Ramesh


  • Adrian Heald

    SSCommitted

    Points: 1614

    Now I’m just getting confused.

    First of all I checked the authority on the directory containing the file and Everyone has Full Control so it can’t be that.

    I’m testing this on three machines one running VISTA native, another running VISTA under VMWARE and a SERVER 2003 machine (WHich has always worked). This morning the machine running VISTA native works fine. The VMWARE machine is still having the same issue.

    I’ve run Process Monitor from sysinternals and have noticed that the following (quite possibly significant) registry key is missing from the failing machine but is present on the working one.

    9RegOpenKey HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0 NAME NOT FOUND Desired Access: Read

    I exported it from the working machine added it to the failing machine and tried again after bouncing the SQL instance. Now I get

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters NAME NOT FOUND Length: 144

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\NestedQueries NAME NOT FOUND Length: 144

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\AllowInProcess NAME NOT FOUND Length: 144

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\NonTransactedUpdates NAME NOT FOUND Length: 144

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\LevelZeroOnly NAME NOT FOUND Length: 144

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\IndexAsAccessPath NAME NOT FOUND Length: 144

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DisallowAdhocAccess NAME NOT FOUND Length: 144

    RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\SQLServerLIKE NAME NOT FOUND Length: 144

    These keys are not present on the working machine. If I remove the HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0 keys from the working machine it fails. The working machine does have a bunch of other stuff that could be impacting these comparisons, like SQL2008 and SSIS where as the failing VMWARE based machine only have VISTA and SQL Server 2005. I’ve searched the working machine registry for “DynamicParameters” (the first of the missing values) but it’s not there. I also (as previously stated) have XP and Server 2003 machines where this all works properly, I’ve searched these registries for “DynamicParameters”, also not found.

    So I added HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters and AllowInProcess to the registry as a DWORDs and set their values to 1. IT NOW WORKS…….Still getting NAME NOT FOUND on the other values but it does read the spreadsheet.

    To summarise, using Microsoft.ACE.OLEDB.12.0 and openrowset on XP or server 2003 machines seems to work fine on Vista machines however it seems to be missing several registry keys (Which don’t appear to be present on the working machines), if the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters and AllowInProcess keys are added it works.

    While I’m rather pleased that I managed to get this mess working I’m concerned about releasing my applicaiton into production when I don’t really understand what caused the problem.

    Any suggestions anyone on why this might be the case?

    I’m a local and domain administrator on all machines and in all cases SQL Server is running under the local system account.

  • Ramesh Saive

    SSC-Insane

    Points: 24275

    …Hmmm, looks like these registry keys are created to restrict the ACCESS to certain PROVIDERS within SQL Server.

    --Ramesh


  • thomas.garay

    SSC-Addicted

    Points: 408

    Hi

    I have been having the same issue and found a solution for reading Data in from Excel Files.

    To achive this,

    The Sheet name in Excell must not have spaces,

    The Directory the file is in must be accessible to the SQL Server Service user Account

    The ‘Ad Hoc Distributed Queries’ Advanced SQL Config option must be enables

    USE: SP_CONFIGURE ‘show advanced options’,1

    RECONFIGURE WITH OVERRIDE

    SP_CONFIGURE ‘Ad Hoc Distributed Queries’,1

    RECONFIGURE WITH OVERRIDE

    Then Create the following Stored Procedure in your DB:

    CREATE PROC stp_ReadXLS @file varchar(1000)

    ,@template varchar(300)

    ,@hashtable varchar(300) = ‘loader_table’

    ,@excell_version varchar(2) = ‘8’

    AS

    DECLARE @SQL_T varchar(4000)

    EXECUTE AS login =’Sup_sp_exec’

    Begin Try

    SET @SQL_T = ‘SELECT * INTO ##’+@hashtable+’ FROM OPENROWSET(”Microsoft.Jet.OLEDB.4.0”, ”Excel ‘+@excell_version+’.0;Database=’+@file+”’, [‘+@template+’]) ‘

    EXEC (@SQL_T)

    END TRY

    BEGIN Catch

    SET @SQL_T = ‘INSERT INTO ##’+@hashtable+’ SELECT * FROM OPENROWSET(”Microsoft.Jet.OLEDB.4.0”, ”Excel ‘+@excell_version+’.0;Database=’+@file+”’, [‘+@template+’]) ‘

    EXEC (@SQL_T)

    END Catch

    PRINT (‘Populated ##’+@hashtable+’ table…..’)

    The Stored Proc Will read in the file and create a ## TABLE with the Contents. IF you Run the SP again, it will ADD new records to the same table, so that you could collate multiple XLS files into one.

    Also, you have the option to Work with Newer and older Excel files by providing the @excell_version parameter with the version number.

    DON’T FORGET TO DROP THE ## TABLE AFTER YOU ARE FINISHED.

    Hope this helps.

  • mfmf

    SSC Veteran

    Points: 214

    I had similar issues on Windows 7 64bit OS; Lot of these issues will disappear if you are on 32 bit OS.

    Take a look at this thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

    Basic things:

    (1) The Microsoft.ACE.OLEDB.12.0 provider from “2007 Office System Driver: Data Connectivity Components is 32 bit”. It only works on 32 bit SSMS. So I had to install 32 bit server instance and 32 bit tools to even try this out. It didn’t work on the 64 bit version.

    2. You will need to configure to allow “Ad Hoc Distributed Queries” for the sql server instance.

    3. As mentioned in the above link, run these two t-sql stmts to create necessary registry entries( copied from above link). Without these you get the error could not fetch a row …But surprisingly, the MSDASQL provider worked without running the following.

    USE [master]

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

    GO

    EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

    GO

    Hope this helps.

  • Adrian Heald

    SSCommitted

    Points: 1614

    Hi all,

    Firstly, There are now 64 bit OLEDB drivers available, these were released with the release 64 bit Microsoft Office earlier this month and have been around in beta form for several months.

    Secondly a warning. My original post in this topic was regarding getting the ODBC drivers to work correctly, the initial response was to use the OLEDB drivers shiped in the ACE package, this seemed to resolve my problems once I’d figured out the stuff about registry keys. However the OLEDB drivers appear to have a bug in which the don’t correctly read all csv or Excel datetime values. See the following steps to reproduce ths issue.

    Creating a text file called “test.csv” in your c:\temp folder with the following contents

    DATETIME

    2010-05-04 07:00

    2010-05-04 07:00:00

    2010-05-04 08:00:00

    2010-05-04 07:10:10

    Then using the following SQL Server statement in management studio to read the file

    select *

    FROM OPENROWSET(‘MICROSOFT.ACE.OLEDB.12.0′,’Text;Database=c:\temp;HDR=Yes’, ‘SELECT * FROM [test.CSV]’)

    Expected Results

    2010-05-04 07:00:00.000

    2010-05-04 07:00:00.000

    2010-05-04 08:00:00.000

    2010-05-04 07:10:10.000

    Actual Results

    2010-05-04 06:59:59.997 <<<error

    2010-05-04 06:59:59.997 <<<error

    2010-05-04 08:00:00.000 <<<ok

    2010-05-04 07:10:10.000 <<<ok

    This has been reported to Microsoft and they are actively working on the problem. I’ve tested this on 32 and 64 bit MICROSOFT.ACE.OLEDB.12.0 and on ‘MICROSOFT.JET.OLEDB.4.0 all of which exhibit the problem.

    I have also had an issue with the new 64 bit drivers when running multiple OPENROWSET statements simultaneously on different threads in the same process. Eventually SQL Server hangs and the only way out is to restart the SQl Server service.

  • ace.spades

    SSC-Addicted

    Points: 408

    Hi,

    I was able to set this up successfully on my local and on production, as long as the user credentials used on running the script is a sysad. This url ( http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm ) By Sergey Vaselenko was very helpful

    It’s been up and running for months already. And just all of a sudden, sometime around last week, I received a complaint from one of our users who utilizes the uploader that they can no longer upload any excel files.

    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)”.

    No one is altering the script.

    No one has updated the server nor my local.

    All of the servers for different apps that uses the script encoutered the error. I can’t even run it on my own local pc.(When I use JET as a driver on my local it still works, but can’t run it using ACE driver)

    I’m really confused already on what happened on the ace driver?

    I’m starting to wonder if there have been any update backend from microsoft or some kind of a bug on the ace driver??

    Hope someone could help me if you have similar issue.

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

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