Search code examples
sql-serverexceloledb

Import data from Excel to SQL server with substring function in query

I am pretty much following the example on this MSDN page:

http://code.msdn.microsoft.com/Imoprt-Data-from-Excel-to-705ecfcd

with 1 exception. Instead of a simple query like

"Select * FROM [Sheet1$]"

I'd like to do something a little more complicated like:

"Select *, SUBSTRING(COLUMN_A, 1, 5) as STRIPPED_COL_A FROM [Sheet1$]"

but I'm getting a useless Exception message "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."

If I have to guess, the problem is due to the usage of the unsupported SUBSTRING function in Excel or OleDB. So how to get around the problem.

Solution

  • In their example you are using the Microsoft.ACE.OLEDB driver to load the excel sheet via Provider=Microsoft.ACE.OLEDB.12.0 or Provider=Microsoft.Jet.OLEDB.4.0 so you are going to have to use the query formatting and functionality available within MS Access.

    Select *, MID(Column_A, <<start>>, <<length>>) as STRIPPED_COL_A from [Sheet1$]

    Keep in mind the other peculiarities such as <<start>> not being ZERO-indexed and instead starting with 1 being the first character in the string.