CODEWITHSUNDEEP
c#xmlcsvssis
Kathir E
Kathir E

Reputation: 13

How to convert XML file into a CSV file by using C#

I am trying to convert xml file into csv file. How to read and parse xml file and convert into csv? Is there any package to convert xml into csv.

Give me best way to write the code.

How to convert a XML file to a CSV file in C#, showing only these Tags:

   <a:MarketingAllCardholder xmlns:b="http://schemas.datacontract.org/2004/07/ExternalClient.Data.Classes">
       <b:MarketingAllCardholderData>
          <b:CentreName>Corporate Office</b:CentreName>
          <b:Country>Austria</b:Country>
          <b:CustomerId>379</b:CustomerId>
          <b:DOB>25/02/1991</b:DOB>
          <b:Email>[email protected]</b:Email>
          <b:ExpiryDate>03/01/2020 08:01</b:ExpiryDate>

       </b:MarketingAllCardholderData>
       <b:MarketingAllCardholderData>
          <b:CentreName>Corporate Office</b:CentreName>
          <b:Country>Egypt</b:Country>
          <b:CustomerId>988915</b:CustomerId>
          <b:DOB>01/03/1986</b:DOB>
          <b:Email>[email protected]</b:Email>
          <b:ExpiryDate>07/01/2020 11:38</b:ExpiryDate>

       </b:MarketingAllCardholderData>

Output CSV file format should be like below:

Title,FirstName,LastName,DOB,Email,Country,Token,OriginalBalance,ExpiryDate Mr,wasim,warsi,19/01/1990,[email protected],India,384176,500,14/11/2019 05:31,14/11/2018 05:33,16.34

Upvotes: 1

Views: 2117

Answers (4)

leandro.andrioli
leandro.andrioli

Reputation: 1047

Basically you can use XMLSerializator or XSLT.

Using XMLSerializator:

public class MarketingAllCardholder{
    public MarketingAllCardholderData[] marketingAllCardholderDataList { get; set; }
}

using System.IO;
using System.Xml.Serialization;

using (FileStream fs = new FileStream(@"name.xml", FileMode.Open))
{
    XmlSerializer serializer = new XmlSerializer(typeof(MarketingAllCardholde[]));
    var data = (MarketingAllCardholder[])serializer.Deserialize(fs);
    List<string> list = new List<string>();
    foreach (var item in data)
    {
        //Add All the necessary columns here...
        //After the columns add the delimiter character -> string.Join(","....
    }
    File.WriteAllLines("D:\\csvFile.csv", list);
}

Another way is using XSLT to convert it.

Here you have the following steps

  • Create an Xml stylesheet to convert xml to csv Use
  • XslCompiledTransform() to convert get the csv string
  • save the csv string to a file string to a file

Use the following method:

public static string ToCSV(string xmlTextDate, string xsltFile)
{
  string result = string.Empty;
  var xpathDoc = new XPathDocument(xmlTextDate);
  var xsltTransform = new System.Xml.Xsl.XslCompiledTransform();
  xsltTransform.Load(xsltFile);

  using (MemoryStream ms = new MemoryStream())
  {
      var writer = new XmlTextWriter(ms, Encoding.UTF8);
      using (var rd = new StreamReader(ms))
      {
          var argList = new System.Xml.Xsl.XsltArgumentList();
          xsltTransform.Transform(xpathDoc, argList, writer);
          ms.Position = 0;
          result = rd.ReadToEnd();
      }
  }
  return result;
}

And call it like this

var csvString = ToCSV("name.xml","name.xsl");

Upvotes: 4

Cinchoo
Cinchoo

Reputation: 6322

With Cinchoo ETL - an open source library, you can do do the conversion easily with few lines of code

using (var r = new ChoXmlReader("*** XML FILE PATH ***")
    .WithXPath("b:MarketingAllCardholderData")
    .WithXmlNamespace("a", "schemas.datacontract.org/2004/07/ExternalClient.Responses")
    .WithXmlNamespace("b", "schemas.datacontract.org/2004/07/ExternalClient.Data.Classes")
    )
{
    using (var w = new ChoCSVWriter(sb)
        .WithFirstLineHeader()
        .Configure(c => c.UseNestedKeyFormat = false)
        )
        w.Write(r);
}
Console.WriteLine(sb.ToString());

Output:

CentreName,Country,CustomerId,DOB,Email,ExpiryDate
Corporate Office,Austria,379,25/02/1991,[email protected],3/1/2020 8:01:00 AM
Corporate Office,Egypt,988915,01/03/1986,[email protected],7/1/2020 11:38:00 AM

For more info, please visit GitHub repository

Hope it helps.

Disclaimer: I'm the author of this library.

Upvotes: 0

Krishna Varma
Krishna Varma

Reputation: 4250

Another approach is using FileHelper

        using (StreamReader r = new StreamReader(xmlfilepath))
        {
            string xmlString = r.ReadToEnd();

            XmlSerializer ser = new XmlSerializer(typeof(MarketingAllCardholder));

            using (TextReader reader = new StringReader(xmlString))
            {
                var marketingAllCardholder = (MarketingAllCardholder)ser.Deserialize(reader);

                var engine = new FileHelperEngine<MarketingAllCardholderData>();
                engine.HeaderText = engine.GetFileHeader();
                string filePath = Path.Combine(@"C:\RnD", "testfile" + ".csv");
                engine.WriteFile(filePath , (IEnumerable<MarketingAllCardholderData>)marketingAllCardholder.MarketingAllCardholderData);
            }
        }

Model from XML (you can use xmltocsharp to convert the xml to c# model) and add [DelimitedRecord(",")]

[DelimitedRecord(",")]
[XmlRoot(ElementName = "MarketingAllCardholderData")]
public class MarketingAllCardholderData
{
    [XmlElement(ElementName = "CentreName")]
    public string CentreName { get; set; }
    [XmlElement(ElementName = "Country")]
    public string Country { get; set; }
    [XmlElement(ElementName = "CustomerId")]
    public string CustomerId { get; set; }
    [XmlElement(ElementName = "DOB")]
    public string DOB { get; set; }
    [XmlElement(ElementName = "Email")]
    public string Email { get; set; }
    [XmlElement(ElementName = "ExpiryDate")]
    public string ExpiryDate { get; set; }
}

[DelimitedRecord(",")]
[XmlRoot(ElementName = "MarketingAllCardholder")]
public class MarketingAllCardholder
{
    [XmlElement(ElementName = "MarketingAllCardholderData")]
    public List<MarketingAllCardholderData> MarketingAllCardholderData { get; set; }
}

Upvotes: 0

Innat3
Innat3

Reputation: 3576

First off, you'll want to deserialize the xml in order to make it's data easy to read and access. For that, I suggest you simply use a website such as xmltocsharp, which will generate your classes and then all you have to do is paste them into your code.

Once you've done that, the conversion code is pretty straight forward:

XmlSerializer serializer = new XmlSerializer(typeof(Envelope));

using (var reader = new StreamReader(xmlFilePath))
{
     var envelope = ((Envelope)serializer.Deserialize(reader));
     var csv_content = new List<string>();
     var headers = new[] { "CentreName", "Country", "CustomerId", "DOB", "Email", "ExpiryDate" };

     csv_content.Add(string.Join(",", headers));

     foreach (var data in envelope.Body
                              .GetMarketingAllCardholderDataResponse
                                  .GetMarketingAllCardholderDataResult
                                      .MarketingAllCardholder
                                          .MarketingAllCardholderData)
     {
        var row_data = new[] 
        { 
            data.CentreName, 
            data.Country, 
            data.CustomerId, 
            data.DOB, 
            data.Email, 
            data.ExpiryDate 
        };

        csv_content.Add(string.Join(",", row_data));
     }

     File.WriteAllLines("csvFilePath.csv", csv_content);
}

Upvotes: 0

Related Questions