OleDbDataAdapter issue

[mtgomes]

Hello,

I'm having a problem when getting data from an excel file (source code
bellow). All works fine, however the datatable filled only has 255
columns.. The files that I'm importing are quite big (more than 1000
columns, but columns are variable), and manual parsing the file will
be quite slow..

This may be a trivial issue, however I can't seem to figure out how to
get it working.

Thanks in advance for the help.

Cheers!!
MG

Source code:

DataSet ds = new DataSet
();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source="....xlsx";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
OleDbConnection conn = new OleDbConnection
(connectionString);
try
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(@"SELECT * FROM [" + sheet +
"$]", conn);
da.Fill(ds);

}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}

 

[S. Justin Gengo]

MG,

I don't know for certain that this is a correct answer. I found it in a post
to the experts exchange website as the accepted solution for the same
problem. But here you go (It's not great news):

Is it possible to have more than 255 columns in ADO.net DataTable?

Yes, of course. I just added 1000000 columns to a data table just to
verify this.

It seems to be a limitation in the Excel database driver.
Excel prior to Excel 2007 did not allow more than 255 columns in a
sheet,
so it's very likely that the database driver doesn't account for more
than that.

You could try to update MDAC to the most recent version.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[mtgomes]

MG,

I don't know for certain that this is a correct answer. I found it in a post
to the experts exchange website as the accepted solution for the same
problem. But here you go (It's not great news):

    Is it possible to have more than 255 columns in ADO.net DataTable?

    Yes, of course. I just added 1000000 columns to a data table justto
verify this.

    It seems to be a limitation in the Excel database driver.
    Excel prior to Excel 2007 did not allow more than 255 columns in a
sheet,
    so it's very likely that the database driver doesn't account for more
than that.

    You could try to update MDAC to the most recent version.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer

Free code library at:www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

Hello,

I'm having a problem when getting data from an excel file (source code
bellow). All works fine, however the datatable filled only has 255
columns.. The files that I'm importing are quite big (more than 1000
columns, but columns are variable), and manual parsing the file will
be quite slow..

This may be a trivial issue, however I can't seem to figure out how to
get it working.

Thanks in advance for the help.

Source code:

DataSet ds = new DataSet
();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source="....xlsx";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
OleDbConnection conn = new OleDbConnection
(connectionString);
try
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(@"SELECT * FROM [" + sheet +
"$]", conn);
da.Fill(ds);

}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}

Thanks for the quick reply.
I'll try to find a workaround to get this working.

Cheers,
MG

 

[S. Justin Gengo]

MG,

I have one thought on a possible workaround. You could create multiple
select statements each pulling 255 columns at a time. Each one could pull
the various column names you need until you have them all. Then you'd have
to combine the data tables...

It isn't a great way to do it. But perhaps as a last resort?

--
Sincerely,

S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

MG,

I don't know for certain that this is a correct answer. I found it in a
post
to the experts exchange website as the accepted solution for the same
problem. But here you go (It's not great news):

Is it possible to have more than 255 columns in ADO.net DataTable?

Yes, of course. I just added 1000000 columns to a data table just to
verify this.

It seems to be a limitation in the Excel database driver.
Excel prior to Excel 2007 did not allow more than 255 columns in a
sheet,
so it's very likely that the database driver doesn't account for more
than that.

You could try to update MDAC to the most recent version.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer

Free code library at:www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

Hello,

I'm having a problem when getting data from an excel file (source code
bellow). All works fine, however the datatable filled only has 255
columns.. The files that I'm importing are quite big (more than 1000
columns, but columns are variable), and manual parsing the file will
be quite slow..

This may be a trivial issue, however I can't seem to figure out how to
get it working.

Thanks in advance for the help.

Source code:

DataSet ds = new DataSet
();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source="....xlsx";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
OleDbConnection conn = new OleDbConnection
(connectionString);
try
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(@"SELECT * FROM [" + sheet +
"$]", conn);
da.Fill(ds);

}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}

Thanks for the quick reply.
I'll try to find a workaround to get this working.

Cheers,
MG

 

[mtgomes]

MG,

I have one thought on a possible workaround. You could create multiple
select statements each pulling 255 columns at a time. Each one could pull
the various column names you need until you have them all. Then you'd have
to combine the data tables...

It isn't a great way to do it. But perhaps as a last resort?

--
Sincerely,

S. Justin Gengo, MCP
Web Developer

Free code library at:www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

MG,

I don't know for certain that this is a correct answer. I found it in a
post
to the experts exchange website as the accepted solution for the same
problem. But here you go (It's not great news):

Is it possible to have more than 255 columns in ADO.net DataTable?

Yes, of course. I just added 1000000 columns to a data table just to
verify this.

It seems to be a limitation in the Excel database driver.
Excel prior to Excel 2007 did not allow more than 255 columns in a
sheet,
so it's very likely that the database driver doesn't account for more
than that.

You could try to update MDAC to the most recent version.
S. Justin Gengo, MCP
Web Developer

Free code library at:www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

news:482d1e73-2ae4-46a6-af96-45d9af5908de@k24g2000pri.googlegroups.com....

Hello,
I'm having a problem when getting data from an excel file (source code
bellow). All works fine, however the datatable filled only has 255
columns.. The files that I'm importing are quite big (more than 1000
columns, but columns are variable), and manual parsing the file will
be quite slow..
This may be a trivial issue, however I can't seem to figure out how to
get it working.
Thanks in advance for the help.
Cheers!!
MG
Source code:
DataSet ds = new DataSet
();
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source="....xlsx";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
OleDbConnection conn = new OleDbConnection
(connectionString);
try
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(@"SELECT * FROM [" + sheet +
"$]", conn);
da.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}

Thanks for the quick reply.
I'll try to find a workaround to get this working.

Cheers,
MG

I've tested your solution (using a dataset that stores several
datatables with 255 columns each) and using Office.Interop.Excel
scaning all the rows / columns into a single DataTable.
Checking the processing timings, the first one is quite faster that
the other, so I'll take your advice and go with that one..

Thanks a lot for the feedback.

Cheers
MG