Column name is number, select it with oledb

Question

Similar question: Pass number as a column name in select statement of Sql

I have a column in a csv file that has a name of 0.000. How do I select it with a oledb select statement? Currently I have:

StringBuilder sbSelectItems = new StringBuilder();
sbSelectItems.Append("location_c, ");
sbSelectItems.Append("impb_, ");
sbSelectItems.Append("order_id, ");
sbSelectItems.Append(" `0.000` as shipCost, ");
sbSelectItems.Append("transmitta, ");
sbSelectItems.Append("piecelb ");

string sSelectStatement = "SELECT " + sbSelectItems.ToString() + " FROM [" + sFileName + "]";

but I get an error that '' is not a valid column. I've tried the [0.000], '0.000', "0.000" and what I have currently and I get the literal values or an error thrown for an invalid column. The file is auto generated through a program I don't have access to so I can't change the column name.

---

UPDATE

Trying the example from the first answer I got an error that said No value given for one or more required parameters. So I was confused and did a SELECT * FROM... and the column name, when I did that the column name, was tr110308#csv.01. I tried to select the value then doing tr110308csv#csv.01 but I was not able to.

Also using 0#000 didn't work...

#62 is the column I want.

Answer 1

Try changing your column name in your "select" statement to "0#000". Here is a sample which attempts to reproduce and then fix your issue.

Given a CSV file with the following content:

Foo,Bar,100.0,200
Alpha,Happy,8,5
Beta,Sad,19,2

A Select statement of the form

"Select Foo, `100.0` From "

Receives an OleDbException with the message...

'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

..which matches the error you received (or, I assume it does, you abbreviated and altered the message).

Changing the select to "100#0" was able to sidestep the issue.

Full repro code:

string fileName = "C:\\Temp\\test.csv";
string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"TEXT;HDR=YES;FMT=Delimited\";",    Path.GetDirectoryName(fileName));

using (var connection = new System.Data.OleDb.OleDbConnection(connectionString)) 
{
    string sql = "Select Foo, `100#0` From " + Path.GetFileName(fileName);
    using (var adapter = new System.Data.OleDb.OleDbDataAdapter(sql, connection)) 
    {
        var table = new DataTable();
        var result = adapter.Fill(table);
        table.Dump(); // LinqPad method to display result for verification
    }
}

You'll note the column name in the output matches the select statement (although you may still alias it, as your original SQL attempts to do). Indeed, the method of discovery here was to simply perform a "Select * From ..." and inspect the output. I found no source material beforehand, so interesting question!

---

Edit: With your update, the same approach is applicable. The name "tr110308csv#csv.01" is not legal for the select statement, but the name "tr110308csv#csv#01" is. Renaming the column (to match yours) in the test file and then using the altered version in the code produces the desired output, and I recommend you attempt it in your code, as well.