CODEWITHSUNDEEP
c#datatable
Eori
Eori

Reputation: 67

Convert data in DataTable

My problem is how to (efficiently) convert data in DataTable from one state to another. Let me show you an example:

Source data - one document in one row

Invoice|Date    |User |LineNo|Article|Qty   |LineNo|Article|Qty  |LineNo|Article|Qty
190201 |20130110|User1|01    |Art1   |12 PCE|02    |Art2   |7 PCE|03    |Art3   |4 PCE

Destination data - one document in several rows depending on how many lines are there in each document. Header data (invoice, data and user) is duplicated in each row.

Invoice|Date    |User |LineNo|Article|Qty
190201 |20130110|User1|01    |Art1   |12 PCE
190201 |20130110|User1|02    |Art2   |7 PCE
190201 |20130110|User1|03    |Art3   |4 PCE

The goal is to convert the one-row-per-document DataTable to multirow DataTable. Do you have any suggestions?

Upvotes: 0

Views: 94

Answers (1)

Schuere
Schuere

Reputation: 1649

Based on the information, i would look at something like this:

you have a predefined SourceDataTable with one row that exists out of 2 parts:

the Static part

Invoice | Date | user

the dynamic part which can occur N amount of times.

LineNo | Article | Qty

This makes the following sequence: Static | Dynamic1 | Dynamic2 | Dynamic3 | DynamicN ...

After this we know how to calculate the amount of dynamic rows.

var AmountOfNewLines = (SourceDataTable.Columns.Count - 3) / 3;

//the minus comes from the Static data
//divide by 3 because every Dynamic part exists out of 3 columns

based on this, you know how many rows there need to be created and you know how your format looks like:

//Creation of the DestinationTable datatable
var DestinationTable = new DataTable("Destination");

DestinationTable.Columns.Add("Invoice", typeof(String)); //Correct Type?
DestinationTable.Columns.Add("Date", typeof(String));
DestinationTable.Columns.Add("User", typeof(String));
DestinationTable.Columns.Add("LineNo", typeof(String));
DestinationTable.Columns.Add("Article", typeof(String));
DestinationTable.Columns.Add("Qty", typeof(String));

for(int i = 0; i < AmountOfNewLines - 1; i++)
{
  //Create a new row
  //Invoice|Date    |User |LineNo|Article|Qty
  foreach(DataRow d in SourceDataTable)
  {
    //Using the foreach makes the datarow more easier to approach
    //The Number inside the brackets explains which column is being approached.
    DestinationTable.Rows.Add(d[0], d[1], d[2], d[3+(3*i)], d[4+(3*i)], d[5+(3*i)])
  }

}

Upvotes: 1

Related Questions