Convert CSV with unknown schema to XML

Question

I am reading a CSV file and converting it into XML format.

   public static string ConvertCSVToXML(string filePath)
   {
        var lines = File.ReadAllLines(filePath);
        XElement xml = new XElement("RootElement",
                from str in lines
                let col = str.Split(',')
                select new XElement("Item",
                    new XElement("Column1", col[0]),
                    new XElement("Column2", col[1]),
                    new XElement("Column3", col[2]),
                    new XElement("Column4", col[3])
                    )
            );
        return xml.ToString();
   }

Here we know the header of the CSV so it works. How to convert it when schema of the CSV is undefined? File contains header row but it is not known how many headers and what are header titles.

You mean [this valid](https://tools.ietf.org/html/rfc4180)? As in *"there is no formal specification in existence"*? — grek40, Aug 11 '17 at 11:26
A little sample: what is your expected result for the following csv file: `1,2;3,4`? — grek40, Aug 11 '17 at 11:33
Then you have additional constraints defined on your CSV format and you need to state them in the question if you want answers! — grek40, Aug 11 '17 at 11:35
Check this out for a starting point: https://stackoverflow.com/a/3508572/5265292 — grek40, Aug 11 '17 at 11:37

Mukesh Modhvadiya · Accepted Answer · 2017-09-14T13:33:17.513

0

Problems seem much bigger till it is not solved!

However it was possible using same linq to xml query with some modifications. For a moment I forgot that I can also access the index in linq select query,

    public static string ConvertCSVToXML(string filePath, char seperator)
    {
        var lines = File.ReadAllLines(filePath);
        var headers = lines[0].Split(seperator);
        XElement xml = new XElement("RootElement",
                            lines.Select(line => new XElement("Item",
                                line.Split(seperator).Select((column, index) => 
                                   new XElement(headers[index], column))
                            ))
                        );
        return xml.ToString();
    }

edited Sep 14 '17 at 13:33

answered Aug 11 '17 at 12:00

Mukesh Modhvadiya

2,178
2
27
32

1

Just be careful with this solution, as it will not work if the data itself contains commas or line breaks. – user2366842 Aug 11 '17 at 13:11
@user2366842, Yes indeed! As I said it will work if file is valid. If data itself contains seperator it will consider as seperate data value. But I had not used static comma as separator in fact. – Mukesh Modhvadiya Sep 14 '17 at 13:32

score 0 · Answer 2 · answered Aug 11 '17 at 12:44

With Cinchoo ETL - an open source ETL framework, you can do it easily

For a sample CSV (users.csv) file below

Id,Name
1,Washington
2,Lincoln

Code below shows how to read and produce XML file

using (var reader = new ChoCSVReader("users.csv").WithFirstLineHeader())
{
    using (var writer = new ChoXmlWriter("users.xml").WithXPath("Emps/Emp"))
        writer.Write(reader);
}

The output XML file look as below

<Emps>
  <Emp>
    <Id>1</Id>
    <Name>Washington</Name>
  </Emp>
  <Emp>
    <Id>2</Id>
    <Name>Lincoln</Name>
  </Emp>
</Emps>

Hope this helps.

Disclosure: I'm the author of this library

Thanks for the answer. Linq to sql is working for my purpose so I would not like to add new library reference. But it will definately be helpful to someone! — Mukesh Modhvadiya, Aug 11 '17 at 13:05

Convert CSV with unknown schema to XML

2 Answers2