Convert XML to csv in c# with multiple collection

Question

I am looking for a way to convert an xml stream to csv, but I only find solution for 1 collection, i.e. my xml looks like :

<?xml version="1.0" encoding="UTF-8"?>
<CompactData>
<Header>
<ID>id_ofç=_file</ID>
<Test>false</Test>
</Header>
<data:DataSet>
<data:Series FREQ="M" item="item1" unit="unit1">
<data:Obs TIME_PERIOD="2015-01" OBS_VALUE="5.47" />
<data:Obs TIME_PERIOD="2015-02" OBS_VALUE="5.01" />
<data:Obs TIME_PERIOD="2015-03" OBS_VALUE="5.39" />
</data:Series>
<data:Series FREQ="M" item="item2" unit="unit2">
<data:Obs TIME_PERIOD="2015-01" OBS_VALUE="5.47" />
<data:Obs TIME_PERIOD="2015-02" OBS_VALUE="5.01" />
<data:Obs TIME_PERIOD="2015-03" OBS_VALUE="5.39" />
</data:Series>
</data:DataSet>
</CompactData>

Here I want a csv with the format :

FREQ,item,unit,TIME_PERIOD,OBS_VALUE

what is the best way to do that? Thanks!

try this => https://codereview.stackexchange.com/questions/159904/convert-xml-to-csv — Lucifer, Sep 01 '20 at 09:42
I'd have a look into [XSLT](https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/using-xslt-to-transform-an-xml-tree) - The example transforms XML to XML, but you surely can also transform XML to CSV (have done that before). — Fildor, Sep 01 '20 at 09:44
^^ See [this](https://stackoverflow.com/questions/365312/xml-to-csv-using-xslt) and [this](https://stackoverflow.com/questions/15226194/xml-to-csv-using-xslt). — Fildor, Sep 01 '20 at 09:50

score 0 · Accepted Answer · answered Sep 01 '20 at 10:10

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Xml.Linq;
using System.IO;

namespace ConsoleApplication166
{
    class Program
    {
        const string XML_FILENAME = @"c:\temp\test.xml";
        const string CSV_FILENAME = @"c:\temp\test.csv";
        static void Main(string[] args)
        {
            StreamWriter writer = new StreamWriter(CSV_FILENAME);
            writer.WriteLine(string.Join(",", new string[] {"FREQ","item","unit","TIME_PERIOD","OBS_VALUE"}));


            XDocument doc = XDocument.Load(XML_FILENAME);

            XElement dataSet = doc.Descendants().Where(x => x.Name.LocalName == "DataSet").FirstOrDefault();
            XNamespace nsData = dataSet.GetNamespaceOfPrefix("data");

            foreach (XElement series in dataSet.Elements(nsData + "Series"))
            {
                string freq = (string)series.Attribute("FREQ");
                string item = (string)series.Attribute("item");
                string unit = (string)series.Attribute("unit");
                foreach (XElement obs in series.Elements(nsData + "Obs"))
                {
                    DateTime time = DateTime.ParseExact((string)obs.Attribute("TIME_PERIOD"), "yyyy-MM", System.Globalization.CultureInfo.InvariantCulture);
                    double value = (double)obs.Attribute("OBS_VALUE");
                    writer.WriteLine(string.Join(",", new string[] {freq, item, unit,time.ToString(), value.ToString()}));
                }
            }

            writer.Flush();
            writer.Close();
        }
    }
 
}

Convert XML to csv in c# with multiple collection

1 Answers1