Parse XML to CSV without CsvHelper in C#

Question

I'm trying to convert an XML file to CSV but the version of .NET I'm running doesn't support the CsvHelper library that seems to be popular here.

The XML document is simplified below:

<E1AFKOL SEGMENT="1">
    <MATNR>456654546</MATNR>
    <E1AFFLL SEGMENT="1">
        <APLZL>00000001</APLZL>
        <E1AFVOL SEGMENT="1">
            <VORNR>0020</VORNR>
            <E1RESBL SEGMENT="1">
                <AUSCH>0.00</AUSCH>
            </E1RESBL>
            <E1RESBL SEGMENT="1">
                <AUSCH>0.00</AUSCH>
                <BDART>AR</BDART>
                <BDMNG>1.000</BDMNG>
                <BDTER>20190626</BDTER>
                <MATNR>LD1000345</MATNR>
                <MEINS>EA</MEINS>
                <VMENG>1.000</VMENG>
                <WERKS>1110</WERKS>
            </E1RESBL>
        </E1AFVOL>
    </E1AFFLL>
</E1AFKOL>

I only one need MATNR tag (Primary Key) in column 0. The contents of the E1RESBL tags are the values that need to added (columns 1-6) but not all the tags in the structure are needed.

I'm passing the XML document as an XElement and a List of column names. It returns all values in one List.

public static List<string> makeRows(XElement xmlD, List<string> columnNames) {
    List<string> columnNames = new List<string>();
    string[] wkOrdColumnNames = { "AUFNR", "AUSCH", "BDMNG", "BDTER", "MATNR", "MEINS", "WERKS" };   // all other fields needed
    columnNames.AddRange(wkOrdColumnNames); // adds the others
    // Load each structure
    var E1RESBL = xmlD.XPathSelectElements(@"/IDOC/E1AFKOL/E1AFFLL/E1AFVOL/E1RESBL");  // XML structure within doc
    var E1AFKOL = xmlD.XPathSelectElements(@"/IDOC/E1AFKOL");                     // column0 structure

    List<string> tagValues = new List<string>(); // all values from the tags are stored here
    // Where the WorkOrderID is added to the the values
    foreach (var innerNode in E1AFKOL) {                        // loops through the parent structure
        foreach (var innerElement in innerNode.XPathSelectElements(columnNames[0]))        // passes the element(AUFNR), gets the value of that tag
            tagValues.Add(innerElement.Value);            // adds it to the List
    }
    // The rest of the fields are added here
    foreach (var innerNode in E1RESBL) {                // loops through all tags in E1RESBL structure
        foreach (string i in columnNames) {            // loops through each string in the array of column names
            foreach (var innerElement in innerNode.XPathSelectElements(i))
                tagValues.Add(innerElement.Value);
        }
    }
    return tagValues;
}

I'm then passing the column names and values to a DataTable.

public static DataTable makeDataTable(List<string> columns, List<string> rows) {
    // Make Data table
    DataTable dataT = new DataTable();
    DataColumn dCol = new DataColumn();
    // Add columns
    foreach (var i in columns) {
        dCol.DataType = Type.GetType("System.String");
        dCol.ColumnName = i;
        dataT.Columns.Add(dCol.ColumnName, dCol.DataType);
    }
    // Add rows
    dataT.Rows.Add(rows[0]); // adds to column 0
    for (int r = 1; r < (rows.Capacity-1); r++) {   // starts after the 0th element (wkOrdID)
        DataRow dr = dataT.NewRow();            // Adds a new row
        Console.WriteLine(rows[r]);
        for (int j = 1; j < 7; j++) {           // skips the 0th column
            dr[j] = rows[r];                    // adds the values of the list to the row
        }
        dataT.Rows.Add(dr);
    }

I know there's probably a way easier way to do this so if you could help that would be much appreciated.

Example of finished product

Answer 1

Try following code :

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

namespace ConsoleApplication142
{
    class Program
    {
        const string XML_FILENAME = @"c:\temp\test.xml";
        const string CSV_FILENAME = @"c:\temp\test.csv";
        static void Main(string[] args) 
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("E1AFKOL SEGMENT", typeof(int));
            dt.Columns.Add("E1AFKOL MATNR", typeof(long));
            dt.Columns.Add("E1AFFLL SEGMENT", typeof(int));
            dt.Columns.Add("APLZL", typeof(string));
            dt.Columns.Add("E1AFVOL SEGMENT", typeof(int));
            dt.Columns.Add("VORNR", typeof(string));
            dt.Columns.Add("E1RESBL SEGMENT", typeof(int));
            dt.Columns.Add("AUSCH", typeof(decimal));
            dt.Columns.Add("BDART", typeof(string));
            dt.Columns.Add("BDMNG", typeof(decimal));
            dt.Columns.Add("BDTER", typeof(long));
            dt.Columns.Add("E1RESBL MATNR", typeof(string));
            dt.Columns.Add("MEINS", typeof(string));
            dt.Columns.Add("VMENG", typeof(decimal));
            dt.Columns.Add("WERKS", typeof(int));

            XDocument doc = XDocument.Load(XML_FILENAME);

            foreach (XElement E1AFKOL in doc.Descendants("E1AFKOL"))
            {
                int E1AFKOL_SEGMENT = (int)E1AFKOL.Attribute("SEGMENT");
                long E1AFKOL_MATNR = (long)E1AFKOL.Element("MATNR");

                foreach (XElement E1AFFLL in E1AFKOL.Elements("E1AFFLL"))
                {
                    int E1AFFLL_SEGMENT = (int)E1AFFLL.Attribute("SEGMENT");
                    string APLZL = (string)E1AFFLL.Element("APLZL");

                    foreach (XElement E1AFVOL in E1AFFLL.Elements("E1AFVOL"))
                    {
                        int E1AFVOL_SEGMENT = (int)E1AFVOL.Attribute("SEGMENT");
                        string VORNR = (string)E1AFVOL.Element("VORNR");

                        foreach (XElement E1RESBL in E1AFVOL.Elements("E1RESBL"))
                        {
                            int E1RESBL_SEGMENT = (int)E1RESBL.Attribute("SEGMENT");
                            decimal? AUSCH = (decimal?)E1RESBL.Element("AUSCH");
                            string BDART = (string)E1RESBL.Element("BDART");
                            decimal? BDMNG = (decimal?)E1RESBL.Element("BDMNG");
                            long? BDTER = (long?)E1RESBL.Element("BDTER");
                            string E1RESBL_MATNR = (string)E1RESBL.Element("MATNR");
                            string MEINS = (string)E1RESBL.Element("MEINS");
                            decimal? VMENG = (decimal?)E1RESBL.Element("VMENG");
                            int? WERKS = (int?)E1RESBL.Element("WERKS");

                            dt.Rows.Add(new object[] {
                                E1AFKOL_SEGMENT,
                                E1AFKOL_MATNR,
                                E1AFFLL_SEGMENT,
                                APLZL,
                                E1AFVOL_SEGMENT,
                                VORNR,
                                E1RESBL_SEGMENT,
                                AUSCH,
                                BDART,
                                BDMNG,
                                BDTER,
                                E1RESBL_MATNR,
                                MEINS,
                                VMENG,
                                WERKS 
                            });

                        }
                    }
                }
            }

            StreamWriter writer = new StreamWriter(CSV_FILENAME);
            string header = string.Join(",", dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName));
            writer.WriteLine(header);

            foreach (DataRow row in dt.AsEnumerable())
            {
                string rowStr = string.Join(",", row.ItemArray.Select(x => x.ToString()));
                writer.WriteLine(rowStr);
            }
            writer.Flush();
            writer.Close();


        }
    }



}