Read excel with diff datatypes in one column
-
Hi All, I have an excel file in this format, which I am trying to read using c# 04/05/2006 46 47 25 26 27 15 vgc 57 I am not able to read the values of row[1][0] and row[2][0], because in this column, the datatype of value in first row is date (04/05/2006). In the same way, I am not able to read the row[2][1], because in this column, the datatype of value in first row is a numeric. So, according to my perception, you need to have same datatype values in any particular column to read it in C#. I am looking for any option to read these type of excel files with values of different datatypes in a particular column. Any input will be highly appreciated. FYI, This is the code snippet, I am using to read this excel file. // Create connection string variable. Modify the "Data Source" parameter as appropriate for your environment. String filePath = "D:\\ProjectInfo\\HU Mail\\test.xls"; String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + "; Extended Properties=\"Excel 8.0;HDR=NO;\""; // Create connection object by using the preceding connection string. OleDbConnection objConn = new OleDbConnection(sConnectionString); // Open connection with the database. objConn.Open(); DataTable dt = null; // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] strSheetName = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { strSheetName[i] = row["TABLE_NAME"].ToString(); i++; } string strComand = "select * from [" + strSheetName[2] + "]"; OleDbCommand objCmdSelect = new OleDbCommand(strComand, objConn); // Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement. OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); // Pass the Select command to the adapter. objAdapter1.SelectCommand = objCmdSelect; // Create new DataSet to hold information from the worksheet. DataSet dsDeal = new DataSet(); // Fill the DataSet with the information from the worksheet. objAdapter1.Fill(dsDeal, "LMPPrices"); for (int k = 0; k < dsDeal.Tables[0].Rows.Count; k++) { for (int j = 0; j < dsDeal.Tables[0].Columns.Count; j++) { Console.Write(dsDeal.Tables[0].Rows[k][j].ToString() + " -> "); } Console.WriteLine(); } Console.ReadLine(); Best Regards,
-
Hi All, I have an excel file in this format, which I am trying to read using c# 04/05/2006 46 47 25 26 27 15 vgc 57 I am not able to read the values of row[1][0] and row[2][0], because in this column, the datatype of value in first row is date (04/05/2006). In the same way, I am not able to read the row[2][1], because in this column, the datatype of value in first row is a numeric. So, according to my perception, you need to have same datatype values in any particular column to read it in C#. I am looking for any option to read these type of excel files with values of different datatypes in a particular column. Any input will be highly appreciated. FYI, This is the code snippet, I am using to read this excel file. // Create connection string variable. Modify the "Data Source" parameter as appropriate for your environment. String filePath = "D:\\ProjectInfo\\HU Mail\\test.xls"; String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + "; Extended Properties=\"Excel 8.0;HDR=NO;\""; // Create connection object by using the preceding connection string. OleDbConnection objConn = new OleDbConnection(sConnectionString); // Open connection with the database. objConn.Open(); DataTable dt = null; // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] strSheetName = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { strSheetName[i] = row["TABLE_NAME"].ToString(); i++; } string strComand = "select * from [" + strSheetName[2] + "]"; OleDbCommand objCmdSelect = new OleDbCommand(strComand, objConn); // Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement. OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); // Pass the Select command to the adapter. objAdapter1.SelectCommand = objCmdSelect; // Create new DataSet to hold information from the worksheet. DataSet dsDeal = new DataSet(); // Fill the DataSet with the information from the worksheet. objAdapter1.Fill(dsDeal, "LMPPrices"); for (int k = 0; k < dsDeal.Tables[0].Rows.Count; k++) { for (int j = 0; j < dsDeal.Tables[0].Columns.Count; j++) { Console.Write(dsDeal.Tables[0].Rows[k][j].ToString() + " -> "); } Console.WriteLine(); } Console.ReadLine(); Best Regards,
-
Hi All, I have an excel file in this format, which I am trying to read using c# 04/05/2006 46 47 25 26 27 15 vgc 57 I am not able to read the values of row[1][0] and row[2][0], because in this column, the datatype of value in first row is date (04/05/2006). In the same way, I am not able to read the row[2][1], because in this column, the datatype of value in first row is a numeric. So, according to my perception, you need to have same datatype values in any particular column to read it in C#. I am looking for any option to read these type of excel files with values of different datatypes in a particular column. Any input will be highly appreciated. FYI, This is the code snippet, I am using to read this excel file. // Create connection string variable. Modify the "Data Source" parameter as appropriate for your environment. String filePath = "D:\\ProjectInfo\\HU Mail\\test.xls"; String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + "; Extended Properties=\"Excel 8.0;HDR=NO;\""; // Create connection object by using the preceding connection string. OleDbConnection objConn = new OleDbConnection(sConnectionString); // Open connection with the database. objConn.Open(); DataTable dt = null; // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] strSheetName = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { strSheetName[i] = row["TABLE_NAME"].ToString(); i++; } string strComand = "select * from [" + strSheetName[2] + "]"; OleDbCommand objCmdSelect = new OleDbCommand(strComand, objConn); // Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement. OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); // Pass the Select command to the adapter. objAdapter1.SelectCommand = objCmdSelect; // Create new DataSet to hold information from the worksheet. DataSet dsDeal = new DataSet(); // Fill the DataSet with the information from the worksheet. objAdapter1.Fill(dsDeal, "LMPPrices"); for (int k = 0; k < dsDeal.Tables[0].Rows.Count; k++) { for (int j = 0; j < dsDeal.Tables[0].Columns.Count; j++) { Console.Write(dsDeal.Tables[0].Rows[k][j].ToString() + " -> "); } Console.WriteLine(); } Console.ReadLine(); Best Regards,
Take a look at http://www.codeproject.com/useritems/Excel_Application_in_C_.asp[^]
-
Hi All, I have an excel file in this format, which I am trying to read using c# 04/05/2006 46 47 25 26 27 15 vgc 57 I am not able to read the values of row[1][0] and row[2][0], because in this column, the datatype of value in first row is date (04/05/2006). In the same way, I am not able to read the row[2][1], because in this column, the datatype of value in first row is a numeric. So, according to my perception, you need to have same datatype values in any particular column to read it in C#. I am looking for any option to read these type of excel files with values of different datatypes in a particular column. Any input will be highly appreciated. FYI, This is the code snippet, I am using to read this excel file. // Create connection string variable. Modify the "Data Source" parameter as appropriate for your environment. String filePath = "D:\\ProjectInfo\\HU Mail\\test.xls"; String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + "; Extended Properties=\"Excel 8.0;HDR=NO;\""; // Create connection object by using the preceding connection string. OleDbConnection objConn = new OleDbConnection(sConnectionString); // Open connection with the database. objConn.Open(); DataTable dt = null; // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] strSheetName = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { strSheetName[i] = row["TABLE_NAME"].ToString(); i++; } string strComand = "select * from [" + strSheetName[2] + "]"; OleDbCommand objCmdSelect = new OleDbCommand(strComand, objConn); // Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement. OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); // Pass the Select command to the adapter. objAdapter1.SelectCommand = objCmdSelect; // Create new DataSet to hold information from the worksheet. DataSet dsDeal = new DataSet(); // Fill the DataSet with the information from the worksheet. objAdapter1.Fill(dsDeal, "LMPPrices"); for (int k = 0; k < dsDeal.Tables[0].Rows.Count; k++) { for (int j = 0; j < dsDeal.Tables[0].Columns.Count; j++) { Console.Write(dsDeal.Tables[0].Rows[k][j].ToString() + " -> "); } Console.WriteLine(); } Console.ReadLine(); Best Regards,
And there's a comment in http://www.codeproject.com/office/excel_using_oledb.asp[^] that says Apparently, the engine reads the first 8 cells of each column and check it's data type. if most of the first 8 cells are int / double, the problem remains.