[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

CSV to XML in VB.Net

The CSV file can have multiple records in it. The first row will contain the column headers for each column. The XML should contain an auto-generated row ID for each record in the CSV file.
For example :
Input CSV file
COL1,COL2,COL3
AAA,BBB,CCC
XXX,YYY,ZZZ
The resultant XML file should be
<Records filepath=C:\input.csv>
        <Record RowID=1>
                <COL1>AAA</COL1>
                <COL2>BBB</COL2>
                <COL3>CCC</COL3>
        </Record>
        <Record RowID=2>
                <COL1>XXX</COL1>
                <COL2>YYY</COL2>
                <COL3>ZZZ</COL3>
        </Record>
<Records>
0
Monaglit
Asked:
Monaglit
  • 4
  • 4
1 Solution
 
käµfm³d 👽Commented:
What is your question?
0
 
MonaglitAuthor Commented:
I need to convert the above CSV file to XML in VB.Net, need help with the coding. Thanks.
0
 
käµfm³d 👽Commented:
Here's one approach:

Module Module1

    Sub Main()
        Dim csvPath As String = "input.csv"
        Dim parser As New FileIO.TextFieldParser(csvPath)
        Dim xdoc As New Xml.XmlDocument()
        Dim root As Xml.XmlNode = xdoc.AppendChild(xdoc.CreateElement("Records"))
        Dim currentRecord As Integer = 0

        root.Attributes.Append(xdoc.CreateAttribute("filepath")).InnerText = csvPath

        parser.TextFieldType = FileIO.FieldType.Delimited
        parser.HasFieldsEnclosedInQuotes = True
        parser.Delimiters = New String() {","}

        Dim headers() As String = parser.ReadFields()

        While Not parser.EndOfData
            Dim record As Xml.XmlNode = root.AppendChild(xdoc.CreateElement("Record"))
            Dim fields() As String = parser.ReadFields()

            currentRecord += 1

            record.Attributes.Append(xdoc.CreateAttribute("RowID")).InnerText = currentRecord.ToString()

            For i As Integer = 0 To headers.Count - 1
                record.AppendChild(xdoc.CreateElement(headers(i))).InnerText = fields(i)
            Next
        End While

        xdoc.Save("output.xml")
    End Sub

End Module

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
käµfm³d 👽Commented:
You don't have to use the HasFieldsEnclosedInQuotes = True option if your CSV doesn't have such fields; you didn't specify, so I left in to be safe.
0
 
MonaglitAuthor Commented:
Thanks a lot. Works very well.
0
 
MonaglitAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Monaglit's comment http:/Q_27410156.html#37011622

for the following reason:

Exactly what I was looking for. Works just right.
0
 
käµfm³d 👽Commented:
I'm confused. You selected your own comment as the answer?
0
 
MonaglitAuthor Commented:
Sorry, yes I am new..
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now