Last post Mar 02, 2013 10:20 AM by Lannie
Feb 25, 2013 08:48 AM|rohitksinha01|LINK
I am trying to read an excel sheet using Microsoft.ACE.OLEDB.12.0
In the excel sheet we have one field which is in telephone number format i.e (888) 801-8888 .Now when we read the excel sheet we are getting telephon no in the format 888 801-8888.Also we have defined all
the necessary parameters in the connection string to read value as text.
string strConn =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName +
";Extended Properties=\"Excel 12.0;HDR=" + HDR +
It seems Oledb is unable to parse the brackets '(' ,')' when we read formatted field from the excel because when we enter value as
(888) 801-8888 without applying any format it is returing same value in the DataSet. Below is the code snippet :
string sheetName = excelSheets;
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheetName + "]", conn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheetName);
DataRow rowDel = output.Tables.Rows;
Is that bug or are we missing something while reading text from excel sheet.
Mar 02, 2013 10:20 AM|Lannie|LINK
Sometimes what you see on the screen within EXCEL cell
is NOT what is actually stored by Excel or retrieved by OLEDB...
especially formatted cells.
Example: it looks like a date 10-MAY-2013 or 5/13/2013.. but Excel stores it as a decimal number,
and it is retrieved as a decimal number.
It is possible, the same holds true for other FORMATTING in excel.