How to avoid OLEDB converting "."s into "#"s in column names?

Question

I'm using the ACE OLEDB driver to read from an Excel 2007 spreadsheet, and I'm finding that any '.' character in column names get converted to a '#' character. For example, if I have the following in a spreadsheet:

Name        Amt. Due        Due Date
Andrew      12.50           4/1/2010
Brian       20.00           4/12/2010
Charlie     1000.00         6/30/2010

the name of the second column would be reported as "Amt# Due" when read with the following code:

OleDbConnection connection = new OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=MyFile.xlsx; " +
    "Extended Properties=\"Excel 12.0 Xml;HDR=YES;FMT=Delimited;IMEX=1\"");
OldDbCommand command = new OleDbCommand("SELECT * FROM MyTable", connection);
OleDbReader dataReader = command.ExecuteReader();
System.Console.WriteLine(dataReader.GetName(1));

I've read through all the documentation I can find and I haven't found anything which even mentions that this will happen. Has anyone run into this before? Is there a way to fix this behavior?

Answer 1

Has anyone run into this before? Yes. (See also http://stackoverflow.com/questions/1088394/why-is-the-column-name-from-a-csv-file-different-than-its-datatable)

Is there a way to fix this behavior? Not while using the OLEDB driver to read from Excel.

The issue is that by using the OLEDB interface you're saying "present it to me as an SQL database", but SQL database columns don't allow "." in their names. (Because . is a special character in SQL, used to say things like tableAlias.columnName)

Answer 2

The period is being changed to # because . isn't legal in a column name. If the column name is only being used internally, then this doesn't matter at all, but since you're asking the question I assume the column name is showing up in a report or grid somewhere with the # in it.

If you don't know ahead of time which columns are going to contain ., then anywhere the column name is displayed you can do label1.Text = colname.Replace("#", ".") and hope that none of the other column names contains #. There may be some way of getting OleDb to replace . with a more obscure and infrequent character (like | or ~) that would let you Replace with less fear of mucking up some other column name.