CODEWITHSUNDEEP
c#xmlcsv
tamrezh21
tamrezh21

Reputation: 125

How to create a CSV file from a XML file

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");
                    }
                }
            }
        }
    }
}

Upvotes: 0

Views: 3797

Answers (5)

mm8
mm8

Reputation: 169200

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());
}

Upvotes: 1

Cinchoo
Cinchoo

Reputation: 6322

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.

Upvotes: 0

Hans Kesting
Hans Kesting

Reputation: 39284

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.

Upvotes: 1

Bassem
Bassem

Reputation: 820

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:

  1. 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.
  2. From Visual Studio Menus select Edit -> Paste Special -> Paste XML as Classes.

  3. 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());

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

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());

Upvotes: 1

Related Questions