Retrieve data from Excel contains a special char using OLEDB C#

Question

I have three columns in an Excel sheet:

1. CompanyName
2. PhoneNo
3. EmailId

When I try to fetch data from Excel, if the sheet contains 012-231564 type of data in PhoneNo column, then the resulting DataSet contains blank cell

var connString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;
Data Source={0};Extended Properties=\"Text;HDR=YES;FMT=Delimited\"",
Path.GetDirectoryName(Server.MapPath("~/DataMiningFiles/" +    StrFileName)));

var query = "SELECT * FROM [" + Path.GetFileName(Server.MapPath("~/DataMiningFiles/" + StrFileName)) + "]";

using (var adapter = new OleDbDataAdapter(query, conn)) { 
    var ObjGetExcelData = new DataSet("CSV File");
    adapter.Fill(ObjGetExcelData); 
}

Answer 1

There's quite a bit I'd change about this tbh. So lets see...

1. I would not use that driver as it is obselete. Use Microsoft.ACE.OLEDB.12.0
2. You need to specify IMEX=1 in order to import mixed data types.
3. Your extended properties specify a text file import but you've said you want to import excel. If so specify that.

4. Generally I'd read this table by table. I'm not familiar with your attempt to read the lot into a DataSet. I may have a go if I get bored over the new year. This example will however work...

   string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                              "Data Source=e:\\Test.xlsx;" +
                              "Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
   
   OleDbConnection oConnection = new OleDbConnection();
   oConnection.ConnectionString = sConnectionString;
   oConnection.Open();
   
   //Find all readable Named Ranges and Worksheets
   DataTable ExcelSheetNames = oConnection.GetSchema("Tables");
   
   foreach (DataRow SheetNameRow in ExcelSheetNames.Rows)
   {
       string SheetName = (string)SheetNameRow["Table_Name"];
   
       //Only handle WorkSheets. Named Ranges are otherwise named.
       if (SheetName.EndsWith("$") | SheetName.EndsWith("$'"))
       {
           DataTable SheetContents = new DataTable();
   
           //Read the contents of the sheet into a DataTable
           using (OleDbCommand oCmd = new OleDbCommand("Select * From [" + SheetName + "]", oConnection))
           {
               SheetContents.Load(oCmd.ExecuteReader());
           }
   
           //You can also put the DataTable load on one line (I do)
           //SheetContents.Load(new OleDbCommand("Select * From [" + SheetName + "]", oConnection).ExecuteReader());
   
           //Print the content of cells A2..C2 to the console window
           Console.WriteLine(SheetContents.Rows[0].ItemArray[0]);
           Console.WriteLine(SheetContents.Rows[0].ItemArray[1]);
           Console.WriteLine(SheetContents.Rows[0].ItemArray[2]);
   
      }
   }
   
   oConnection.Close();
   

Incidentally please try to avoid the use of "var" variables. Strongly type them and you will save yourself and others much heartache later on.

If you know the name of the sheet you want to read you can simply code it all as...

    string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                               "Data Source=e:\\Test.xlsx;" +
                               "Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

    OleDbConnection oConnection = new OleDbConnection();
    oConnection.ConnectionString = sConnectionString;
    oConnection.Open();

    DataTable SheetContents = new DataTable();

    SheetContents.Load(new OleDbCommand("Select * From [Sheet1$]", oConnection).ExecuteReader());

    Console.WriteLine(SheetContents.Rows[0].ItemArray[0]);
    Console.WriteLine(SheetContents.Rows[0].ItemArray[1]);
    Console.WriteLine(SheetContents.Rows[0].ItemArray[2]);

    oConnection.Close();