Using OPENROWSET to Read Excel Worksheets from SQL Server: Part 2 – How to Define and Query a Linked Server

In my last post we looked at how to set up the system to use OPENROWSET to read Excel Worksheets from SQL Server. I believe that using the OPENROWSET function is the most flexible and practical method for linking Excel worksheets to SQL Server. Before we look at OPENROWSET, however, let’s take a moment to review the techniques for permanently defining a linked server in SQL Server.

Defining a Linked Server

Since an Excel worksheet is not really a “server”, it is probably best to take the time to write an SQL script to create a linked server rather than use the GUI. Once you have created a script, you can easily change the workbook path and filename for future projects.

Remember that when you define a linked server, SQL Server does not check to see that your settings will work. Therefore, the sp_addlinkedserver can execute without error even with erroneous parameter values. It is only when you attempt to execute a query will you discover whether your linked server definition is actually correct.

If you wish, you may download these SQL statements here.

The 32-bit Version

If you are using 32-bit SQL Server, you must create a linked server for Microsoft.Jet.OLEDB.4.0. 

EXEC sp_addlinkedserver

@server=OrderValues_EXCEL32,

@srvproduct=‘Whatever’,

@provider= ‘Microsoft.Jet.OLEDB.4.0’,

@datasrc= ‘c:\Temp\OrderValues.xls’, — xlsx files do not work in 32-bit version

@provstr= ‘Excel 8.0;’ — version doesn’t matter

The 64-bit Version

EXEC sp_addlinkedserver

@server=OrderValues_EXCEL64,

@srvproduct=‘Whatever’,

@provider= ‘Microsoft.ACE.OLEDB.12.0’,

@datasrc= ‘c:\Temp\OrderValues.xlsx’,

@provstr= ‘Excel 12.0;’ — version doesn’t matter

Notice a few curious points of interest. The value for @srvproduct cannot be null, but the actual value is not used and is irrelevant. Here we entered “Whatever”. Furthermore, the Excel version number entered in the @provstr does not need to match your version of Excel. You might as well enter the correct number to make your code more self-documenting, but it should work regardless.

Querying the Linked Server

The syntax for querying Excel from SQL Server is simple, but there are a few tricks. A named range in the Excel workbook can be queried directly, as if it were a table. In contrast, if you wish to specify a worksheet rather than a range, you must tack a “$” onto the end of the sheet name. In my opinion, using named ranges is less prone to problems and is the better choice. Note also the three periods between the server name and the table name in the following query examples. This is because SQL Server expects the four-part name syntax for linked servers:

Servername.DatabaseName.Schema.TableName

Since database name and schema name have no meaning in a workbook, they are omitted from the four-part syntax, leaving three odd-looking periods.

SELECT xl.* FROM OrderValues_EXCEL64results AS xl

— “results” is a named range

SELECT xl.* FROM OrderValues_EXCEL64OrderData$ AS xl

— “OrderData” is a sheet name

The workbook file cannot be open in Excel while SQL Server is attempting to execute a linked query. The table alias in the above queries is not necessary, but it is handy to alias a remote table if we want to include it in a join. The join syntax for linked tables is no different from the usual.

SELECT ProductName

, xl.*

FROM OrderValues_EXCEL32results AS xl

JOIN Products AS p

ON xl.ProductID = p.ProductID

Using OPENROWSET

In my opinion, it is wiser to use OPENROWSET to link to data in Excel workbooks rather than define a linked server. It is common to grab data from many different workbooks, and the OPENROWSET function enables you to query different workbooks without having to define many multiple linked servers. Just as with linked server definitions, you must use the 32-bit version if you are using 32-bit SQL Server, and as before you will not be able to query xlsx files. If you are using 64-bit SQL Server, then either the 32- or the 64-bit technique are available to you.

You will not be able to use OPENROWSET until you have configured your server to allow ad-hoc distributed queries. You can check the current configuration setting by running the following query in the Management Studio. Notice that when using sp_configure, it is not necessary to type out the entire configuration setting name; you must only type out enough so that the name is unambiguously distinguished from other configuration setting names.

EXEC sp_configure ‘Ad hoc dis’

If you get an error message, it may be that your server is not configured to show the advanced configuration options. You can turn on advanced options as follows:

EXEC sp_configure ‘Show Advanced’, 1

RECONFIGURE

You should now be able to check the current setting of Ad Hoc Distributed Queries. If it is 0, you must turn it on.

EXEC sp_configure ‘Ad hoc dis’, 1

RECONFIGURE

Check the value once again to ensure the value is now 1.

The 32-bit Version

SELECT * FROM OPENROWSET(

‘Microsoft.Jet.OLEDB.4.0’

,‘Excel 8.0;Database=C:\Temp\OrderValues.xls;HDR=YES’

,results) results” is a named range in excel

The 64-bit Version

SELECT * FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’

,‘Excel 12.0;Database=C:\Temp\OrderValues.xlsx;HDR=YES’

,results) — “results” is a named range in excel

As mentioned, the 64-bit version using Microsoft.ACE.OLEDB.12.0 can read either xlsx files or the older compatibility-mode xls files. Just as with predefined linked servers, you can easily join Excel data with SQL Server tables. Of course, if you are going to be using the Excel data repeatedly, you may wish to import the data into an SQL Server. You can use SELECT INTO or INSERT SELECT for this purpose.

SELECT * INTO #testTable

FROM OPENROWSET(

‘Microsoft.ACE.OLEDB.12.0’

,‘Excel 2.0;Database=C:\Temp\OrderValues.xlsx;HDR=YES’

,results)

Conclusion

There are a lot of details, to be sure. However, with careful attention to the installed software and the appropriate bitness and file type, you should be able to easily connect Excel data with SQL Server.  If you would like to learn more, have a look at Learning Tree’s 4-day course – SQL Server Transact-SQL Programming.  You can take it online from home or office using our AnyWare platform or from one of our many global centers.

Please to not hesitate to write if you have any comments or questions.

[1][2]

Become a True Data Wrangler with SQL Programming Training

View Course

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.