Reading Data from excel

Question

Hello,

 

I have an application which is reading data from excel. I am using the below connection strings:

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 12.0;HDR=Yes'"/>

When a column is having data like below(some cells with only numeric value and one cell with alphanumeric), the data which is having alphanumeric(ie, data in the 3rd row-23A) is not reading from the excel.The data coming to the datatable is shown in the image image.

 

Value in the excel file:

 

Size

34

23A

45

 

Data coming in the datatable

 

 

 

Below is the code used to read data from excel. Please check and suggest a way to fix the issue:

string path = Server.MapPath("~/Upload/");
string filePath = string.Empty;
string extension = string.Empty;
DataTable dtsheet = new DataTable();
DataSet ExcelData = new DataSet();
if (postedfile != null)
{
if (Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedfile.FileName);
extension = Path.GetExtension(postedfile.FileName);
postedfile.SaveAs(filePath);
}
string connectionstring = string.Empty;
switch (extension)
{
case ".xls"://for 97-03
connectionstring = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
//connectionstring = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
case ".xlsx"://07 and above
connectionstring = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
connectionstring = string.Format(connectionstring, filePath);
using (OleDbConnection connExcel = new OleDbConnection(connectionstring))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//firstsheetname
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//read data
connExcel.Open();
cmdExcel.CommandText = "SELECT * FROM [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dtsheet);
connExcel.Close();
}
}
}

Thanks,

Hanusha