0

Summary: Using the Jet.OLEDB provider and the SQL query, I do not know how to access the column the header text of which contains a dot. Is there any way to escape the dot in the SELECT query?

Details: I am using a connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data source=test.xls;Extended Properties="Excel 8.0;HDR=Yes;"

When the header text of the column of an Excel sheet contains a dot (notice the dot column named Abbrev. Packing)...

Column header with dot in the text.

... then the SELECT query like this...

SELECT
  [Date] AS d,
  [Code] AS code, 
  [Abbrev. Packing] AS packing,
  [Price] AS price
FROM [Sheet1$]

... fails with error 80004005. When I remove the dot from the header text and from the SELECT command, everything works smoothly, and the data is extracted. However, the Excel table comes from third party, and I cannot change the text of the header.

How can I escape the dot in the SELECT command, or what is the way to fix it?

pepr
  • 20,112
  • 15
  • 76
  • 139
  • 1
    Check this out meanwhile: http://www.experts-exchange.com/questions/23637965/How-do-I-read-excel-file-with-'-'-in-column-name-in-sql-table-using-SSIS-script-task.html – FutbolFan Jul 30 '15 at 19:37

1 Answers1

1

It appears that SSIS replaces period (.) with number sign (#) when I tried to load similar spreadsheet as you provided through a SSIS package. So, I am guessing you would need to either load it through SSIS if you have that option available, otherwise you could probably try querying it directly like this:

SELECT
  [Date] AS d,
  [Code] AS code, 
  [Abbrev# Packing] AS packing,
  [Price] AS price
FROM [Sheet1$]

SSIS Sample

FutbolFan
  • 13,235
  • 3
  • 23
  • 35