How to skip semicolon inside one attributes of XML while saving into CSV

Question

I am using C# in my project. I have a long XML file. I want to import all of them at once in a CSV file. I am trying this by writing the following code, But there is mismatch inside column. Next column value comes previously. Suddenly I noticed that for some attributes (For example Note), the text is written with semicolon instead of Comman and as a result this text set in three columns instead of one.

Example "Review VAT query; draft simple VAT agreement; review law and reply to queries".How can I ingore Semicolon of that properties.

Here is my code.

var output = new StringBuilder();
output.AppendLine("EmployeeId;EmployeeFirstName;EmployeeLastName;AllocationId;TaskId;TaskName;ProjectName;CustomerName;InvoiceAmount;WorkHours");
if (workUnit != null)
{
    foreach (XmlNode customer in workUnit)
    {
        var unit = new WorkUnit();
        var childNodes = customer.SelectNodes("./*");
        if (childNodes != null)
        {
            for (int i = 0; i < childNodes.Count; ++i)
            {
                XmlNode childNode = childNodes[i];
                output.Append(childNode.InnerText);
                if (i < childNodes.Count - 1)
                    output.Append(";");
            }
        }
        output.Append(Environment.NewLine);
    }
    Console.WriteLine(output.ToString());
    File.AppendAllText("c:\\..WorkUnits.csv", output.ToString());
}

while writing the output to csv , you can use ReplaceAll() or Replace() property to replace Semicolon(;) with Blank space. — SH7, Dec 13 '17 at 10:56
@user3501749 Could you plaese explain whete can I write this line — tamrezh21, Dec 13 '17 at 11:00
Usually you'd try to keep the data intact. You could replace `"` with `""` in each value and then surround each value with double-quotes to get a standard format of CSV. — Andrew Morton, Dec 13 '17 at 11:02
@AndrewMorton I am still new in programming, where can I set that logic — tamrezh21, Dec 13 '17 at 11:03
@tamrezh21 Last Line - File.AppendAllText("c:\\..WorkUnits.csv", output.ToString().Replace(";","")); — SH7, Dec 13 '17 at 11:27

score 1 · Accepted Answer · answered Dec 13 '17 at 11:07

You could try to use the StringToCSVCell method defined by @Ed Bayiates here for to escape any semi-colon in the cell values:

escaping tricky string to CSV format

XmlNode childNode = childNodes[i];
output.Append(StringToCSVCell(childNode.InnerText));
if (i < childNodes.Count - 1)
    output.Append(";");

How to skip semicolon inside one attributes of XML while saving into CSV

1 Answers1