Excel datatable row limit? Please help!

[almurph]

Hi,


Am wondering can you help me please. I am processing an Excel file
(MS Office Excel 2003 11.8316.8221 SP3) with 60,000 rows using C#. I
am using the following connection string as shown below

*** CODE AS FOLLOWS ***

string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + file.FullName + ";Extended Properties=Excel 8.0";
OleDbConnection con = new OleDbConnection(strConnection);;
OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from
Sheet1", con);
DataTable dTable = new DataTable();
dAdapter.Fill(dTable);

*** END CODE ***

I am getting the impression when I cycle thru the rows that there is
some sort of limit to the datatable because it seems to get stuck at
around 51,000 or so (difficult to know where exactly).

My questions are: if this is the case how can I overcome it? Any
hints/suggestion/code-sample to aid this would be most appreciated.

Thank,
Al.

 

[Family Tree Mike]

Hi,


Am wondering can you help me please. I am processing an Excel file
(MS Office Excel 2003 11.8316.8221 SP3) with 60,000 rows using C#. I
am using the following connection string as shown below

*** CODE AS FOLLOWS ***

string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + file.FullName + ";Extended Properties=Excel 8.0";
OleDbConnection con = new OleDbConnection(strConnection);;
OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from
Sheet1", con);
DataTable dTable = new DataTable();
dAdapter.Fill(dTable);

*** END CODE ***

I am getting the impression when I cycle thru the rows that there is
some sort of limit to the datatable because it seems to get stuck at
around 51,000 or so (difficult to know where exactly).

My questions are: if this is the case how can I overcome it? Any
hints/suggestion/code-sample to aid this would be most appreciated.

Thank,
Al.

It should be limited at 65,536.
(http://office.microsoft.com/en-us/excel/HP051992911033.aspx)

Datatables have a much higher limit, on the order of 16 million.

Why can't you count the rows processed? Do you get an error?

 

[almurph]

Datatables have a *much* higher row limit the Excel worksheets...

1) Are you sure that there are no blank rows in the worksheet?

2) Are you sure that all of the rows contain valid data? Sometimes the OleDb
adaptor skips over invalid rows without throwing an error - it shouldn't,
but it does...

Mike & Mark,

Thanks for your replies. Yes, during runtime I pause and run
dTable.Rows.count and I get the value of 51,403. Problems is though
the Excel spreadsheet contains more rows than this, so when it reaches
this point i get an exception.
No, there are no blank rows in the spreadsheet and all data is
valid.

This issue appears to be related to this limit. My question is, do
you know of any way of increasing it? Any thoughts or suggestions much
appreciated.

Al.

 

[Anders Eriksson]

Hello,

Thanks for your replies. Yes, during runtime I pause and run
dTable.Rows.count and I get the value of 51,403. Problems is though
the Excel spreadsheet contains more rows than this, so when it reaches
this point i get an exception.

What is the exception?

// Anders

 

[Family Tree Mike]

Mike & Mark,

Thanks for your replies. Yes, during runtime I pause and run
dTable.Rows.count and I get the value of 51,403. Problems is though
the Excel spreadsheet contains more rows than this, so when it reaches
this point i get an exception.
No, there are no blank rows in the spreadsheet and all data is
valid.

This issue appears to be related to this limit. My question is, do
you know of any way of increasing it? Any thoughts or suggestions much
appreciated.

Al.

In addition to the common answer already given of "What exception?", I
am betting there is extra data on the row that fails, or the data
changes type, or format, or something like that. We'll have a better
guess with the exception.

 

[Rich P]

Something to consider when working with Excel and .Net is that you have
to massage the data a little bit for it to work correctly with .Net.
One thing you could try (the easiest thing) is to highlight your entire
worksheet and set the data format to text and SAVE THE FILE afterwards.
Or -- you could highlight groups of columns of the same data type and
set their formats respectively to Text, DateTime, Numeric. You probably
have a row with a cell that has a character that is causing the problem.

Another thing you could do is to isolation the offending Excel row. The
way to do this is to import the rows in chunks. Like copy 50,000 rows
to a new worksheet. See if you can import all of these rows. If yes,
then copy another group of rows to another new sheet and try to import
these. If you get an error, remove a group of the rows until you can
import that sheet. Then keep doing this until you are down to one (or
two) rows.

When you have isolated the offending row you can then isolate the
offending cell by either clearing out each cell of the used rows (or
write "test") in each cell and then copy the actually data to the row
one cell at a time (or in groups of cells) until you can't import that
row. Then you have isolated the offending cell.

Whenever I get Excel files from an external source I always check for
obvious inconsistencies. With 60,000 rows there is/are always one or
two rows that are not consistent with the rest of the data. You may
also want to check the Sheet.UsedRange.Select (from the code behind
module of Excel) to see if the Sheet.UsedRange includes blank rows. In
a code behind module you can type something like

Sub slsl()
ActiveSheet.UsedRange.Select
End Sub

And then either press F5 in the code behind window or in the Excel sheet
goto Tools/Macros and run the macro (slsl). This will select all used
rows and columns whether they are empty or not. The OleDataAdapter
basically does the same thing. It goes after the usedRange. If a row
had data and the data was deleted from the cells, but the row itself was
not deleted - that counts as a used row.

Rich