Parsing DataTable from flat table to normailze table

Question

I have a requirement for B2B integration, I will reading flat table from dataset and parse to a normalize form of datatable.

I will have reapting colums in the datatable Sample Data

Invoice num     Amount    LineNum   Line Amout  Ledger
INV1            100       1         50          11101
INV1            100       2         50          25631

rows will repeat with different invoices

How can distinctly select into new datatable ? using ADO.NET

I want to parse the data into following format

Header Table

  Invoice num     Amount    
  INV1            100

Line Table

  Invoice num  LineNum   Line Amout  Ledger
  INV1          1         50          11101
  INV1          2         50          25631

QUESTION : I dont know what would be the best way to bring the above format ? I see examples usign linq , DataTable, Views ? I looking for a code snippet.

Anthony Pegram Anthony Pegram · Accepted Answer · 2011-04-22T00:39:53

OK, to start the problem, I am working with a DataTable and data defined as follows. Change names and types to suit your needs.

// I am building this table in code just for the purposes of this answer.
// If you already have your data table, ignore!
DataTable salesTable = new DataTable();
salesTable.Columns.Add("InvoiceNum", typeof(string));
salesTable.Columns.Add("Amount", typeof(decimal));
salesTable.Columns.Add("LineNum", typeof(int));
salesTable.Columns.Add("LineAmount", typeof(decimal));
salesTable.Columns.Add("Ledger", typeof(string));

// This is also just to populate data for the sample.
// Omit as you already have your data.
salesTable.Rows.Add("INV1", 100M, 1, 50M, "11101");
salesTable.Rows.Add("INV1", 100M, 1, 50M, "25631");

Notice that I'm using the overload of .Rows.Add that accepts a params object[] array. The values I'm passing in are in the order and type of the columns they should populate. The code below uses the same approach.

First thing I want to do is define the tables for your new normalized format. First, the header table.

DataTable headerTable = new DataTable();
headerTable.Columns.Add("InvoiceNum", typeof(string));
headerTable.Columns.Add("Amount", typeof(decimal));

And then the line item table.

DataTable lineTable = new DataTable();
lineTable.Columns.Add("InvoiceNum", typeof(string));
lineTable.Columns.Add("LineNum", typeof(int));
lineTable.Columns.Add("LineAmount", typeof(decimal));
lineTable.Columns.Add("Ledger", typeof(string));

After this, I'm going to utilize LINQ to group the original sales table based on the invoice number.

var groupedData = from row in salesTable.AsEnumerable()
                  group row by row.Field<string>("InvoiceNum") into grp
                  select grp;

After this, it's just a matter of iterating over the groups and adding the data to the new tables.

foreach (var invoiceGroup in groupedData)
{
    string invoiceNumber = invoiceGroup.Key;
    decimal amount = invoiceGroup.First().Field<decimal>("Amount");

    headerTable.Rows.Add(invoiceNumber, amount);

    foreach (DataRow row in invoiceGroup)
    {
        lineTable.Rows.Add(
                invoiceNumber,
                row.Field<int>("LineNum"),
                row.Field<decimal>("LineAmount"),
                row.Field<string>("Ledger")
            );
    }
}

And now you have your data in the normalized format you prefer. Again, change relevant column names and data types to suit your needs.

Parsing DataTable from flat table to normailze table

1 Answers