Solved

Convert CSV to XML Using VB.NET Classes

Posted on 2004-10-21
936 Views
Last Modified: 2012-05-05
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.
0
Question by:nathanpz
    5 Comments
     
    LVL 7

    Expert Comment

    by:J_Mak
    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
     

    Author Comment

    by:nathanpz
    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
     
    LVL 7

    Accepted Solution

    by:
    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
     

    Author Comment

    by:nathanpz
    Thanks J_Mak,

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

    Cheers.
    0
     
    LVL 7

    Expert Comment

    by:J_Mak
    Glad I could help
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    911 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now