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. 1;John Doe;40  
  2. 2;Mark Nosurname;35  
  3. 3;Jane Doe;32  
  4. 4;Without name;60  
CsvToXML Function

Let's see the conversion function, plus some afterword.
  1. Private Sub CsvToXml(_inputFile As String, _dataName As String, _separator As Char, _outputFile As StringOptional _fieldnames() As String = Nothing)  
  2.     Dim dt As New DataTable(_dataName)  
  3.     Dim firstRow As Boolean = True  
  4.    
  5.     Using sr As New StreamReader(_inputFile)  
  6.         While Not (sr.EndOfStream)  
  7.             Dim fields() As String = sr.ReadLine.Split(_separator)  
  8.    
  9.             If firstRow Then  
  10.                 For ii As Integer = 0 To fields.Count - 1  
  11.                     Dim _fName As String = ""  
  12.                     If IsNothing(_fieldnames) Then  
  13.                         _fName = "Field" & ii.ToString("000")  
  14.                     Else  
  15.                         _fName = _fieldnames(ii)  
  16.                     End If  
  17.                     dt.Columns.Add(_fName)  
  18.                 Next  
  19.                 firstRow = False  
  20.             End If  
  21.    
  22.             dt.Rows.Add(fields)  
  23.         End While  
  24.    
  25.         dt.WriteXml(_outputFile)  
  26.         dt.Dispose()  
  27.     End Using  
  28. 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: 
  1. Dim fieldNames() As String = {"Id""Name""Age"}  
  2. CsvToXml("c:\temp\example.txt""TempTable"";""c:\temp\example.xml", fieldNames)  
And the final results will be:
  1. <?xml version="1.0" standalone="yes"?>  
  2. <DocumentElement>  
  3.   <TempTable>  
  4.     <Id>1</Id>  
  5.     <Name>John Doe</Name>  
  6.     <Age>40</Age>  
  7.   </TempTable>  
  8.   <TempTable>  
  9.     <Id>2</Id>  
  10.     <Name>Mark Nosurname</Name>  
  11.     <Age>35</Age>  
  12.   </TempTable>  
  13.   <TempTable>  
  14.     <Id>3</Id>  
  15.     <Name>Jane Doe</Name>  
  16.     <Age>32</Age>  
  17.   </TempTable>  
  18.   <TempTable>  
  19.     <Id>4</Id>  
  20.     <Name>Without name</Name>  
  21.     <Age>60</Age>  
  22.   </TempTable>  
  23. </DocumentElement>  
Source Code Bibliography

Next Recommended Readings