Problem while reading data from excel in c#
-
Hi I have a code in c# to read data in excelsheet.
string connstr = "provider = Microsoft.Jet.Oledb.4.0;Data Source='C:\\aaa.xls';Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = "SELECT * FROM [Sheet1$]";OleDbCommand cmd = new OleDbCommand(strSQL, conn); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds); dt = ds.Tables\[0\];While reading i could not get few values in a particular column(say column B) and the values that are not being read are completely numeric(12345) while the others are alpha numeric(Ex: e0345) in the same column. Example Column B in excel has values as below U098ij i097hl 123453 gdtr56 When i see in the dataset, 3rd value is null. Dont know where i am going wrong. Please help me Thanks in advance Naina
Naina
-
Hi I have a code in c# to read data in excelsheet.
string connstr = "provider = Microsoft.Jet.Oledb.4.0;Data Source='C:\\aaa.xls';Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(connstr);
string strSQL = "SELECT * FROM [Sheet1$]";OleDbCommand cmd = new OleDbCommand(strSQL, conn); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds); dt = ds.Tables\[0\];While reading i could not get few values in a particular column(say column B) and the values that are not being read are completely numeric(12345) while the others are alpha numeric(Ex: e0345) in the same column. Example Column B in excel has values as below U098ij i097hl 123453 gdtr56 When i see in the dataset, 3rd value is null. Dont know where i am going wrong. Please help me Thanks in advance Naina
Naina
There is a universal problem when accessing excel through ADO and that is that ADO only scans the first 8 lines and then chooses what data type to return based on the data type of the majority of the returned results. Thus if the majority of the returned results are strings, then it thinks that all the data should be returned as strings, and ignores other data types. The best way to get around this is to add the instruction IMEX=1 to the extended properties instruction in the connection string. Thus your connection string would now look like this:
string connstr = @"provider = Microsoft.Jet.Oledb.4.0;Data Source='aaa.xls';Extended Properties='Excel 8.0;IMEX=1'";
Of course, the problem with this is that all your data is returned as a string, and numeric values will need to be parsed. Hope this helps
Live for today. Plan for tomorrow. Party tonight!
-
There is a universal problem when accessing excel through ADO and that is that ADO only scans the first 8 lines and then chooses what data type to return based on the data type of the majority of the returned results. Thus if the majority of the returned results are strings, then it thinks that all the data should be returned as strings, and ignores other data types. The best way to get around this is to add the instruction IMEX=1 to the extended properties instruction in the connection string. Thus your connection string would now look like this:
string connstr = @"provider = Microsoft.Jet.Oledb.4.0;Data Source='aaa.xls';Extended Properties='Excel 8.0;IMEX=1'";
Of course, the problem with this is that all your data is returned as a string, and numeric values will need to be parsed. Hope this helps
Live for today. Plan for tomorrow. Party tonight!
Wayne Does this force large numbers into e##### format when converting to strings?
Never underestimate the power of human stupidity RAH
-
Wayne Does this force large numbers into e##### format when converting to strings?
Never underestimate the power of human stupidity RAH
Yes, unfortunately it does do this if you have the cell format set to general or number. This can lead to a serious loss of data. If you format the cells to currency or accounting, then you get the actual value.
Live for today. Plan for tomorrow. Party tonight!
-
There is a universal problem when accessing excel through ADO and that is that ADO only scans the first 8 lines and then chooses what data type to return based on the data type of the majority of the returned results. Thus if the majority of the returned results are strings, then it thinks that all the data should be returned as strings, and ignores other data types. The best way to get around this is to add the instruction IMEX=1 to the extended properties instruction in the connection string. Thus your connection string would now look like this:
string connstr = @"provider = Microsoft.Jet.Oledb.4.0;Data Source='aaa.xls';Extended Properties='Excel 8.0;IMEX=1'";
Of course, the problem with this is that all your data is returned as a string, and numeric values will need to be parsed. Hope this helps
Live for today. Plan for tomorrow. Party tonight!
Hi Thanks for your reply and now i have included IMEX to extended properties. Now the problem is, large numbers are converted into e**** format Regards Naina
Naina
-
Hi Thanks for your reply and now i have included IMEX to extended properties. Now the problem is, large numbers are converted into e**** format Regards Naina
Naina
Hi, please read my answer to Mycroft above. The only solution is to format your cells as accounting or currency.
Live for today. Plan for tomorrow. Party tonight!