3

I need to create a procedure to upload data from an MS Excel spreadsheet to SQL server on command. My background is in Access VBA I am attempting to use either of the below Distributed Query methods as described on the Microsoft support website: (https://support.microsoft.com/en-us/help/321686/how-to-import-data-from-excel-to-sql-server). My table name and filename are different, otherwise I am using the exact code below. I receive the error: The OLE DB provider "Microsoft.ACE.OLEDB.4.0 has not been registered." I've tried other OLEDB versions to no avail. Not sure how to check which version of the driver I need. The SQL server that I am working with is 64 bit but my local machines are 32 bit and I think this is causing issues. If anyone can help simplify this issue and point me in the right direction I would much appreciate it. I am well versed in VBA with some SQL background. Other than that I don't have much programming background. Thank you!

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
Improve this question
7
  • What other elements are part of this procedure? Do they require that you use Access, or use VBA to move data from Excel to SQL Server? Commented Jul 10, 2017 at 20:30
  • OLEDB 4.0 is not supported by 64 bit operating systems. You will most likely need to use 12.0 (download 32 or 64bit here) Commented Jul 10, 2017 at 20:39
  • Did you install the 4.0 driver on the SQL Server? Commented Jul 10, 2017 at 20:48
  • Hi rd, I am investigating all options. Every morning I need to import the Excel spreadsheet into SQL. I will then perform a bunch of functions and queries to produce reports. My main concern for now is automating the process of importing the data from Excel to SQL. If there's a way to do it in VBA that would be great or if I can do it directly through a SQL query that would work too. Commented Jul 10, 2017 at 20:48
  • 2
    Try Microsoft.ACE.OLEDB.12.0 inside SQL Server not Excel VBA. Commented Jul 10, 2017 at 20:53

2 Answers 2

Reset to default
2

The query:

SELECT * INTO [TableName]
FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; Database=' + [SpreadsheetFullPath] + ';IMEX=1'' )...[' + [WorksheetName] + '$]'

Notes:

  1. Keep in mind that the query is executed on the SQL server so [SpreadsheetFullPath] is the path on the server and not the local machine.
  2. UNC paths (\\sharedDir\...) are accepted.
  3. Keep Excel worksheets to be imported simple.
  4. When something fails, the errors returned by this driver are very unhelpful, so try to avoid them by observing 3.
  5. You may need to install drivers on the database server: Office 2010, Office 2016
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

There is a bigmuscled engine called SSIS (Sql Server Importing Services) that does this, with error reporting, transformation of data, scheduling etc. It may already exist on your server. This is not the place to describe it in detail but there are tutorials on youtube. During the years the design environment has either been standalone or a plugin in Visual Studio. It will sure solve your importing problems, but there is a (steep) learning curve. (Which may pay off in you increased skills!). After SSIS you can continue to SSRS and SSAS but that is another story!

Your current problem is the driver name in the "connection string". It often depends on your server's OS-version. (I.e. what has (not) been installed). Instead of guessing (as I have done, developing on XP and at first deploying blindly on Win7 and Win10), I suggest you ask your Admins to on your server make a ODBC connection DSN file connecting to that Excel file of yours, and then look at the contents of that DSN file. It will contain the connection string and the driver name you are looking for, that suits your server's installation. I have also found that "default port#" may suddenly be required in the string as well when connecting to Sql Server.

Improve this answer

1 Comment

Hi P, thanks for the response. I am going to look into this. I am not the Server Admin (I have a dedicated database on the server) so I have run into many permissions issues, but I will work with our SQL administration team.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.