Avoiding OLEDB conversion with. "S in" # "s in column names?

Question

I am using the ACE OLEDB driver to read from an Excel 2007 spreadsheet, and I find that any "." the character in the column names is converted to the character '#'. For example, if the table has the following:

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 will be indicated as "Amt # Due" when reading 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 read all the documentation that I can find, and I did not find anything that even mentions that this will happen. Has anyone come across this before? Is there any way to fix this behavior?

Answer 1

See OleDBAdapter Excel QA I sent via stack overflow.

I created an .xlsx workbook in excel with your data and then modified OleDbConnection and it read your data perfectly.

 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 data to the console:

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

Answer 2

The period changes to # because . is not legal in a column name. If the column name is used only internally, then it doesn’t matter at all, but since you are asking a question, I assume that the column name is displayed in the report or grid somewhere with # in it.

If you do not know in advance which columns will contain . , then at any place where 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 to get OleDb to replace . a more obscure and infrequent character (such as | or ~ ) that will allow you to Replace with less fear of losing another column name.