Reading an excel file missing column

[tshad]

I have an excel file that has 3 columns.

Reading this into a DataTable

I am reading the xls file into the DataTable, like so:

connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" +
AppSettings.InputFilePath + Path.GetFileName(strFile) + ";Extended
Properties=\"Excel 8.0;HDR=No;IMEX=1\"");

connection.Open();
tableName =
((DataTable)connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null)).Rows[0].ItemArray[2].ToString();

da = new OleDbDataAdapter("SELECT * FROM " + tableName, connection);
da.Fill(ds);

And the columns look something like this:

COUNT KEY AMT
11 0A102 $38.00
23 0A111 $150.88

This works fine and ends up with 3 columns in my DataTable, as it should.

But if I Cut the 2nd column and paste it before count so that the file looks
like:

KEY COUNT AMT
0A102 11 $38.00
0A111 23 $150.88

When reading the xls file, the datatable now shows that there are only 2
columns (dt.Columns.Count = 2)

When looping through the collection of rows I get:

dr[0] = "COUNT"
dr[1] = "AMT"

dr[2] - 'dr[2]' threw an exception of type
'System.IndexOutOfRangeException'

 

[tshad]

I tried opening the file in a different way and it brings back 7 columns
(which is fine - there are 4 empty columns).

string mstrConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";

string strSQL = "SELECT * FROM [Query1$]";
mstrConnection = string.Format(mstrConnection,
@"C:\AutoUPSDocs\567_010109_022809.xls");

OleDbDataAdapter objCmd = new OleDbDataAdapter(strSQL,
mstrConnection);
DataSet ds2 = new DataSet();
objCmd.Fill(ds2, "ExcelInfo");

I put this before my code and ran both through and mine still comes back
with 2 columns and the new one comes back with 7.

Why would that be?

Thanks,

Tom

 

[tshad]

I found out what the problem was but not sure why.

I was using "SELECT * from tableName". For some reason, that worked fine
for the file before I moved the column. After I moved the column it
doesn't.

You have to have the Select as:

strSQL = String.Format("SELECT * FROM [{0}$]",tableName);

This puts a "[" before the sheet name(tableName) and "$]" after the sheet
name. Now it works fine.

Not sure what the difference is.

Tom

I tried opening the file in a different way and it brings back 7 columns
(which is fine - there are 4 empty columns).

string mstrConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";

string strSQL = "SELECT * FROM [Query1$]";
mstrConnection = string.Format(mstrConnection,
@"C:\AutoUPSDocs\567_010109_022809.xls");

OleDbDataAdapter objCmd = new OleDbDataAdapter(strSQL,
mstrConnection);
DataSet ds2 = new DataSet();
objCmd.Fill(ds2, "ExcelInfo");

I put this before my code and ran both through and mine still comes back
with 2 columns and the new one comes back with 7.

Why would that be?

Thanks,

Tom

I have an excel file that has 3 columns.

Reading this into a DataTable

I am reading the xls file into the DataTable, like so:

connection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
AppSettings.InputFilePath + Path.GetFileName(strFile) + ";Extended
Properties=\"Excel 8.0;HDR=No;IMEX=1\"");

connection.Open();
tableName =
((DataTable)connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null)).Rows[0].ItemArray[2].ToString();

da = new OleDbDataAdapter("SELECT * FROM " + tableName, connection);
da.Fill(ds);

And the columns look something like this:

COUNT KEY AMT
11 0A102 $38.00
23 0A111 $150.88

This works fine and ends up with 3 columns in my DataTable, as it should.

But if I Cut the 2nd column and paste it before count so that the file
looks like:

KEY COUNT AMT
0A102 11 $38.00
0A111 23 $150.88

When reading the xls file, the datatable now shows that there are only 2
columns (dt.Columns.Count = 2)

When looping through the collection of rows I get:

dr[0] = "COUNT"
dr[1] = "AMT"

dr[2] - 'dr[2]' threw an exception of type
'System.IndexOutOfRangeException'