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 => codereview.stackexchange.com/questions/159904/… — Lucifer
– Lucifer, Commented Sep 1, 2020 at 9:42
I'd have a look into XSLT - The example transforms XML to XML, but you surely can also transform XML to CSV (have done that before). — Fildor
– Fildor, Commented Sep 1, 2020 at 9:44

jdweng · Accepted Answer · 2020-09-01 10:10:07Z

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

Collectives™ on Stack Overflow

Convert XML to csv in c# with multiple collection

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related