Convert CSV File to XML With DataTable

Scope

In this short article, we'll see how to convert a common CSV file into its XML representation, using Visual Basic .NET and the powerful functionalities of DataTable objects. When working in the field of data management, data manipulation is an everyday task and it can be useful to quickly convert a given set of data in other forms.

Sample Data

Speaking about a CSV file, let's suppose we have a file named "example.txt", in the C:\Temp folder. It has content like this:

1;John Doe;40
2;Mark Nosurname;35
3;Jane Doe;32
4;Without name;60

CsvToXML Function

Let's see the conversion function, plus some afterword.

Private Sub CsvToXml(_inputFile As String, _dataName As String, _separator As Char, _outputFile As String, Optional _fieldnames() As String = Nothing)
    Dim dt As New DataTable(_dataName)
    Dim firstRow As Boolean = True

    Using sr As New StreamReader(_inputFile)
        While Not (sr.EndOfStream)
            Dim fields() As String = sr.ReadLine.Split(_separator)

            If firstRow Then
                For ii As Integer = 0 To fields.Count - 1
                    Dim _fName As String = ""
                    If IsNothing(_fieldnames) Then
                        _fName = "Field" & ii.ToString("000")
                    Else
                        _fName = _fieldnames(ii)
                    End If
                    dt.Columns.Add(_fName)
                Next
                firstRow = False
            End If

            dt.Rows.Add(fields)
        End While

        dt.WriteXml(_outputFile)
        dt.Dispose()
    End Using
End Sub

Our function requires a path for our CSV file (_inputFile), a name to be assigned to our main XML entity (_dataName), the character used as a field separator (_separator), a path for the output XML file (_outputFile), and an optional array of strings, representing specific field names to be assigned (_fieldnames).

We start our routine by creating a new DataTable and opening our CSV file. Reading the first line, the columns of the DataTable are created. If we have passed the argument _fieldnames, the function uses our passed string, otherwise it proceeds to create a column with a more generic name, such as "FieldXYZ", where "XYZ" is replaced by the field's index.

In a while loop, we process every line of the CSV file, splitting each line using a _separator as the split character and assigning each splitted parameter to a local array of strings that will be used as an argument for the standard Rows.Add method of the DataTable object. At the end of the loop, we'll have a full-fledged DataTable, with its columns and rows compiled with CSV delimited data.

Invoking the WriteXML method will produce an XML representation of the DataTable data.

The final results assume the preceding CSV data, we could use our function as follows: 

Dim fieldNames() As String = {"Id", "Name", "Age"}
CsvToXml("c:\temp\example.txt", "TempTable", ";", "c:\temp\example.xml", fieldNames)

And the final results will be:

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <TempTable>
    <Id>1</Id>
    <Name>John Doe</Name>
    <Age>40</Age>
  </TempTable>
  <TempTable>
    <Id>2</Id>
    <Name>Mark Nosurname</Name>
    <Age>35</Age>
  </TempTable>
  <TempTable>
    <Id>3</Id>
    <Name>Jane Doe</Name>
    <Age>32</Age>
  </TempTable>
  <TempTable>
    <Id>4</Id>
    <Name>Without name</Name>
    <Age>60</Age>
  </TempTable>
</DocumentElement>

Source Code

• CsvToXml.zip

Bibliography

• DataTable Class
• StreamReader Class