CODEWITHSUNDEEP
c#linqvalidationdatatable
Arukaito
Arukaito

Reputation: 65

Split DataTable Values(Comma Separated Values) Into multiple rows

I'm having a problem with my code which is the following example.

I have the following example data:

DataTable with 2 columns

Fruits         FruitGroup

Banana         FG1,FG2,FG5
Apple            
Melon          FG1,FG6,FG11

My Expected Outcome has to be this.

Fruits         FruitGroup

Banana         FG1
Banana         FG2
Banana         FG5
Apple          NA
Apple          NA 
Apple          NA 
Melon          FG1
Melon          FG6
Melon          FG11

This is my most recent failed attempt which I found here on Stack Overflow.

var tblfruitflat = fruitTable.Clone();
foreach (DataRow row in fruitTable.Rows)
{
    string frutas = row.Field<string>("Fruit");
    string frutasGroup = row.Field<string("FruitGroup").Split(',');
    for (int ir = 0; ir < frutasGroup.Length; ir++)
    {
        var newRow = tblfruitflat.Rows.Add();
        newRow.SetField("Fruits", frutas[ir]);
        newRow.SetField("FruitGroup", fruitGroup.ElementAtOrDefault(ir));

    }
}

Have to mention that I can have empty strings in my FruitGroup column which must be "NA". I'm lost in how to approach this.

Code Snippet Thanks To @Tim Schmelter

Upvotes: 0

Views: 3022

Answers (2)

SurelyTheresABetterWay
SurelyTheresABetterWay

Reputation: 136

Hopefully this will put your head in the right space. I didn't match the names up exactly to your implementation, but the logic still applies. 

//Work inside a for loop so you can manipulate the rows collection.
        for (int i = 0; i < previousLength; i++)
        {
            //First, get your split values.
            string[] vals = namesTable.Rows[i][0].ToString().Split(',');

            //only operate on rows that were split into multiples.
            if (vals.Length > 1)
            {
                //Add a  new row for each item parsed from value string
                foreach (string s in vals)
                {
                    DataRow newRow = namesTable.NewRow();
                    newRow[0] = namesTable.Rows[i].ToString();
                    newRow[1] = s;
                    namesTable.Rows.Add(newRow);
                }
            }
        }

        //Remove old rows
        for (int i = 0; i < previousLength; i++)
        {
            namesTable.Rows.RemoveAt(i);
        }

Upvotes: 1

NetMage
NetMage

Reputation: 26927

I'm not sure how your answer would get 3 rows with Apple, NA from one input row, but if one Apple row is okay, do this:

var ans = from r in d1 from fg in r.FruitGroup.Split(',') select new { r.Fruit, FruitGroup = (fg == String.Empty) ? "NA" : fg };

If you expect FruitGroup to always be three items and need three Apple rows, do this:

var ans2 = from r in d1 from fg in ((r.FruitGroup == String.Empty) ? new String[3] : r.FruitGroup.Split(',')) select new { r.Fruit, FruitGroup = (String.IsNullOrEmpty(fg)) ? "NA" : fg };

Upvotes: 1

Related Questions