Introduction
I’ve been asked on several occasions to export data in a format that can be easily loaded into Excel or parsed by a simple application. CSV is an excellent format since it’s widely recognized and supported by Excel. In this article, I’ll provide a simple way to build a string containing the data in CSV format. As a bonus, I’ll cover saving the returned string to a file and include the application that I originally wrote the code for.
The Code
Below you will find a fairly basic function that will take a given DataSet, loop through the data and produce CSV formatted data. One thing that you might notice is that I’ve used a StringBuilder. Using string concatenation wouldn’t be an issue if you are expecting a small number of records, but using the StringBuilder class allows us to process a large number of records without causing a memory issue. You may also notice that the Delimiter is a parameter of the function, rather than being a hard-coded value. Space and Tab delimited data is also common, so I thought it would be a good idea to make the function flexible. Additionally, you should notice that it conditionally exports the column names. If you are using Excel, this would allow you to import the data with column names, making it easier to read.
From there, the function simply loops through the columns in all of the available rows and appends them to the StringBuilder. Please note that this code requires you to import the System.Text namespace for the StringBuilder class.
Public Function DelimitData(ByVal Data As DataSet, ByVal Delimiter As String, ByVal NoHeader As Boolean) As String
Dim sbDelimited As New StringBuilder()
If Data.Tables.Count > 0 Then
With Data.Tables(0)
If Not NoHeader Then
For iCols As Integer = 0 To (.Columns.Count - 1)
If iCols <> 0 Then
sbDelimited.Append(Delimiter)
End If
sbDelimited.Append(.Columns(iCols).ColumnName)
Next
sbDelimited.AppendLine()
End If
For iRow As Integer = 0 To (.Rows.Count - 1)
For iCol As Integer = 0 To (.Columns.Count - 1)
If iCol <> 0 Then
sbDelimited.Append(Delimiter)
End If
sbDelimited.Append(.Rows(iRow)(iCol))
Next
sbDelimited.AppendLine()
Next
End With
End If
Return sbDelimited.ToString()
End Function
Exporting the CSV to a File
Once again, this is a very simple block of code. We’ll make use of a simple StreamWriter to output the string to a file. I attempted to make use of a function in the new My namespaces (My.Computer.FileSystem.WriteAllText), but ended up with some strange characters in the first line of the file. However, this method appears to work without any issues.
Dim sOutput As String = ""
sOutput = DelimitData(dsData, sDelim, bNoHeader)
Dim sw As New StreamWriter(sOutputFile, False)
sw.Write(sOutput)
sw.Close()
Additional Applications
The reason why I wrote the above blocks of code was because we needed a simple console application that could run a query against an access database at a scheduled interval. It’s a pretty simple application and might be of use to someone, so I’ll include the code below. It is also available for download here.
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Text
Module Export
''' <summary>
''' Main execution function
''' </summary>
''' <param name="cmdArgs">Command line arguments</param>
''' <remarks></remarks>
Sub Main(ByVal cmdArgs() As String)
Try
Trace.Listeners.Add(New TextWriterTraceListener(My.Application.Info.DirectoryPath() + "\Export.log"))
Trace.AutoFlush = True
Console.WriteLine("Log file - " + My.Application.Info.DirectoryPath() + "\Export.log")
Catch ex As Exception
Console.WriteLine("Unable to start log file")
Console.WriteLine(ex.ToString())
End Try
Dim sDelim As String = ","
Dim sAccessFile As String = ""
Dim sQuery As String = ""
Dim sOutputFile As String = "Export.csv"
Dim bNoHeader As Boolean
Console.WriteLine("Starting" + Date.Now.ToString())
Trace.WriteLine("Starting" + Date.Now.ToString())
For i As Integer = 0 To (cmdArgs.Length - 1)
Console.WriteLine(cmdArgs(i))
If String.Compare(cmdArgs(i), "/file", True) = 0 Then
If i + 1 < cmdArgs.Length Then
sAccessFile = cmdArgs(i + 1)
End If
ElseIf String.Compare(cmdArgs(i), "/query", True) = 0 Then
If i + 1 < cmdArgs.Length Then
sQuery = cmdArgs(i + 1)
End If
ElseIf String.Compare(cmdArgs(i), "/export", True) = 0 Then
If i + 1 < cmdArgs.Length Then
sOutputFile = cmdArgs(i + 1)
End If
ElseIf String.Compare(cmdArgs(i), "/d", True) = 0 Then
If i + 1 < cmdArgs.Length Then
sDelim = cmdArgs(i + 1)
End If
ElseIf String.Compare(cmdArgs(i), "/noheader", True) = 0 Then
bNoHeader = True
ElseIf cmdArgs(i)(0) = "/"c Then
'must be a sql parameter
If i + 1 < cmdArgs.Length Then
sQuery = sQuery.Replace("<" + cmdArgs(i).Remove(0, 1) + ">", cmdArgs(i + 1))
End If
End If
Next
sQuery = sQuery.Replace("<date>", Date.Now.ToShortDateString)
If sAccessFile = "" Or sQuery = "" Then
Console.WriteLine("Missing Parameters. Syntax:")
Console.WriteLine("/file c:\exportTest.mdb /query ""select * from email"" /export file.csv(optional - default Export.csv) /d , (optional - default ,)")
Else
Try
Dim sOutput As String = ""
sOutput = DelimitData(GetData(sAccessFile, sQuery), sDelim, bNoHeader)
Dim sw As New StreamWriter(sOutputFile, False)
sw.Write(sOutput)
sw.Close()
Catch ex As Exception
Console.WriteLine(ex.ToString())
Trace.WriteLine(ex.ToString())
End Try
Trace.WriteLine("Done" + Date.Now.ToString())
Console.WriteLine("Done" + Date.Now.ToString())
End If
End Sub
''' <summary>
''' Takes any data and formats it into a delimited string
''' </summary>
''' <param name="Data"></param>
''' <param name="Delimiter"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function DelimitData(ByVal Data As DataSet, ByVal Delimiter As String, ByVal NoHeader As Boolean) As String
Dim sbDelimited As New StringBuilder()
If Data.Tables.Count > 0 Then
With Data.Tables(0)
If Not NoHeader Then
For iCols As Integer = 0 To (.Columns.Count - 1)
If iCols <> 0 Then
sbDelimited.Append(Delimiter)
End If
sbDelimited.Append(.Columns(iCols).ColumnName)
Next
sbDelimited.AppendLine()
End If
For iRow As Integer = 0 To (.Rows.Count - 1)
For iCol As Integer = 0 To (.Columns.Count - 1)
If iCol <> 0 Then
sbDelimited.Append(Delimiter)
End If
sbDelimited.Append(.Rows(iRow)(iCol))
Next
sbDelimited.AppendLine()
Next
End With
End If
Return sbDelimited.ToString()
End Function
''' <summary>
''' Runs the requested query agains the provided Access database
''' </summary>
''' <param name="FileName">Location of the Access Database</param>
''' <param name="Query">Query to run</param>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetData(ByVal FileName As String, ByVal Query As String) As DataSet
Dim sConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName
Dim dsData As New DataSet
Try
Console.WriteLine("Running Query " + Query)
Trace.WriteLine("Running Query " + Query)
Dim daAccess As New OleDbDataAdapter(Query, sConnectString)
daAccess.Fill(dsData)
Catch ex As Exception
Trace.WriteLine(ex.ToString())
Console.WriteLine(ex.ToString())
End Try
Return dsData
End Function
End Module