Copy column data to another data type

Question

I have a pair of DataTable and I need to copy them to another DataTable. For example, as you can see in the attached figure, I need to take all the data from the source table 1 and copy it to the first 2 columns of the Dest table, copy the source table 2 to the next 2 columns and so on. How can this be easily achieved?

Edit: I need to read several excel files (I store each file in a datatable), and I won’t know exactly how many source tables I will have, so it needs to be done dynamically somehow.

Answer 1

Assuming your source tables have the same structure, you can use Table.Copy () to create your dest table, and then copy the data into a loop:

List<DataTable> sourceTables = getYourSourceTablesMethod(); if (sourceTables.Length>0) { DataTable destTable = sourceTables[0].Copy(); for (int i = 1; i < sourceTables; i++) { foreach (DataRow drow in sourceTables[i].Rows) destTable.Rows.Add(drow.ItemArray); } } 

Answer 2

You can use the Merge method provided by the framework to use and see Microsoft Datatable Merge for more information.

Answer 3

You should find the relationship between this source table. For example, they have the same identifier, you can copy them as

 insert into destTable( select s1.col1, s1.col2, s2.col3, s2.col4, s3.col5, s3.col6, s4.col7, s4.col8 from sourcetable1 s1, sourcetable2 s2, sourcetable3 s3, sourcetable4 s4 where s1.id = s2.id and s2.id = s3.id and s3.id = s4.id) 

Answer 4

You can run queries, for example:

 select col1,col2 into sourcetable1 from destTable union select col3,col4 into sourcetable2 from destTable union select col5,col6 into sourcetable3 from destTable union select col7,col8 into sourcetable4 from destTable 

Or, you can follow the technique mentioned above here .

In the case of data rows, this could be:

 foreach (DataRow row in DestTable) { SourceTable1.ImportRow(row); } 

Answer 5

First, you can create a target table, add its columns (summing the number of columns in all input tables), and then add them to the rows, combining separate arrays of values ​​for each row in the input tables.

Of course, the rows in the resulting DataTable will contain values ​​since they are displayed in a top-down direction for each input table (aligned on top). It also means that the number of resulting rows is the number of rows in the largest input table.

First, we initialize and populate the List<DataTable> variable, then we will make the connection using this variable as a method parameter:

 #region table collection initialization List<DataTable> dts = new List<DataTable>(); var dt = new DataTable(); dt.Columns.Add("Test0", typeof(string)); dt.Rows.Add(1); dt.Rows.Add(2); dts.Add(dt); dt = new DataTable(); dt.Columns.Add("Test1", typeof(int)); dt.Rows.Add(2); dts.Add(dt); dt = new DataTable(); dt.Columns.Add("Test3", typeof(int)); dt.Columns.Add("Test4"); dt.Columns.Add("Test5", typeof(int)); dt.Rows.Add(3, "a", 1); dt.Rows.Add(4); dt.Rows.Add(5, "a"); dt.Rows.Add(null, "a"); dts.Add(dt); dt = new DataTable(); dt.Columns.Add("Test6", typeof(DateTime)); dt.Columns.Add("Test7", typeof(int)); dt.Rows.Add(DateTime.Now); dts.Add(dt); #endregion // sample method usage var result = GetJoinedTable(dts); 

Create a GetJoinedTable method that returns the resulting joined table into the result variable:

 private DataTable GetJoinedTable(List<DataTable> dts) { var dest = new DataTable(); //will be used if you have non-unique column names int counter = 0; foreach (var column in dts .SelectMany<DataTable, DataColumn>(t => t.Columns.Cast<DataColumn>())) { dest.Columns.Add(column.ColumnName, column.DataType); // if you have non-unique column names use the following instead //dest.Columns.Add(String.Format("column_{0}", counter++), // column.DataType); } List<object> rowItems; for (int i = 0; i < dts.Max(t => t.Rows.Count); i++) { rowItems = new List<object>(); for (int j = 0; j < dts.Count; j++) { if (dts[j].Rows.Count > i) { var r = dts[j].Rows[i].ItemArray .Select((v, index) => (v == null || v == System.DBNull.Value) ? GetDefault(dts[j].Columns[index].DataType) : v); rowItems.AddRange(r); } else { for (int c = 0; c < dts[j].Columns.Count; c++) { rowItems.Add(GetDefault(dts[j].Columns[c].DataType)); } } } dest.Rows.Add(rowItems.ToArray()); } return dest; } 

You will also need to add the following method, which returns the corresponding default column value, based on the DataType property of the column:

 object GetDefault(Type t) { if (t.IsValueType) { return Activator.CreateInstance(t); } else { return null; } }