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

Posted by Andrew Miner on Stack Overflow See other posts from Stack Overflow or by Andrew Miner
Published on 2010-04-29T01:10:57Z Indexed on 2010/04/29 1:17 UTC
Read the original article Hit count: 276

Filed under:
|

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={0}; " +
    "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?

© Stack Overflow or respective owner

Related posts about oledb

Related posts about excel