SQL OpenRowSet Excel Query Returns Null Values

In an earlier article I reviewed how to query an Excel spreadsheet from SQL Server Management Studio (SSMS). I’ve found that this functionality is very useful, especially when working with data maintained by business users outside of a database system.

For an overview of how to connect SSMS with an Excel WorkSheet, please see my detailed article covering the subject. I continue to use the method of saving my SpreadSheet as a .xls format file which I then connect to using the OpenRowSet command with the Microsoft Jet OLEDB 4.0 driver. Once the Excel worksheet is query-able by SSMS it’s quite simple to work with this data just like you would work with a regular table.

However, one important caveat is that if your worksheet contains numeric as well as alphabetic data, this will confuse the driver and depending on the order of the data in your Spreadsheet, null results will be returned instead of the numeric data.

This behavior can be surprising when one first notices it, but happily it can be easily corrected so that you can properly query mixed data type columns.

To correct the Excel mixed Datatype problem it is best to specially format important columns in your Excel SpreadSheet before you query them.

See the screen capture and detailed steps below for how to format your columns in Excel 2007 so that they can be queried correctly.

Step by step:

1. First highlight the column of data that you wish to normalize
2. Then Click the Excel Data tab
3. Within the Data tab, select the Option titled Text to Columns
4. A wizard interface will appear that will allow you to set the data type of your column
5. Choose the following options in the Text to Columns wizard:
   1. On the first page of the wizard, choose the delimited file type radio button option for your data
   2. Click the Next button to reach the second page of the Text to Columns wizard
   3. The second page will show you a list of possible delimiters to choose from. I usually leave the first delimiter option selected. This option is to use the Tab delimiter. Make sure that the sample data shown in the Data preview pane of the wizard looks correct after you have selected your delimiter.
   4. Click the Next button to reach the third page of the Text to Columns wizard
   5. On the third page of the wizard you will be asked to set the Column data format. For columns that contain mixed alphabetic and numeric characters I select the Text radio button option. This will ensure that your SSMS query will also return numeric data.
   6. Finally, click the Finish button.

Congratulations! Now that you have followed these steps you will be able to use SSMS to query Excel worksheet mixed datatype column data without losing valuable information.