Excel Import Problem
-
Hi, See my code for importing data from excel sheet.I have problem with importing data which contains in one column all record in digit except one is in charecters. The row containing charecters in that perticular column is not imported.
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + @"\" + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; OleDbConnection ExcelConnection = new OleDbConnection(excelConnectionString); OleDbCommand ExcelCommand = new OleDbCommand(); ExcelCommand.Connection = ExcelConnection; OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); ExcelConnection.Open(); DataTable ExcelSheets = ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null); string SpreadSheetName = "[" + ExcelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString() + "]"; ExcelCommand.CommandText = @"SELECT * FROM " + SpreadSheetName; ExcelAdapter.Fill(ExcelDataSet);In my "Password" colomn all record contains digits but one record contains charecters. At a time of debuging, i have found record contains NULL value. I have done lots of googling for this problem but still face this problem. My registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel key ImportMixedTypes=Text.!- F - R - I - E - N - D - S -!
-
Hi, See my code for importing data from excel sheet.I have problem with importing data which contains in one column all record in digit except one is in charecters. The row containing charecters in that perticular column is not imported.
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + @"\" + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; OleDbConnection ExcelConnection = new OleDbConnection(excelConnectionString); OleDbCommand ExcelCommand = new OleDbCommand(); ExcelCommand.Connection = ExcelConnection; OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); ExcelConnection.Open(); DataTable ExcelSheets = ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null); string SpreadSheetName = "[" + ExcelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString() + "]"; ExcelCommand.CommandText = @"SELECT * FROM " + SpreadSheetName; ExcelAdapter.Fill(ExcelDataSet);In my "Password" colomn all record contains digits but one record contains charecters. At a time of debuging, i have found record contains NULL value. I have done lots of googling for this problem but still face this problem. My registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel key ImportMixedTypes=Text.!- F - R - I - E - N - D - S -!
The excel driver makes an assumption about the data type of each column based on the data in the first 8 rows. If you mix data types in the same column you need to format your excel sheet properly to get the data type set correctly. Related MS KB article[^]
-
The excel driver makes an assumption about the data type of each column based on the data in the first 8 rows. If you mix data types in the same column you need to format your excel sheet properly to get the data type set correctly. Related MS KB article[^]
Actually my client is upload excel sheet. I can't format cells in every sheet. is it any way to format excel sheet column? or any other Extended Property is there?
!- F - R - I - E - N - D - S -!
-
Actually my client is upload excel sheet. I can't format cells in every sheet. is it any way to format excel sheet column? or any other Extended Property is there?
!- F - R - I - E - N - D - S -!