Convert a XML to CSV using C#

Question

How to convert a XML file to a CSV file in C#, showing only these Tags: <original-impot-no>, <price>, <Small-price>, <Big-price>?

sample XML code: it represents one line among several lines, and every line may contains several <product-lineitem>Tags

<?xml version="1.0" encoding="UTF-8"?>
<impots xmlns="http://www.google.com/xml/impot//20016-02-31">
  <impot impot-no="W0110891258">
    <impot-date>2017-12-10T22:33:35.000Z</impot-date>
    <prop-by>Yallo</prop-by>
    <original-impot-no>891258</original-impot-no>
    <currency>EUR</currency>
    <server-locale>Esp</server-locale>
    <lax>gross</lax>
    <current-impot-no>123358</current-impot-no>
    <product-lineitems>
       <product-lineitem>
            <price>450</price>
            <red>6.50</red>
            <Small-price>39</Small-price>
            <Big-price>3229</Big-price>
            <lineitem-text>Grand create</lineitem-text>
            <basis>234.00</basis>
        </product-lineitem>
    </product-lineitems>
       <product-lineitem>
            <price>432</price>
            <red>12</red>
            <Small-price>44</Small-price>
            <Big-price>34</Big-price>
            <lineitem-text>Small create</lineitem-text>
            <basis>44.00</basis>
       </product-lineitem>
    </product-lineitems>
  </impot>
</impots>

I should get someting like this in y CSV file:

891258;450;39;229

891258;432;44;34

the C# code:

the problem that Im facing with this code is that I can not retrieve the descandent of the TAG <impot>

   XmlTextReader xtr = new XmlTextReader(@"C:\Temp_Convert\Impot.xml");
            StringBuilder dataToBeWritten = new StringBuilder();
            while (xtr.Read())
            {

                    if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "original-impot-no")
                    {
                        string s1 = xtr.ReadElementString();
                        dataToBeWritten.Append(s1);
                        dataToBeWritten.Append(";");
                    }
                    else if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "price")
                    {
                        string s2 = xtr.ReadElementString();
                        dataToBeWritten.Append(s2);
                        dataToBeWritten.Append(";");

                    }
                    else if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "Small-price")
                    {
                        string s2 = xtr.ReadElementString();
                        dataToBeWritten.Append(s2);
                        dataToBeWritten.Append(";");
                        dataToBeWritten.Append(0);
                        dataToBeWritten.Append(Environment.NewLine);
                    }
                        else if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "Big-price")
                    {
                        string s2 = xtr.ReadElementString();
                        dataToBeWritten.Append(s2);
                        dataToBeWritten.Append(";");
                        dataToBeWritten.Append(0);
                        dataToBeWritten.Append(Environment.NewLine);
                    }
                }

                File.WriteAllText(@"C:\Temp_Convert\Impot.csv", dataToBeWritten.ToString());

            }

Can somebody please propose a solution, thank you so much in advance.

Nkosi · Answer 1 · 2017-12-21T02:32:56.703

Review the following code. Note the use of SelectMany to get the impot items to construct the desired object models.

XNamespace ns = "http://www.google.com/xml/impot//20016-02-31";
var results = xDocument.Descendants(ns + "impot")
    .SelectMany(impot => impot.Descendants(impot.Name.Namespace + "product-lineitem")
        .Select(item => new {
            ImpotNo = (string)impot.Element(impot.Name.Namespace + "original-impot-no"),
            Price = (string)item.Element(item.Name.Namespace + "price"),
            SmallPrice = (string)item.Element(item.Name.Namespace + "Small-price"),
            BigPrice = (string)item.Element(item.Name.Namespace + "Big-price"),
        })
    ).ToList();


for (int i = 0; i < results.Count; i++) {
    dataToBeWritten.Append(results[i].ImpotNo);
    dataToBeWritten.Append(";");
    dataToBeWritten.Append(results[i].Price);
    dataToBeWritten.Append(";");
    dataToBeWritten.Append(results[i].SmallPrice);
    dataToBeWritten.Append(";");
    dataToBeWritten.Append(results[i].BigPrice);
    dataToBeWritten.Append(";");
    dataToBeWritten.Append(0);
    dataToBeWritten.Append(Environment.NewLine);
}

Also note the syntax used for the properties.

thank you for your answer, the problem is that I can not get the root impot — tollamie, Dec 21 '17 at 01:35

score 0 · Answer 2 · answered Dec 21 '17 at 01:51

Well, first I tried to reformat your XML to be a bit more readable but the tag structure still seems wrong...

<impots
    xmlns="http://www.google.com/xml/impot//20016-02-31">
    <impot impot-no="W0110891258">
        <impot-date>2017-12-10T22:33:35.000Z</impot-date>
        <prop-by>Yallo</prop-by>
        <original-impot-no>891258</original-impot-no>
        <currency>EUR</currency>
        <server-locale>Esp</server-locale>
        <lax>gross</lax>
        <current-impot-no>123358</current-impot-no>
        <product-lineitems>
            <product-lineitem>
                <price>450</price>
                <red>6.50</red>
                <Small-price>39.00</Small-price>
                <Big-price>3229.00</Big-price>
                <lineitem-text>Grand create</lineitem-text>
                <basis>234.00</basis>
-
            </product-lineitems>
-
        </product-lineitem>
        <product-lineitems>
            <product-lineitem>
                <price>432</price>
                <red>12</red>
                <Small-price>44.00</Small-price>
                <Big-price>34.00</Big-price>
                <lineitem-text>Small create</lineitem-text>
                <basis>44.00</basis>
            </product-lineitems>
        </product-lineitem>

Nonetheless, I'm guessing this line is incorrect since "impot-no" is an attribute...

impot-no = (string)x.Element("impot impot-no")

Perhaps you meant that line to be...

impot-no = (string)x.Attribute("impot-no").Value

Going by memory -- hopefully that is the correct way to retrieve an attribute.

Oshi · Accepted Answer · 2017-12-21T02:23:22.017

You have an invalid XML. I guess here is the correct format.

<?xml version="1.0" encoding="UTF-8"?>
<impots xmlns="http://www.google.com/xml/impot//20016-02-31">
   <impot impot-no="W0110891258">
      <impot-date>2017-12-10T22:33:35.000Z</impot-date>
      <prop-by>Yallo</prop-by>
      <original-impot-no>891258</original-impot-no>
      <currency>EUR</currency>
      <server-locale>Esp</server-locale>
      <lax>gross</lax>
      <current-impot-no>123358</current-impot-no>
      <product-lineitems>
         <product-lineitem>
            <price>450</price>
            <red>6.50</red>
            <Small-price>39.00</Small-price>
            <Big-price>3229.00</Big-price>
            <lineitem-text>Grand create</lineitem-text>
            <basis>234.00</basis>
         </product-lineitem>
      </product-lineitems>
      <product-lineitems>
         <product-lineitem>
            <price>432</price>
            <red>12</red>
            <Small-price>44.00</Small-price>
            <Big-price>34.00</Big-price>
            <lineitem-text>Small create</lineitem-text>
            <basis>44.00</basis>
         </product-lineitem>
      </product-lineitems>
   </impot>
</impots>

You couldn't retrieve the descendant because you are not including the namespace http://www.google.com/xml/impot//20016-02-31.

Here is how the code should be.

XNamespace ns = "http://www.google.com/xml/impot//20016-02-31";
var results = xDocument.Descendants(ns + "impot");

Then, you need to modify your query to retrieve elements that you need.

Here is the sample. I assume that product-lineitems only has one child product-lineitem.

var results = xDocument.Descendants(ns + "impot").Select(x => new {
    ImpotNo = x.Attribute("impot-no")?.Value,
    ProductLineItems = x.Descendants(ns + "product-lineitems").Select(y => new
    {
        Item = y.Descendants(ns + "product-lineitem").Select(z => new
        {
            Price = z.Element(ns + "price")?.Value,
            SmallPrice = z.Element(ns + "Small-price")?.Value,
            BigPrice = z.Element(ns + "Big-price")?.Value,
        }).FirstOrDefault()
    })
});

foreach (var result in results)
{
    foreach (var productLine in result.ProductLineItems)
    {
        dataToBeWritten.Append(result.ImpotNo);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(productLine.Item.Price);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(productLine.Item.SmallPrice);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(productLine.Item.BigPrice);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(0);
        dataToBeWritten.Append(Environment.NewLine);
    }
}

I have added the namespace as you mentioned however I get a blank csv file — tollamie, Dec 21 '17 at 02:07
You need to modify your query to fit your need. I have edited my post. — Oshi, Dec 21 '17 at 02:20
Thank you! it worked, but I have several child `product-lineitem` for one impot-no , and each `impot-no` can be duplicated, it depend on `product-lineitem` — tollamie, Dec 21 '17 at 02:41
Please accept the answer if it solved your main problem in the question. You can remove the `FirstOrDefault()` if there can be more than one `product-lineitem`. Then, you need to do one more loop on the `Items` to construct the result. And you need to do duplicate checking by yourself. Good luck. — Oshi, Dec 21 '17 at 02:47
You need to show some efforts. Your problem "can not retrieve the descandent of the TAG impot" has been solved. Please accept the answer. — Oshi, Dec 21 '17 at 02:56
Check your data and do duplicate checking if needed. Thanks. — Oshi, Dec 21 '17 at 02:59
Hi @tollamie if this or any answer has solved your question please consider [accepting it](https://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answer-er and yourself. There is no obligation to do this. — Oshi, Dec 21 '17 at 03:04

Convert a XML to CSV using C#

3 Answers3