We help IT Professionals succeed at work.

Splitting a Data Table based on row number

JackDev68
JackDev68 asked
on
I have Dataset with DataTable(dt[0]) having 150,000 rows.  Is there any way I can split DataTable to 3 different datatables(dt[0],dt[1],dt[3] ) with each having 50,000 rows? To be more specific I want to split DataTable based on rownumber.

My goal is to overcome the Excel row limit by being able to split an export to three separate tabs.
Comment
Watch Question

I assume that you are using a stored procedure to retrieve the data. Instead of using this kind of SQL statement:
Select field_list
from tables
where ...

Use something like this:

Select field_list
from tables
where id between 1 and 50000

Select field_list
from tables
where id between 50001 and 100000

Select field_list
from tables
where id between > 100000

This will return a dataset containing 3 datatables.
Commented:

DataSet dst = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("ID");
for(int i=0;i<150000;i++)
{
dt.Rows.Add(i.ToString());
}
dst.Tables.Add(dt);
DataSet dst2 = new DataSet();
for (int i = 0; i < 3; i++)
{
DataTable dtTemp = new DataTable();
dtTemp = dt.Clone();
dtTemp.TableName = i.ToString();
for (int j = 0; j < 50000; j++)
{
dtTemp.ImportRow(dt.Rows[i * 50000 + j]);
}
dst2.Tables.Add(dtTemp);
}