Convert CSV to XML Using VB.NET Classes

I have a csv file with predefined column headers and a variable number of rows. I need to convert this file by code into an xml file that can be read into a DataSet via the ReadXml feature.

How do I go about this? XSLTransform or some other reader class?

Thanks.
nathanpzAsked:
Who is Participating?
 
J_MakConnect With a Mentor Commented:
Ok silly me, of course it should look exaclty the same as your original xml file!!!!

Anyway, there are two ways of creating your XML file from the CSV:

1. Using XML DOM
2. Streaming Model

This article will explain it:

http://builder.com.com/5100-6373-1044638.html

Both are achievable in .NET, it is up to you to choose which way to go about it.

Have a look at this link: http://samples.gotdotnet.com/quickstart/howto/doc/Xml/DOMInterfaceXmlDocument.aspx, which comes from the same helpful site that I gave to you before. It explains how to use DOM to create your XML files.

I guess coverting from CSV to XML would require the same process as going the other way, that is, you need a stylesheet to define the transformation.

When you want to make this compatible with a Dataset, there are two ways of doing so. But either way, you must define a schema.

1. The schema may be in the same file as your XML, or
2. The schema may be in a totally separate file.

In both cases, once you have created your XML and schema, you can use the ReadXml function. When you want to generate your schema, you can firstly define the tables and required in your dataset, because what you want are columns with headers and values which contitute to rows. It is therefore a table in itself. From that, you can generate your own schema and even your own XML.

Have a look at these examples:

http://www.startvbdotnet.com/ado/datatable.htm
http://www.visual-basic-data-mining.net/forum/ShowPost.aspx?PostID=642

Have a look at these useful links for more interesting stuff (although some are in C#):

http://www.awprofessional.com/articles/article.asp?p=30593&seqNum=1
http://www.awprofessional.com/articles/article.asp?p=30593&seqNum=2
http://www.dotnetjunkies.com/Tutorial/83DA8FC4-79D1-477C-9D5E-70444A476270.dcik
0
 
J_MakCommented:
Hi nathanpz,

I'm glad that you were able to convert your xml file to a csv successfully from previous discussions. I guess I could see it coming that you were gonna want to convert it back to xml!!!!

Got two questions:

1. Can I have a look at your csv file?
2. How do you want it to look like in xml?

Cheers.
0
 
nathanpzAuthor Commented:
Hi J_Mak.
I'm building this thing in a pretty predictable way I guess! Just trying to get used to moving data around between applications.

The csv file will have the following column headers:

Class,Last Name,First Name,New Score, Previous Score, Level, Previous1, Previous2, Previous3, Date1,Date2,Date3,MaxScore,DateN

The XML file should turn out as follows:

<?xml version="1.0" standalone="yes" ?>
- <NewDataSet>
- <Students>
  <Class>1 - A</Class>
  <Last_x0020_Name>Brooks</Last_x0020_Name>
  <First_x0020_Name>Ben</First_x0020_Name>
  <New_x0020_Score>10</New_x0020_Score>
  <Previous_x0020_Score>7</Previous_x0020_Score>
  <Level>C</Level>
  <Previous1>3</Previous1>
  <Previous2>5</Previous2>
  <Previous3>7</Previous3>
  <Date1>02Jan2000</Date1>
  <Date2>04Jan2000</Date2>
  <Date3>09Jan2000</Date3>
  <MaxScore>20</MaxScore>
  <DateN>12Jan2000</DateN>
  </Students>
  </NewDataSet>

It's pretty much the exact reverse of what my XSLT currently does.



0
 
nathanpzAuthor Commented:
Thanks J_Mak,

These links look great. I should be right from here.

Cheers.
0
 
J_MakCommented:
Glad I could help
0
All Courses

From novice to tech pro — start learning today.