locked
Unable to read formatted value with brackets '(' ')' from the excel sheet using Oledb provider RRS feed

  • Question

  • I am trying to read an excel sheet using Microsoft.ACE.OLEDB.12.0 provider.

    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 + ";IMEX=1;ImportMixedTypes=Text\"";

    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[0];
                            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheetName + "]", conn);
                            cmd.CommandType = CommandType.Text;
                            DataTable outputTable = new DataTable(sheetName);
                            output.Tables.Add(outputTable);
                            new OleDbDataAdapter(cmd).Fill(outputTable);
                            DataRow rowDel = output.Tables[0].Rows[0];
                            output.Tables[0].Rows.Remove(rowDel);

    Is that bug or are we missing something while reading text from excel sheet.

    Please help!

     

     

    Monday, February 25, 2013 7:13 AM