How to create a CSV file from a XML file

Question

I am very new at C#. In my project I need to create a csv file which will get data from a xml data. Now, I can get data from XML, and print in looger for some particulaer attributes from xml. But I am not sure how can I store my Data into CSV file for that particular attribues.

Here is my XML file that I need to create a CSV file.

    <?xml version="1.0" encoding="utf-8"?>
<tlp:WorkUnits xmlns:tlp="http://www.timelog.com/XML/Schema/tlp/v4_4"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xsi:schemaLocation="http://www.timelog.com/XML/Schema/tlp/v4_4 http://www.timelog.com/api/xsd/WorkUnitsRaw.xsd">
  <tlp:WorkUnit ID="130">
    <tlp:EmployeeID>3</tlp:EmployeeID>
    <tlp:AllocationID>114</tlp:AllocationID>
    <tlp:TaskID>239</tlp:TaskID>
    <tlp:ProjectID>26</tlp:ProjectID>
    <tlp:ProjectName>LIK Template</tlp:ProjectName>
    <tlp:CustomerId>343</tlp:CustomerId>
    <tlp:CustomerName>Lekt Corp Inc.</tlp:CustomerName>
    <tlp:IsBillable>1</tlp:IsBillable>
    <tlp:ApprovedStatus>0</tlp:ApprovedStatus>
    <tlp:LastModifiedBy>AL</tlp:LastModifiedBy>
  </tlp:WorkUnit>

And my Code where I am getting this value in logger.But I am not sure how can I create a csv file that stores that value in order.

Edited

   namespace TimeLog.ApiConsoleApp
{
    /// <summary>
    /// Template class for consuming the reporting API
    /// </summary>
    public class ConsumeReportingApi
    {
        private static readonly ILog Logger = LogManager.GetLogger(typeof(ConsumeReportingApi));

        public static void Consume()
        {
            if (ServiceHandler.Instance.TryAuthenticate())
            {
                if (Logger.IsInfoEnabled)
                {
                    Logger.Info("Successfully authenticated on reporting API");
                }

                var customersRaw = ServiceHandler.Instance.Client.GetWorkUnitsRaw(ServiceHandler.Instance.SiteCode,
                     ServiceHandler.Instance.ApiId,
                     ServiceHandler.Instance.ApiPassword,
                     WorkUnit.All,
                     Employee.All,
                     Allocation.All,
                     Task.All,
                     Project.All,
                     Department.All,
                     DateTime.Now.AddDays(-5).ToString(),
                     DateTime.Now.ToString()
                  );



                if (customersRaw.OwnerDocument != null)
                {
                    var namespaceManager = new XmlNamespaceManager(customersRaw.OwnerDocument.NameTable);
                    namespaceManager.AddNamespace("tlp", "http://www.timelog.com/XML/Schema/tlp/v4_4");
                    var workUnit = customersRaw.SelectNodes("tlp:WorkUnit", namespaceManager);

                    var output = new StringBuilder();
                    output.AppendLine("AllocationID,ApprovedStatus,CustomerId,CustomerName,EmployeeID");
                    if (workUnit != null)
                    {

                       foreach (XmlNode customer in workUnit)

                        {
                            var unit = new WorkUnit();
                            var childNodes = customer.SelectNodes("./*");

                            if (childNodes != null)
                            {


                                foreach (XmlNode childNode in childNodes)
                                {
                                    if (childNode.Name == "tlp:EmployeeID")
                                    {
                                        unit.EmployeeID = Int32.Parse(childNode.InnerText);
                                    }
                                    if (childNode.Name == "tlp:EmployeeFirstName")
                                    {
                                        unit.EmployeeFirstName = childNode.InnerText;
                                    }
                                    if (childNode.Name == "tlp:EmployeeLastName")
                                    {
                                        unit.EmployeeLastName = childNode.InnerText;
                                    }

                                    if (childNode.Name == "tlp:AllocationID")
                                    {
                                        unit.AllocationID = Int32.Parse(childNode.InnerText);
                                    }

                                    if (childNode.Name == "tlp:TaskName")
                                    {
                                        unit.TaskName = childNode.InnerText;
                                    }


                                }                             
                           }
                            output.AppendLine($"{unit.EmployeeID},{unit.EmployeeFirstName},{unit.EmployeeLastName},{unit.AllocationID},{unit.TaskName}");
                            //Console.WriteLine("---");

                        }

                        Console.WriteLine(output.ToString());
                        File.WriteAllText("c:\\...\\WorkUnits.csv", output.ToString());

                    }
                }

                else
                {
                    if (Logger.IsWarnEnabled)
                    {
                        Logger.Warn("Failed to authenticate to reporting API");
                    }
                }
            }
        }
    }
}

Do you have a sample of what you expect the CSV to look like? But I will say writing out a CSV is simple enough, you just need to watch out for adding " around values with commas, and if you have values that contain ", they need to doubled-up. — Dylan Nicholson
– Dylan Nicholson, Commented Dec 12, 2017 at 9:58
Take a stringbuilder variable. Right at the place of Logging write the value appended with a comma into stringbuilder. Finally, get ToString() from the stringbuilder — Prateek Shrivastava
– Prateek Shrivastava, Commented Dec 12, 2017 at 9:59
@DylanNicholson I have to add as that values , for exmaple employee id, name etc as header value as metioned in the code — tamrezh21
– tamrezh21, Commented Dec 12, 2017 at 10:00

Hans Keﬆing · Accepted Answer · 2017-12-12 11:32:30Z

1

You want to write the columns in the correct order to the CSV (of course), so you need to process them in the correct order. Two options:

intermediate class

Create a new class (let's call it WorkUnit) with properties for each of the columns that you want to write to the CSV. Create a new instance for every <tlp:WorkUnit> node in your XML and fill the properties when you encounter the correct subnodes. When you have processed the entire WorkUnit node, write out the properties in the correct order.

  var output = new StringBuilder();
  foreach (XmlNode customer in workUnit)
  {
       // fresh instance of the class that holds all columns (so all properties are cleared)
       var unit = new WorkUnit();
       var childNodes = customer.SelectNodes("./*");

       if (childNodes != null)
       {
            foreach (XmlNode childNode in childNodes)
            {
                if(childNode.Name== "tlp:EmployeeID")
                {
                     // employeeID node found, now write to the corresponding property:
                     unit.EmployeeId = childNode.InnerText;
                }
                // etc for the other XML nodes you are interested in
            }
            // all nodes have been processed for this one WorkUnit node
            // so write a line to the CSV
            output.AppendLine($"{unit.EmployeeId},{unit.AllocationId}, etc");
       }

read in correct order

Instead of using foreach to loop through all subnodes in whatever order they appear, search for specific subnodes in the order you want. Then you can write out the CSV in the same order. Note that even when you don't find some subnode, you still need to write out the separator.

  var output = new StringBuilder();
  foreach (XmlNode customer in workUnit)
  {
      // search for value for first column (EmployeeID)
      var node = workUnit.SelectSingleNode("tlp:EmployeeID");
      if (node != null)
      {
         output.Append(node.InnerText).Append(',');
      }
      else
      {
         output.Append(','); // no content, but we still need a separator
      }
      // etc for the other columns

And of course watch out for string values that contain the separator.

edited Dec 12, 2017 at 11:32

answered Dec 12, 2017 at 10:06

Hans Keﬆing

39.5k10 gold badges84 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

tamrezh21 Over a year ago

Thank you ver ymuch for your answer. But could you please explain with example

tamrezh21 Over a year ago

shall I write this line inside workUnit Class. Your First Paragraph Create a new class (WorkUnit) with properties for the columns. Create a new instance for every workunit node in your XML and fill the properties when you encouter the correct subnodes. When you have processed the entire WorkUnit node, write out the properties in the correct order is not Clear to me. Sorry I am very new in C# as well as In programing

Hans Keﬆing Over a year ago

@tamrezh21 I have extended my answer a bit

tamrezh21 Over a year ago

I have added your code. Now with this line I cannot store the CSV output.AppendLine($"{unit.EmployeeId},{unit.AllocationId}, etc"); How can I store CSV file

Hans Keﬆing Over a year ago

See File.WriteAllText, as used in other answers

|

Vijunav Vastivch · Accepted Answer · 2017-12-12 10:05:47Z

1

Assuming that you put your XML data into List

 StringBuilder str = new StringBuilder();

  foreach (var fin list.ToList())
  {
        str.Append(fin.listfield.ToString() + ",");

  }

to create a new line:

str.Replace(",", Environment.NewLine, str.Length - 1, 1);

to save:

string filename=(DirectoryPat/filename.csv");
File.WriteAllText(Filename, str.ToString());

answered Dec 12, 2017 at 10:05

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

2 Comments

tamrezh21 Over a year ago

your code is not compatable with my code. I am trying to write this line foreach (var f in childNode.InnerText) b8t is not getting with that line

Vijunav Vastivch Over a year ago

childNode.InnerText is not a list.. as iv'e said put like yourlist.listfield=childNode.InnerText before you proceed to foreach. Add the content of a your xml first into a list then proceed foreach

mm8 · Accepted Answer · 2017-12-12 16:35:34Z

1

Try this:

var output = new StringBuilder();
output.AppendLine("AllocationID,ApprovedStatus,CustomerId,CustomerName,EmployeeID");
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];
                if (childNode.Name == "tlp:EmployeeID")
                {
                    unit.EmployeeID = Int32.Parse(childNode.InnerText);
                }
                if (childNode.Name == "tlp:EmployeeFirstName")
                {
                    unit.EmployeeFirstName = childNode.InnerText;
                }
                if (childNode.Name == "tlp:EmployeeLastName")
                {
                    unit.EmployeeLastName = childNode.InnerText;
                }

                if (childNode.Name == "tlp:AllocationID")
                {
                    unit.AllocationID = Int32.Parse(childNode.InnerText);
                }

                if (childNode.Name == "tlp:TaskName")
                {
                    unit.TaskName = childNode.InnerText;
                }

                output.Append(childNode.InnerText);
                if (i<childNodes.Count - 1)
                    output.Append(",");
            }
            output.Append(Environment.NewLine);
        }
    }
    Console.WriteLine(output.ToString());
    File.WriteAllText("c:\\Users\\mnowshin\\projects\\WorkUnits.csv", output.ToString());
}

answered Dec 12, 2017 at 16:35

mm8

170k10 gold badges70 silver badges102 bronze badges

3 Comments

tamrezh21 Over a year ago

Could you please explain what is the functionality of this line?

tamrezh21 Over a year ago

PLease just igone the previous comment. I have corrected in my code. But could you please tell me if I have semicolon inside one attributes of the XML, how can I ignore that while saving to CSV file. I mean if this semicolon exist, the text is aliged in three colums

mm8 Over a year ago

Please ask a new question if you have another issue.

Bassem · Accepted Answer · 2017-12-12 11:01:50Z

0

You can use this sequence:

a. Deserialize (i.e. convert from XML to C# objects) your XML.

b. Write a simple loop to write the data to a file.

The advantages of this sequence:

You can use a list of your data/objects "readable" that you can add any other access code to it.
If you XML schema changed at any time, you can maintain the code very easily.

The solution

a. Desrialize:

Copy you XML file contents. Note You should modify your XML input before coping it.. You should double the WorkUnit node, in order to tell Visual Studio that you would have a list of this node nested inside WorkUnits node.
From Visual Studio Menus select Edit -> Paste Special -> Paste XML as Classes.

Use the deserialize code.

    var workUnitsNode = customersRaw.SelectSingleNode("tlp:WorkUnits", namespaceManager);
    XmlSerializer ser = new XmlSerializer(typeof(WorkUnits));
    WorkUnits workUnits = (WorkUnits)ser.Deserialize(workUnitsNode);

b. Write the csv file

    StringBuilder csvContent = new StringBuilder();
    // add the header line
    csvContent.AppendLine("AllocationID,ApprovedStatus,CustomerId,CustomerName,EmployeeID");
    foreach (var unit in workUnits.WorkUnit)
    {
        csvContent.AppendFormat(
            "{0}, {1}, {2}, {3}, {4}",
            new object[] 
            {
                unit.AllocationID,
                unit.ApprovedStatus,
                unit.CustomerId,
                unit.CustomerName,
                unit.EmployeeID
                // you get the idea
            });
        csvContent.AppendLine();
    }

    File.WriteAllText(@"G:\Projects\StackOverFlow\WpfApp1\WorkUnits.csv", csvContent.ToString());

edited Dec 12, 2017 at 11:01

answered Dec 12, 2017 at 10:56

Bassem

8209 silver badges17 bronze badges

7 Comments

tamrezh21 Over a year ago

What is WorkUnits here. is a class

Bassem Over a year ago

@tamrezh21, When you modify and copy the XML, then Paste Special the contents to Visual Studio. Visual Studio will create classes that matches your XML data. WorkUnits class will be created then.

tamrezh21 Over a year ago

@Basem the XML file is from API. can I save it in that way

Bassem Over a year ago

@tamrezh21, I modified the answer; Added an important note before coping the xml.

Bassem Over a year ago

Do you want to save the XML to a file too?

|

Cinchoo · Accepted Answer · 2017-12-12 13:14:09Z

0

You can use Cinchoo ETL - if you have room to use open source library

using (var csvWriter = new ChoCSVWriter("sample1.csv").WithFirstLineHeader())
{
    using (var xmlReader = new ChoXmlReader("sample1.xml"))
        csvWriter.Write(xmlReader);
}

Output:

ID,tlp_EmployeeID,tlp_AllocationID,tlp_TaskID,tlp_ProjectID,tlp_ProjectName,tlp_CustomerId,tlp_CustomerName,tlp_IsBillable,tlp_ApprovedStatus,tlp_LastModifiedBy
130,3,114,239,26,LIK Template,343,Lekt Corp Inc.,1,0,AL

Disclaimer: I'm the author of this library.

answered Dec 12, 2017 at 13:14

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to create a CSV file from a XML file

5 Answers 5

intermediate class

read in correct order

10 Comments

2 Comments

3 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

intermediate class

read in correct order

10 Comments

2 Comments

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related