CODEWITHSUNDEEP
c#datatable
lross15
lross15

Reputation: 177

Separate multiple field values with a comma in a datatable

In my application I have got a function that will clone the original datatable and use it to create a new one with a new column added on like so:

    private static DataTable CloneTable(DataTable originalTable, DataTable newTable, DataTable targetTable,
        string addedColumn, string columnToExtract, bool multipleConditions = false, string secondColumnName = null, string secondColumnConditon= null)
    {

        newTable = originalTable.Clone();
        newTable.Columns.Add(addedColumn);

        foreach (DataRow row in originalTable.Rows)
        {

            DataRow[] rowsTarget;

            if (multipleConditions == false)
            {
                rowsTarget = targetTable.Select(string.Format("ItemId='{0}'", Convert.ToString(row["ItemId"])));
            } else
            {
               rowsTarget = targetTable.Select(string.Format("ItemId='{0}' AND {1} ='{2}'", Convert.ToString(row["ItemId"]), secondColumnName, secondColumnConditon));
            }


            if (rowsTarget != null && rowsTarget.Length > 0)
            {
                string data = rowsTarget[0][columnToExtract].ToString();
                var lst = row.ItemArray.ToList();
                lst.Add(data);

                newTable.Rows.Add(lst.ToArray());
            }

            else
            {
                string data = "";
                var lst = row.ItemArray.ToList();
                lst.Add(data);
                newTable.Rows.Add(lst.ToArray());
                ErrorLogging.ErrorList.Add(new ErrorLogging()
                {
                    ErrorType = "Warning",
                    Relation = "Database- to do with column: " + addedColumn ,
                    Message = "Data not found for document with the itemId: " + row["ItemId"]
                });
            }

        }          

        return newTable;

    }

This works fine, however, the data that is used for the datatables come from sql queries and sometimes in a field in a row there will be multiple values in a single field. For example for the column Language there may be a field that has the values: English French German. What I am trying to do is change it so that these values are separated by a comma, what I had tried was doing:

            if (rowsTarget != null && rowsTarget.Length > 0)
            {
                string data = rowsTarget[0][columnToExtract].ToString();
                var lst = row.ItemArray.ToList();
                lst.Add(data);
                string test = string.Join(",", lst);

                foreach (DataColumn column in newTable.Columns)
                {
                    if (column.ColumnName == "Language")
                    {
                        Console.WriteLine("test");
                        
                        newTable.Rows.Add(test.ToArray());
                    } 
                }
                newTable.Rows.Add(lst.ToArray());
            }

But I realise this does not work as that will affect all the other columns as well, so I was wondering how can I change it so this will affect the fields of only the specified columns so that multiple values are separated by a comma? Am I on the right track or am I going about this the wrong way?

Edit -

So basically, at the end of it all, the final data table is exported into a csv file, this is what it currently looks like:

enter image description here

And this is what I would like it to look like:

enter image description here

I'm not sure how easy it is to tell, but in the first image you will see that for the fields under the column Language, when there is more than 1 word, it is not separated by a comma which is what I would like it to do as can be seen in the second image

Second Edit:

When I try the following:

        foreach (DataRow newRow in itemsTable.Rows)
        {

            object cellData = newRow["Language"];

            string test = string.Join(",", cellData.ToString().ToArray());
            Console.WriteLine(test);


        }

It does add a comma but it is after every character rather than every word, so I feel like I'm getting closer but there is something that I am still missing

Upvotes: 0

Views: 536

Answers (1)

JohnG
JohnG

Reputation: 9479

You may to be complicating things… it appears you want to mix all this “combining” of the tables and adding the commas ALL into one method…. And I would think it would be easier to separate those functions into a simple “clone” method and a separate simple “addCommas” method.

The add commas method should be fairly trivial and may look something like below, note you could reduce this down to four lines of code, however for clarity I used some extra variables…

string originalText;
string finalText;
string[] splitArray;
foreach (DataRow row in YourDataTable.Rows) {
  originalText = row["Language"].ToString();
  splitArray = originalText.Trim().Split(' ');
  finalText = string.Join(", ", splitArray);
  row["Language"] = finalText;
}

Upvotes: 1

Related Questions