Splitting the datatable into multiple datatables

Question

These are the contents of my datatable dtAllData.

 column_1  column_2  column_3    
 --------  --------  --------  
 sec_1       Test1        2
 sec_1       Result1      5
 sec_1       Unit1        2 
 sec_2       Test2        2           
 sec_2       Result2      2
 sec_2       Unit2        5
 sec_3       Test3        2 
 sec_3       Result3      2     
 sec_3       Unit3        2

I need to split it into multiple datatables on the basis of contents in column column_1.

So in this case I'm supposed to get 3 tables (one having all rows with sec_1, other with sec_2 & another with sec_3).

I tried this:

var dtArray = dtAllData.AsEnumerable() 
                            .GroupBy(row => new
                            {
                                column_1 = (string)row["column_1"]
                            });
DataTable[] array = new DataTable[dtArray.Count()];

How can I get tables in array from dtArray?

What is purpose of that split? In ASA/ASE databases (Sybase products) replication based on some column is possible. Im not SQLServer specialisty, but maybe SqlServer have similar functionality? — Kamil, Feb 27 '13 at 13:30
You write _I need to split it into multiple datatables_ after _I'm supposed to get 3 tables_ you want to split in multiple database or tables ? Why you want to group ? You simply filter table by where clausole — Max, Feb 27 '13 at 13:31

Kaf · Accepted Answer · 2013-02-27T14:13:30.180

4

Using CopyToDataTable() method to get the data into a new DataTable.

var Result =  from x in dtAllData.AsEnumerable()
              where x.Field<string>("column_1") == "sec_1"
              select x;
DataTable t1 = Result.CopyToDataTable();

Another way is using LoadDataRow() method. Here is an example

edited Feb 27 '13 at 14:13

answered Feb 27 '13 at 13:39

Kaf

33,101
7
58
78

can you please look into one of my [question](https://stackoverflow.com/questions/55688229/how-to-split-data-table-into-multiple-tables-with-adding-1-minute-delay) related to it? – Moeez Apr 16 '19 at 06:39
@kaf i use this solution in datatable with 5 million rows but i get out of memory exception, can i help me to avoid this exception, i need a solution to manage this with memory optimized way – Hasan Fathi Mar 26 '20 at 06:20

score 0 · Answer 2 · answered Feb 27 '13 at 13:58

0

If you know the exact names of column_1 you can use .Select .Select("column_1 = sec_1")etc. or use a DataView

answered Feb 27 '13 at 13:58

happygilmore

3,008
4
23
37

mike44 · Answer 3 · 2013-02-28T05:31:51.623

0

Modifying Kaf's solution, I achieved what I wanted as:

var uniqueList = dtAllData.AsEnumerable().Select(x=> x.Field<string>("column_1")).Distinct();
List<string> myList = new List<string>();
myList =uniqueList.ToList();

DataTable[] array = new DataTable[myList.Count()];
int index = 0;
foreach (string item in myList)
{
    var Result =  from x in dtAllData.AsEnumerable()
                  where x.Field<string>("column_1") == item
                  select x;
    DataTable table = Result.CopyToDataTable();
    array[index] = table;
    index++;
}

So the array contains 3 datatables with different values of column_1

edited Feb 28 '13 at 05:31

answered Feb 27 '13 at 14:09

mike44

802
5
15
36

can you please look into one of my [question](https://stackoverflow.com/questions/55688229/how-to-split-data-table-into-multiple-tables-with-adding-1-minute-delay) related to it? – Moeez Apr 16 '19 at 06:40

score -2 · Answer 4 · edited Sep 06 '16 at 16:52

Function splitDataTable(ByVal tbl As DataTable) As DataTable()
    Dim tableCount = Math.Ceiling(tbl.Rows.Count / NewCountRows)

    Dim Divisor = tbl.Rows.Count / tableCount
    Dim tables = tbl.AsEnumerable().Select(Function(r, i) New With {.row = r, .index = i}).GroupBy(Function(x) Math.Floor(x.index / Divisor)).Select(Function(g) g.Select(Function(x) x.row).CopyToDataTable())
    Return tables.ToArray
End Function

Splitting the datatable into multiple datatables

4 Answers4