Reading excel file giving 2 sheets

[tshad]

I am trying to read some excel sheets and am getting what seems to be 2
sheets but I have only one sheet in one of my tables ( I do want to be able
to read multiple sheets).

My code is:
**********************************************************
string strSQL = "SELECT * FROM [{0}$]";
string mstrConnectionXLS = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
mstrConnectionXLS = string.Format(mstrConnectionXLS, strFile);
connection = new OleDbConnection(mstrConnectionXLS);
connection.Open();

DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
if (dt == null)
{
return null;
}
foreach (DataRow row in dt.Rows)
{
tableName = row["TABLE_NAME"].ToString().Replace("$", "");
da = new OleDbDataAdapter(String.Format(strSQL,
Regex.Replace(tableName, "'", "")), connection);
da.Fill(ds, tableName);
}
**************************************************************

This works pretty well but in my loop I am getting 2 table names:
Query1
Query1$

My program thinks there are 2 sheets so it ends up running it with the same
TableName.

If I look at my debugger, it shows the same data for both dt.Rows[0] and
dt.Rows[1].

Why is that and how do I tell how many sheets I have?

I thought this was the way?

Thanks,

Tom

 

[Alvin Bruney - ASP.NET MVP]

There's a bug in your code. The DataTable that the GetOleDbSchemaTable
method returns has a row for each object that meets the OleDbSchemaGuid type
and the restriction criteria. The DataTable has a column for each of the
restriction columns, which is followed by additional schema information
based on the OleDbSchemaGuid field. You'll likely get duplicate tables
depending on the schema data. Why don't you just get the dataset and extract
the table from it. I do not see where you actually use schema information in
the code.

So, replace this line DataTable dt =
connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
with a ds.Fill(newdataset, string.empty)
then extract the datable like so
if(ds != null && ds.Tables.Rows.Count > 0)
DataTable dt = ds.Tables[0];
then call your loop.

 

[tshad]

Not sure what you mean.

How do I do a ds.Fill ???

Also, I do the Schema stuff to get the name of the table to do the
connection string (one for each possible sheet in the file).

Not sure how you mean to set it up?

Thanks,

Tom

There's a bug in your code. The DataTable that the GetOleDbSchemaTable
method returns has a row for each object that meets the OleDbSchemaGuid
type and the restriction criteria. The DataTable has a column for each of
the restriction columns, which is followed by additional schema
information based on the OleDbSchemaGuid field. You'll likely get
duplicate tables depending on the schema data. Why don't you just get the
dataset and extract the table from it. I do not see where you actually use
schema information in the code.

So, replace this line DataTable dt =
connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
with a ds.Fill(newdataset, string.empty)
then extract the datable like so
if(ds != null && ds.Tables.Rows.Count > 0)
DataTable dt = ds.Tables[0];
then call your loop.

--
Vapordan
Shameless Author Plug
ASP.NET 4 by Example only $20
OWC Blackbook www.lulu.com/owc

I am trying to read some excel sheets and am getting what seems to be 2
sheets but I have only one sheet in one of my tables ( I do want to be
able to read multiple sheets).

My code is:
**********************************************************
string strSQL = "SELECT * FROM [{0}$]";
string mstrConnectionXLS = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
mstrConnectionXLS = string.Format(mstrConnectionXLS, strFile);
connection = new OleDbConnection(mstrConnectionXLS);
connection.Open();

DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
if (dt == null)
{
return null;
}
foreach (DataRow row in dt.Rows)
{
tableName = row["TABLE_NAME"].ToString().Replace("$", "");
da = new OleDbDataAdapter(String.Format(strSQL,
Regex.Replace(tableName, "'", "")), connection);
da.Fill(ds, tableName);
}
**************************************************************

This works pretty well but in my loop I am getting 2 table names:
Query1
Query1$

My program thinks there are 2 sheets so it ends up running it with the
same TableName.

If I look at my debugger, it shows the same data for both dt.Rows[0] and
dt.Rows[1].

Why is that and how do I tell how many sheets I have?

I thought this was the way?

Thanks,

Tom