Search code examples
excelado.netjet

Syntax while trying to include The multi-part identifier [°C] in command text

I am trying to copy data directly from a Microsoft Excel file into the database using Asp.net. The problem is that the spreadsheet has a column named Product[°C], which, when I include in the query, fails to parse as valid SQL.

I'm using the following code from an example on how to connect to and query an Excel file via OleDb and Ado.net.

var conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0},HDR={1}";
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
connExcel.Open();
var SheetName = "Sheet1$";

// ...
cmdExcel.CommandText = "SELECT [Date/time] as [Date/time], [Product[°C]]] as [Product] From [" + SheetName + "] ";

Here the problem bit is [Product[°C]]]. The debugger gives an error saying:

Additional information: Syntax error in query expression '[Product[°C]]]'.

Where as if I want to do query a table with [Product[°C]]] in SQL I can do it via using:

Select id, [Product[°C]]] from TableName

This query parses fine in SQL but not when I query Excel. Why not?

Solution

  • Ok, I've finally deducted that you are using the Excel Jet driver to connect directly to the Excel spreadsheet. This driver uses a SQL engine which has some... unique features.

    One feature is to convert square brackets in a column name to parentheses. What you want is likely this:

     cmdExcel.CommandText = "select [Date/Time], [Product(°C)] from [Sheet1$]";

    Note the use of '(' in place of '['.