DataTable to CSV

There are many examples (and ways) available to save data from a DataTable to a csv TextFile. The vast majority utilize some type of loop to iterate through the rows and either  a string variable or a StringBuilder object to create the delimited line of text to save to the file.

In this article I’m going to show a slightly different approach.

To set up the Form, add a DataGridView and 2 Buttons from the ToolBox. Double click the Form’s TitleBar to create the Form Load event. First we’ll create a couple of class-level variables.

<code>
    Dim dtTest As New DataTable
    Dim f_name As String = "C:\Users\Joe\Desktop\Test Folder\createTxt.txt"
    Dim sb As StringBuilder
</code>

In the Form Load event we’ll add some Columns and then some records to the table. Then set the DataGridView DataSource property to the table.

<code>
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 dtTest.Columns.Add("ID", GetType(Integer))
 dtTest.Columns.Add("Col2", GetType(String))
 dtTest.Columns.Add("Col3", GetType(Integer))

 Dim rndm As New Random
 For i As Integer = 65 To 70
 dtTest.Rows.Add(i, Chr(i), rndm.Next(100, 201))
 Next
 DataGridView1.DataSource = dtTest.DefaultView
 End Sub
</code>

Now that we have some data in the table, in the first Button Click event we’ll run through a typical example of saving the data. Note that I have commented out the lines to write to the textfile and am merely writing to the Immediate Window within the IDE. I find this more useful when testing an app – I don’t have to keep opening the textfile to check the results. If it displays properly in the Immediate Window, it will write properly to the textfile.

<code>
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

         Using sw As New StreamWriter(f_name, False)
            sb = New StringBuilder
            For Each dcol As DataColumn In dtTest.Columns
                sb.Append(dcol.ColumnName & ",")
            Next

            'sw.WriteLine(sb.ToString)
            Debug.WriteLine(sb.ToString)
            For Each drow As DataRow In dtTest.Rows
                sb = New StringBuilder
                For i As Integer = 0 To dtTest.Columns.Count - 1
                    sb.Append(drow.Item(i).ToString & ",")
                Next

                'sw.WriteLine(sb.ToString)
                Debug.WriteLine(sb.ToString)
            Next
        End Using
    End Sub
</code>

There are 3 loops here. The first loops through the Columns in the table and creates the first line with the column names. After that the second loops through all the Rows in the table. The third is a nested loop to iterate through all the Items (Columns) within that row and create a comma delimited line for the data in each row. If you run the app you should get 7 lines in the Immediate Window. Try it now.

Did you notice anything? Hopefully you noticed each line has an extra comma at the end because of the way we have used the StringBuilder to create each line. This can be corrected by adding this to the code right above both commented out sw.WriteLine lines. In the code above I have thoughtfully left a blank line for you to add it in

<code>
sb.Remove(sb.Length - 1, 1)
</code>

While that code isn’t too complicated or messy, now we’ll look at another option using String.Join, a Lambda Expression and the Column and DataRow collections from the table. It will also use the ItemArray property of each DataRow to simplify retrieving each field from a row. The only loop required will be one to iterate through the DataRow collection.

Let’s tackle the Columns first. In the Click event of the second button, add this code.

<code>
Private Sub btnSave2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave2.Click
        Using sw As New StreamWriter(f_name, False)
            Dim cols As String = String.Join(",", dtTest.Columns.Cast(Of DataColumn)().Select(Function(c) c.ColumnName).ToArray())
            'sw.WriteLine(cols)
            Debug.WriteLine(cols)

        End Using
    End Sub
</code>

If you run this now, you should get a line with the Column names separated by a comma. The advantage of using String.Join is you don’t end up with the extra comma at the end of the line. And it’s a nice compact one line of code to get all the Column names using the Select method along with a Lambda Expression. Pretty neat, huh?

But now we need the data from the table, also. We will need to iterate through the DataRow collection with a loop, but by using String.Join and a Lambda Expression with the DataRow ItemArray property, we can avoid the nested loop to get each Item from each row to create our comma-separated line. Add this block right above the End Using line.

<code>
For Each drow As DataRow In dtTest.Rows
                Dim lineoftext = String.Join(",", drow.ItemArray.Select(Function(s) s.ToString).ToArray)
                'sw.WriteLine(lineoftext)
                Debug.WriteLine(lineoftext)
            Next
</code>

When you run the completed code, the output from both Buttons should be the same. However, I find the second option much cleaner code-wise. You don’t have the nested loops and you don’t have to remove a comma from the end of the string.

Advertisement