CODEWITHSUNDEEP
vb.net
Jason Bayldon
Jason Bayldon

Reputation: 1296

Writing DataRows into multiple CSV files

I am using the following code to create a CSV from a collection of DataRows:

      Dim filename = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".csv" 'assign a temp file
        Using csvWriter As New System.IO.StreamWriter(filename, True)

            'write column headings
            csvWriter.WriteLine(String.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", "AcctNumber", "AccountHolderName", "AccountOption", "Address1", "Address2", "City", "State", "Zip"))

            For Each row in LetterRows 'collection of datarows
                csvWriter.WriteLine(String.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", row.AcctNumber, row.AcctHolderName, row.AccountOption, row.Address1, row.Address2, row.City, row.ST, row.ZIP))
            Next row

        End Using

This correctly writes all the datarows out to the CSV. I now want to put an upper limit, say 5000, and create a new csv each time I have written ~5000 records (I will maintain each file in a list of string). I was thinking along the lines of this:

 Dim filesCreated As New List(Of String)
 Dim filename As String = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".csv" 'assign a temp file
    filesCreated.Add(filename)

    For i = 0 To LetterRows.Count - 1

        Using csv As New System.IO.StreamWriter(filename, True) 'dont think this is efficient, what to do

            'write data here


        End Using

        If i Mod 5000 = 0 Then
            filename = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".csv" 'assign a temp file
            filesCreated.Add(filename)
        End If
    Next i

I think that is kind of sloppy however and I do not think the using loop in this context makes much sense because I am opening and closing the writer for each record. What is the best way to do this?

Upvotes: 0

Views: 384

Answers (1)

Mr Dog
Mr Dog

Reputation: 396

What you can do instead is put all the rows into a string and write the entire string at one time. This might be a bit better instead of initializing the writer every line?

 Dim to_write as String 

 For i = 0 To LetterRows.Count - 1

   to_write = to_write & vbCrLf & 'newline 

   if i = 5000 Then
      System.IO.File.WriteAllText(filename,to_write)   
      filename = 'increment or set your new/next filename here
      to_write = ""   'reset string and continue 
   End if

 Next

So the string will store all your data until it is ready to write. The vbCrLf will make sure each line is separated.

Upvotes: 1

Related Questions