CODEWITHSUNDEEP
c#linqdatatable
learner999
learner999

Reputation: 195

Create multiple DataTable rows based on a column with comma separated values

I'm trying to parse a comma-separated value in a DataTable row and create separate rows from it.

Here is an example of the table I'm starting with:

ID Date        Places
1  09/24/2019  Paris,Tokyo,Rome
2  09/23/2019  London,Florence,Barcelona
3  09/22/2019  Vienna,Rome,London

My output DataTable should look like this:

ID Date        Places
1  09/24/2019  Paris
1  09/24/2019  Tokyo
1  09/24/2019  Rome 
2  09/23/2019  London
2  09/23/2019  Florence
2  09/23/2019  Barcelona
3  09/22/2019  Vienna
3  09/22/2019  Rome
3  09/22/2019  London

Here's my code so far:

for (int i = 0; i < dataTable.Rows.Count; i++)
{
    string[] places = dataTable.Rows[i][2].ToString().Split(',');

    if (places.Length > 1)
    {
        foreach (string s in places)
        {
            //create a new datarow 
            //get the values for row[i] (ID and Date)
            //assign the place 
        }
    }
}

I need help within the foreach.

Upvotes: 2

Views: 1110

Answers (1)

Brian Rogers
Brian Rogers

Reputation: 129777

You can split your places into multiple rows like this:

// Use ToList() here so that we can modify the table while iterating over the original rows
foreach (DataRow row in dataTable.Rows.Cast<DataRow>().ToList())
{
    int id = row.Field<int>("ID");
    string date = row.Field<string>("Date");
    string places = row.Field<string>("Places");

    foreach (string place in places.Split(','))
    {
        dataTable.Rows.Add(id, date, place);
    }

    row.Delete();  // delete the original row
}

Note: the Field<T>() extension method is defined in System.Data.DataSetExtensions, so you'll need a reference to that assembly in your project if you want to use that method.

Working demo: https://dotnetfiddle.net/zYSWlv

Upvotes: 1

Related Questions