Hello,
I have an application which is reading data from excel. I am using the below connection strings:
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
Jignesh KumarPosted Mar 12, 2021, 5:42 AM