Amr Alshazli Amr Alshazli - 2 days ago 4
C# Question

C# Issue while importing Excel sheets

I've searched the internet for this and couldn't really find a question like it.I am coding an application that takes data from Excel and view it.
All of my tests were correct, but suddenly I found that Importing a column that contains set of numbers in a row then letters will result in not showing the fields that contain those letters at all

PathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";";

OleDbConnection conn = new OleDbConnection(PathConn);
System.Data.DataTable dtSchema = new System.Data.DataTable();

conn.Open();
dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string Sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");

conn.Close();
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + Sheet1 + "]", conn);
DataSet ds = new DataSet();
myDataAdapter.Fill(ds);


the first dataset is the regular result


7841
7847s
2344
2262
7738
JD32916
JD329161
JD318161
JD31716
JD7643
JD21116
7194


the second dataset is the problematic result (notice that I removed the 's' from "7847s")


7841
7847
2344
2262
7738






7194


as you can see, all the fields with letters in them just disappeared,
it only happens where there are 5 or more consecutive fields with no letters in them. example (2nd number from the top contains 's' to prevent that error from happening)

Answer

Use IMEX=1 in your extended properties to treat all columns as text values. Without it, the Jet provider will infer a data type based on whatever type the majority of the values in that column is, which may not be correct.

Secondly, since your data does not have a header row, you should use HDR=NO in your extended properties as well.

Comments