Wednesday, February 4, 2009

How to easily produce CSV files

It can be easily done either from dataset or datareader.

If dataset (datatable) is the source then:


Public Shared Sub ProduceCSV(ByVal dt As DataTable, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)
    Dim i As Int32
    Dim j As Int32
    Dim myCIintl As System.Globalization.CultureInfo
    myCIintl = System.Globalization.CultureInfo.CurrentCulture
    If (WriteHeader) Then
      Dim arr(dt.Columns.Count - 1) As String
      For i = 0 To dt.Columns.Count - 1
        arr(i) = dt.Columns(i).ColumnName
        arr(i) = GetWriteableValue(arr(i))
      Next
      file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, arr))
    End If

    For j = 0 To dt.Rows.Count - 1
      Dim dataArr(dt.Columns.Count - 1) As String
      For i = 0 To dt.Columns.Count - 1
        Dim o As Object = dt.Rows(j)(i)
        dataArr(i) = GetWriteableValue(o)
      Next
      file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, dataArr))
    Next
  End Sub

Making csv files from reader:


Public Shared Sub ProduceCSV(ByVal reader As DbDataReader, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)
    Dim i As Int32
    Dim myCIintl As System.Globalization.CultureInfo

    While reader.Read
      myCIintl = System.Globalization.CultureInfo.CurrentCulture
      If (WriteHeader) Then
        Dim arr(reader.FieldCount) As String
        For i = 0 To reader.FieldCount - 1
          arr(i) = reader.GetName(i)
          arr(i) = GetWriteableValue(arr(i))
        Next
        file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, arr))
        WriteHeader = False ''header zapisemo samo enkrat
      End If

      Dim dataArr(reader.FieldCount - 1) As String
      For i = 0 To reader.FieldCount - 1
        Dim val As String
        'If reader.GetName(i).ToLower.Contains("stid") Then
        '  val = String.Format("""STID:{0}""", reader.Item(i))
        'Else
        val = GetWriteableValue(reader.Item(i))
        'End If

        dataArr(i) = val
      Next
      file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, dataArr))
    End While
  End Sub

Since csv in my case contain millions of records I use reader as a source to limit memory usage and boost performance.
Post a Comment