How to split a large datatable into multiple datatables working on VS2005

Question

0.00/5 (No votes)

See more:

I create a module which upload text file, then suddenly I encounter error of out of memory. I realize that I reach the maximum size of file to be uploaded.

Now I create a condition on my module that if the rows.count is greater than 100,000 it will loop on my condition.

I divided rows.count to 30000 cause I assume that the largest file that I will upload will have 150,000 count.

c should be whole number so that the reason I always put + 1 if c is not a whole number.

I updated my code base on the solution below, but still got error. I know something missing in my code.

Thank you for the help.

What I have tried:

If (dt.Rows.Count > 100000) Then

Dim Columns As DataRow = dt.NewRow
Dim c = dt.Rows.Count / 30000
Dim SNumber = Convert.ToInt64(System.Math.Ceiling(c))

dt.Columns.Add("Key_Id", GetType(String))

If c = CDbl(c) Then
c = System.Math.Floor(c + 1)

Dim dtArr(c) As DataTable
For j As Integer = 0 To c

dtArr(j) = dt.Select("Key_Id <= 1 And Key_Id >= 30000").Clone()
dtArr(j) = dt.Select("Key_Id <= 30001 And Key_Id >= 60000").Clone()
dtArr(j) = dt.Select("Key_Id <= 60001 And Key_Id >= 90000").Clone()
dtArr(j) = dt.Select("Key_Id <= 90001 And Key_Id >= 120000").Clone()
dtArr(j) = dt.Select("Key_Id <= 120001 And Key_Id >= 150000").Clone()

Next

End If

End If

Posted 11-Mar-18 17:11pm

1DSparrows

Updated 12-Mar-18 21:19pm

v6

Add a Solution

Comments

Jαved 12-Mar-18 1:57am

your code seems to be wrong. the variable "dtArr" is of type "double".
i.e. Dim dtArr = _New_dt.Rows.Count / 30000

1DSparrows 12-Mar-18 2:01am

dtArr has the possibility to be decimal,

Jαved 12-Mar-18 2:35am

nope, it is decimal the code will not work and give error.

1DSparrows 12-Mar-18 2:38am

so dtArr is the only wrong in my coding?

Jαved 12-Mar-18 2:55am

Yes, declare another variable to set count to split and declare dtArr to be DataTable array as - Dim dtArr() as DataTable and in loop set array as-
dtArr(i) = dt.Select("key_id <= 1 And key_id >= 30000").CopyToDataTable()

1DSparrows 12-Mar-18 3:26am

Dim _New_dt As New DataTable()

_New_dt = dt

dt.Columns.Add("Key_Id", GetType(String))

Dim c = _New_dt.Rows.Count / 30000
Dim SheetNumber = Convert.ToInt64(Math.Ceiling(c))
If c = CDbl(c) Then
c = Math.Floor(c + 1)

Dim dtArr(c) As DataTable

dtArr(c) = dt.Select("key_id <= 1 And key_id >= 30000").Clone()
dtArr(c) = dt.Select("key_id <= 61000 And key_id >= 90000").Clone()
dtArr(c) = dt.Select("key_id <= 91000 And key_id >= 120000").Clone()
dtArr(c) = dt.Select("key_id <= 121000 And key_id >= 150000").Clone()

1DSparrows 12-Mar-18 3:26am

I come up with this code base on your suggestion but encounter error. Unable to cast object of type 'System.Data.DataTable' to type 'myStronglyTypedDataTable'

Jαved 12-Mar-18 3:32am

use below code-

Dim _New_dt As New DataTable()

_New_dt = dt

dt.Columns.Add("Key_Id", GetType(String))

Dim c = _New_dt.Rows.Count / 30000
Dim SheetNumber = Convert.ToInt64(System.Math.Ceiling(c))
' If c = CDbl(c) Then
'c = System.Math.Floor(c + 1)

Dim dtArr(c) As DataTable
For j = 0 To c
dtArr(j) = dt.Select("key_id <= 1 And key_id >= 30000").CopyToDataTable()
dtArr(j) = dt.Select("key_id <= 61000 And key_id >= 90000").CopyToDataTable()
dtArr(j) = dt.Select("key_id <= 91000 And key_id >= 120000").CopyToDataTable()
dtArr(j) = dt.Select("key_id <= 121000 And key_id >= 150000").CopyToDataTable()
Next

'End If

1DSparrows 12-Mar-18 3:37am

CopyToDataTable is not a member of system array

1DSparrows 12-Mar-18 3:41am

I used your code but still getting the error, Unable to cast object of type 'System.Data.DataRow[]' to type 'System.Data.DataTable'and CopyToDataTable is not a member of system array.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jαved · Answer 1 · 2018-03-11T19:23:00

Solution 1

Hi,
go through this link-
c# - Split datatable into multiple fixed sized tables[^]

Posted 11-Mar-18 19:23pm

Jαved

Comments

1DSparrows 12-Mar-18 1:25am

@javed I already go through that link but haven't fixed my code on the right one.

Jαved 12-Mar-18 1:58am

what is the issue with solutions given in the link?

1DSparrows 12-Mar-18 3:00am

Dim tables As List(Of DataTable) = New List(Of DataTable)()
Dim batchSize As Integer
Dim c As Integer = 0
Dim j As Integer = 1
Dim rowCount As Integer = 0
Dim tempDt As DataTable = dt.Clone()
tempDt.TableName = "scm_move_order" & j.ToString()
tempDt.Clear()
For Each row As DataRow In dt.Rows
rowCount += 1
Dim newRow As DataRow = tempDt.NewRow()
newRow.ItemArray = row.ItemArray
tempDt.Rows.Add(newRow)
c += 1
If c = batchSize Or rowCount = dt.Rows.Count Then
tables.Add(tempDt)
j += 1
tempDt = dt.Clone()
tempDt.TableName = "scm_move_order" & j.ToString()
tempDt.Clear()
c = 0
End If
Next

1DSparrows 12-Mar-18 3:00am

I followed this code but still got the error of out of memory

Jαved 12-Mar-18 3:16am

you are looping through all the rows in main table 'dt' un-necessarily and the batchSize check is in middle of the loop it should be the first check in the loop.
Although this code will take lot of time and is not optimized.

Maciej Los 12-Mar-18 5:30am

5ed!

1DSparrows 12-Mar-18 5:45am

What?

Maciej Los 12-Mar-18 5:53am

Take a look at top-right corner of solution. It is called: voting system.

1DSparrows 12-Mar-18 5:55am

Oh.okay

Maciej Los · Answer 2 · 2018-03-12T01:06:00

Seems, your logic is wrong... Take a look at your code, especially at underlined parts:

C#

dtArr() = dt.Select("key_id <= 1 And key_id >= 30000").Clone()
dtArr() = dt.Select("key_id <= 31000 And key_id >= 60000").Clone()
dtArr() = dt.Select("key_id <= 61000 And key_id >= 90000").Clone()
dtArr() = dt.Select("key_id <= 91000 And key_id >= 120000").Clone()
dtArr() = dt.Select("key_id <= 121000 And key_id >= 150000").Clone()

You mixed less than and greater than operators! Another issue is that you lose 1000 records (31000-3000=1000) between each select!

If you would like to split data into parts with 30000 records, you can use Linq. Take a look at example...

Method #1 - copying data by `key_id`:

C#

//source table
DataTable src = new DataTable();
src.Columns.Add(new DataColumn("key_id", typeof(int)));
//add example data
src = Enumerable.Range(1,100).Select(x=>src.LoadDataRow(new object[]{x}, false)).CopyToDataTable();

int increasement = 30;
int curVal = 1;
//temporary object type of datarow
DataRow[] result = null;
//destination dataset, here we'll store smaller datatables
DataSet ds = new DataSet();
do
{
	result = src.AsEnumerable()
		.Where(x=>x.Field<int>("key_id")>=curVal && x.Field<int>("key_id")<curVal+increasement)
		.ToArray();
	if(result.Length>0)
	{
		DataTable tmp = src.Clone();
		tmp = result.CopyToDataTable();
		ds.Tables.Add(tmp);
	}
	curVal+=increasement;
} while(result.Length>0);

VB.NET

Dim src As DataTable = New DataTable()
src.Columns.Add(New DataColumn("key_id", Type.GetType("System.Int32")))
'add example data
src = Enumerable.Range(1,100).Select(Function(x) src.LoadDataRow(New Object(){x}, False)).CopyToDataTable()

Dim increasement As Integer = 30
Dim curVal As Integer = 1
Dim result As DataRow()
Dim ds As DataSet = New DataSet()
Do 
	result = src.AsEnumerable() _
		.Where(Function(x) x.Field(Of Integer)("key_id")>=curVal And x.Field(Of Integer)("key_id")<curVal+increasement) _
		.ToArray()
	If result.Length>0 Then
		Dim tmp As DataTable = src.Clone()
		tmp = result.CopyToDataTable()
		ds.Tables.Add(tmp)
	End If
	curVal+=increasement
Loop While(result.Length>0)

Method #2 - copying the same number of rows:

C#

int increasement = 30;
int curVal = 0; //start with 0!
DataRow[] result = null;
DataSet ds = new DataSet();
do
{
	result = src.AsEnumerable()
		.Skip(curVal) //
		.Take(increasement)
		.ToArray();
	if(result.Length>0)
	{
		DataTable tmp = src.Clone();
		tmp = result.CopyToDataTable();
		ds.Tables.Add(tmp);
	}
	curVal+=increasement;
} while(result.Length>0);

VB.NET

Dim increasement As Integer = 30
Dim curVal As Integer = 0
Dim result As DataRow()= Nothing
Dim ds As DataSet = New DataSet()
Do 
	result = src.AsEnumerable() _
		.Skip(curVal) _
		.Take(increasement) _
		.ToArray()
	If result.Length>0 Then
		Dim tmp As DataTable = src.Clone()
		tmp = result.CopyToDataTable()
		ds.Tables.Add(tmp)
	End If
	curVal+=increasement
Loop While(result.Length>0)

[EDIT]
Non-Linq solution - method #1:

VB.NET

'needed:
'Imports System.Data
'Imports System.Text

Dim src As DataTable = New DataTable()
src.Columns.Add(New DataColumn("key_id", Type.GetType("System.Int32")))
'add example data
For i As Integer = 0 To 100
    src.Rows.Add(New Object() {i})
Next

Dim increasement As Integer = 30
Dim curVal As Integer = 1
Dim result As DataRow()
Dim ds As DataSet = New DataSet()
Do
    result = src.Select(String.Format("key_id >={0} And key_id<{1}", curVal, curVal + increasement)).Clone()
    If result.Length > 0 Then
        Dim tmp As DataTable = src.Clone()
        Dim sb As StringBuilder = New StringBuilder()
        For Each dr As DataRow In result
            tmp.Rows.Add(New Object() {dr("key_id")})
            sb.Append(String.Format("{0};", dr("key_id")))
        Next
        ds.Tables.Add(tmp)
        Console.WriteLine("{0}", sb.ToString())
        sb = Nothing
    End If
    curVal += increasement
Loop While (result.Length > 0)

Console.ReadKey()

Result (4 tables):

txt

1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;
31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;
61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;
91;92;93;94;95;96;97;98;99;100;

[END EDIT]

Good luck!

How to split a large datatable into multiple datatables working on VS2005

2 solutions

Solution 1

Solution 2

Method #1 - copying data by key_id:

Method #2 - copying the same number of rows:

Add your solution here

Preview 0

Existing Members

...or Join us

Method #1 - copying data by `key_id`: