csv to xml using LINQ

Question

I have a c# code in which I'm having a csv file containing customer data as follows:

ClientNumber1,Name1, BenefitCode1, EffectiveDt1 
ClientNumber1,Name1, BenefitCode2, EffectiveDt2 
ClientNumber2,Name2, BenefitCode3, EffectiveDt3

Now in this case I want the output XML to have mutliple tags for Benefit within same customer (eg, the first two rows in my csv have same customer but two different benefits)

My output xml should be like

<Member>
<Num>ClientNumber1<Num>
<Nm>Name1</Nm>
 <Benefit>
  <Bc>BenefitCode1</Bc>
  <Dt>EffectiveDt1</Dt>
 </Benefit>
 <Benefit>
  <Bc>BenefitCode2</Bc>
  <Dt>EffectiveDt2</Dt>
 </Benefit>
</Member>
<Member>
<Num>ClientNumber1<Num>
<Nm>Name2</Nm>
 <Benefit>
   <Bc>BenefitCode3</Bc>
   <Dt>EffectiveDt3</Dt>
 </Benefit>
</Member>

So basically, while I performing the LINQ I should also be able to check it with previous ClientNum and in case I have the same num, the benefit data should be added within the same Member tag.

Thanks.

it seems like you need deserialize csv strings to data structure, and then serialize it to xml — Grundy
– Grundy, Commented Jan 9, 2014 at 10:04
Welcome to SO! Show us what have your tried so far and what specific problem you have, as SO is not 'write me some code' portal. — Konrad Kokosa
– Konrad Kokosa, Commented Jan 9, 2014 at 10:04
Well currently I've tried the following code and this works but I need the Benefit data to be aggregated into single tag.<br> String[] File1 = File.ReadAllLines(@"C:\Desktop\test.csv"); String xml = ""; XElement top = new XElement("Client", from items in File1 let fields = items.Split(',') select new XElement("Benefit", new XElement("bc", fields[0]), new XElement("dt", fields[1]) ) ); File.WriteAllText(@"C:\Desktop\xmlout.xml", xml + top.ToString()); — Amar
– Amar, Commented Jan 9, 2014 at 10:38

Konrad Kokosa · Accepted Answer · 2014-01-09 10:44:21Z

2

I would split this into two clearly separated steps:

1) Create data representation from file - by creating anonymous types, grouped by client number:

var data = File.ReadAllLines(@"C:\Desktop\test.csv")
               .Select(line => line.Split(','))
               .Select(parts => new
                {
                    ClientNumber = parts[0].Trim(),
                    Name = parts[1].Trim(),
                    BenefitCode = parts[2].Trim(),
                    EffectiveDate = parts[3].Trim()
                })
                .GroupBy(x => x.ClientNumber);

2) Save data as XML:

var xml = new XDocument(
        new XElement("SomeRoot",
                output.Select(e => new XElement("Member",
                        new XElement("Num", e.Key),
                        new XElement("Name", e.First().Name),
                        e.Select(x => new XElement("Benefit",
                            new XElement("Bc", x.BenefitCode),
                            new XElement("Dt", x.EffectiveDate)))
                    ))
            )
    );

Then you can save it to file with xml.Save(...)

answered Jan 9, 2014 at 10:44

Konrad Kokosa

17k2 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amar Over a year ago

I am having some more columns and different data types but this code works. Thanks a lot...!!!

Collectives™ on Stack Overflow

csv to xml using LINQ

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related