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?

Jamestown

This appears to be another variant of #1088894 – Torry

Answer 1

See this OleDBAdapter Excel QA I posted via stack overflow.

I made an .xlsx workbook in excel with your data, and then changed the OleDbConnection and it read in your data just fine.

string sql = "SELECT F1, F2, F3 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

Writing the data to the Console:

Name  Amt. Due  Due Date
Andrew  12.50  4/1/2010
Brian  20.00  4/12/2010

Ductile

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.

Khalid