CODEWITHSUNDEEP
c#xmllinqcsv
Indika Pathirage
Indika Pathirage

Reputation: 29

how to convert csv to xml with different headers

I have a csv with different column headers and I want to convert this to an XML payload.

The csv looks like following.

TEST1,APPLICATION_NAME,START_TIME,STOP_TIME,SERVICE_DESCRIPTION,FILING_STATUS,TIME_OF_LAST_UPDATE,RECORD_STATUS,ERROR_MESSAGE
,,20120101000000ES,20140131000000ES,New FGH Application,,,
,,20140304000000ES,20161231000000ES,New FGH Application,,,
,,20150109000000ES,20201231000000ES,New FGH Application,,,
TEST2,app,TOL,QUEUED
,nits,20120101000000ES,20201231000000ES

I tried to do this with Linq but couldn't figure out a way. Also I don't really want to specify columns like in the following example.

https://msdn.microsoft.com/en-us/library/bb387090

please note that this csv has different column headers.

The output I am expecting is;

<Root>
  <TEST1>
    <APPLICATION_NAME></APPLICATION_NAME>
    <START_TIME>20120101000000ES</START_TIME>
    <STOP_TIME>20140131000000ES</STOP_TIME>
    <SERVICE_DESCRIPTION>New NITS Application</SERVICE_DESCRIPTION>
    <FILING_STATUS></FILING_STATUS>
    <TIME_OF_LAST_UPDATE></TIME_OF_LAST_UPDATE>
    <RECORD_STATUS></RECORD_STATUS>
  </TEST1>
  <TEST1>    
    <APPLICATION_NAME></APPLICATION_NAME>
    <START_TIME>20140304000000ES</START_TIME>
    <STOP_TIME>20161231000000ES</STOP_TIME>
    <SERVICE_DESCRIPTION>New NITS Application</SERVICE_DESCRIPTION>
    <FILING_STATUS></FILING_STATUS>
    <TIME_OF_LAST_UPDATE></TIME_OF_LAST_UPDATE>
    <RECORD_STATUS></RECORD_STATUS>
  </TEST1>
  <TEST1>
    <APPLICATION_NAME></APPLICATION_NAME>
    <START_TIME>20150109000000ES</START_TIME>
    <STOP_TIME>20201231000000ES</STOP_TIME>
    <SERVICE_DESCRIPTION>New NITS Application</SERVICE_DESCRIPTION>
    <FILING_STATUS></FILING_STATUS>
    <TIME_OF_LAST_UPDATE></TIME_OF_LAST_UPDATE>
    <RECORD_STATUS></RECORD_STATUS>
  </TEST1>
  <TEST2>
    <app>nits</app>
    <TOL>20120101000000ES</TOL>
    <QUEUED>20201231000000ES</QUEUED>
 </TEST2>
</root>

Thanks for your help.

update: this is what I started off with.

string[] headers = lines[0].Split(',').Select(x => x.Trim('\"')).ToArray();
var xml = new XElement("root",
    lines.Where((line, index) => index > 0).Select(line => new XElement("TEST",
        line.Split(',').Select((column, index) => new XElement(headers[index], column)))));

Upvotes: 0

Views: 1213

Answers (2)

Kami
Kami

Reputation: 19407

Expanding on the linked example, you can do this

string[] source = File.ReadAllLines("text.csv");
string IGNORE_ROW = "XXXXX";
List<string> data = new List<string>();
string test = "";
for (int i = 0; i < source.Length; i++)
{
    string[] _str = source[i].Split(',');
    if (String.IsNullOrWhiteSpace(_str[0])) _str[0] = test;
    else
    {
        test = _str[0];
        _str[0] = IGNORE_ROW;
    }

    source[i] = String.Join(",", _str);
}

XElement data = new XElement("Root",
    from str in source
    where str.StartsWith(IGNORE_ROW) == false
    let fields = str.Split(',')
    select new XElement(fields[0],
        new XElement("APPLICATION_NAME", fields[1]),
        new XElement("START_TIME", fields[2]),
        new XElement("STOP_TIME", fields[3]),
        new XElement("SERVICE_DESCRIPTION", fields[4]),
        new XElement("FILING_STATUS", fields[5]),
        new XElement("TIME_OF_LAST_UPDATE", fields[6]),
        new XElement("RECORD_STATUS", fields[7])
    )
);
Console.WriteLine(data);

It is simply a matter for renaming the relevant elements and including them in the correct order.

// Edited

After reviewing the comment, it appears you are repeating the header within the data so that it can used as an element name. If you have control over the csv generation, remove this repeated row, and simply output the test value as the first element in the csv.

If you do not have control over the csv, you can alter the text so that it can be set. This is what the edited example does.

Upvotes: 2

DrKoch
DrKoch

Reputation: 9772

Use TextFieldParser to read the csv file and parse it into classes. Then use XDocument to build a xml document in memory and write it to a file after its completed.

Upvotes: 0

Related Questions