Reading xlsx file in C# returning blank value

[C# Programming]

Hi,

I have to read an xlsx file in which there are multiple columns. One of it is a 'Date' column type. I am using OLEDB with Microsoft.ACE.OLEDB.12.0 as the provider. The problem is, suppose if there is a non date value (Error data) in the column the oledb is replacing the error data with null. I have a requirement to show the all data (good data as well as the error) to user who is reading the excel file into our database.i.e. i dont wnt the engine to convert the value to null

When reading xls file,which can be done by using Microsoft.Jet.4.0 provider, we can overcome this by changing property of IMEX=1 and changing the 'Typeguessrows=1' in the registry. But unfortunately this provider cannot be used for reading an xlsx file.

Can anyone help me how to overcome this problem??? I have also listed down the code below which i am using for reading the file






DataSet ds = new DataSet();
//Provider String Extended properties:
//"Excel 12.0": Use Excel (xls or xlsx) as source
//"Header Yes": Header is included in the Excel sheet
//"IMEX=1": When reading from the excel sheet ignore datatypes and read all data in the sheet.
//Without setting IMEX=0, the excel reader looks for the datatype in the excel sheet.
//The code below in comments was used for reading xls file
//OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
// The code below is the one i am using for reading an xlsx file
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=0\"");
con.Open();
try
{
//Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname = dt.Rows[0]["TABLE_NAME"].ToString();

OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + sheetname + "]", con);
myCommand.Fill(ds);

}
catch (Exception ex)
{
string exce = ex.Message;
}
finally
{
con.Close();
}
return ds;