|
Writing the Contents of a DataSet to CSV (VB.NET) |
|
|
Written by: Evan Cummings
Writing the Contents of a DataSet to CSVOften times a Comma Seperated Value (CSV) file is a handy (or even necessary) way in which to transfer datastores between different people, systems, or departments. SQL Server in particular can automatically generate such files. But what if you need a completely automated system? One problem I have run into recently is that I am processing an incredibly large amount of data from a third-party web service that is being inserted into a local SQL Server database. This process takes many hours to complete, so it would be very nice if we could schedule this process to run unattended as well as produce the CSV file required to pass on to other departments.
Luckily, the process isn't very difficult - we can simply make use of ADO.NET objects to handle the heavy lifting for us.
To accomplish this task, we need several objects - A DataSet, a DataRow, an Object, and a StringBuilder. The goal is to fill our DataSet with the data source we wish to convert to a CSV format, then iterate through each DataRow within that DataSet. Within each DataRow, we will need to iterate through each individual field or column, which we can nicely represent through a generic Object, allowing us to avoid types and reference problems. At this point, we can append a comma through the StringBuilder object. With this background in place, lets take a look at the solution!
First of all, we need to get the necessary housekeeping out of the way first - our SQLConnection, SQLCommand, and SQLDataAdapters:
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("connString").ConnectionString)
Dim sqlCmd As New SqlCommand("SELECT * FROM CUSTOMERS", conn)
Dim sqlDA As New SqlDataAdapter()
sqlDA.SelectCommand = sqlCmd
Dim csvSet As New DataSet
Try
sqlDA.Fill(csvSet)
Catch ex As SqlException
Console.WriteLine(ex.Message)
End Try
Note that our DataSet we will be working with, csvSet, has been created and filled with the information contained within the customers table. We now need to declare instances of the remaining objects needed for the conversion:
Dim str As New Text.StringBuilder
Dim dr As DataRow
Dim column As Object
The StringBuilder class offers us a variety of helper methods to manipulate strings which will be handy when we need to start appending commas to each of our DataSets columns. The DataRow object represents an individual row within a DataSet. We need this object so we can effectively utilize a foreach loop to iterate through the DataSet's Rows collection. And finally, the column object will offer us a common type to work with individual fields within our DataRows.
To actually begin this conversion, we need to bring together all of these objects and concepts into a series of nested loops:
Try
For Each dr In csvSet.Tables(0).Rows
For Each column In dr.ItemArray
str.Append(column.ToString & ",")
Next
str.Replace(",", vbCrLf, str.Length - 1, 1)
Next
Catch ex As Exception
Console.WriteLine("Error converting Dataset to CSV - ")
End Try
The structure is the key here - our outer loop iterates through our DataSet's, csvSet, Rows Collection through a DataRow object, dr. Our inner loops iterates through each column (or field) within the DataRow. ADO.NET represents the actual layout of fields (amongst other things, such as tables) in an array based structure. The specific collection we need to iterate through is the ItemArray of the DataRow. Within this iteration, we use our StringBuilder object, str, to Append a comma to the end of each individual field. Remember that the object column represents a single field - we simply append a comma to the end of this. The final consideration is that we are appending a comma to every field, but this is not the correct format for a CSV file - each new record begins on a new line. To compensate for this, we simply perform a Replace operation on the last column in the DataRow and substitute in a new line rather than a comma.
Thats all there is to creating a CSV, but we aren't done yet. The DataSet (and the string we built) is a memory resident data structure, and unless we do something with it, thats where it will live and die. We need to write the contents of our string out to a file that will persist the lifetime of our program:
Try
My.Computer.FileSystem.WriteAllText("C:\testcsv.csv", str.ToString, False)
Catch ex As Exception
Console.WriteLine("Write Failure")
End Try
Here I use a unique feature of Visual Basic available in all versions of the .NET Framework 2.0 and up. The My keyword is basically a shortcut operator that exposes extremely common functions preformed by Visual Basic developers. We simply use the WriteAllText method and provide it a path, our CSV as a string, and a boolean value for whether we want to append the string to the current file or simply overwrite it. These operations can, of course, be accomplished with typical file streams.
That is all there is to it - we simply pulled our data into a DataSet object and iterated through it building a string in the process with our CSV file contained inside of it. To finish up, we wrote this string to a simple, plain text file that allows for us to easily pass it around!
|
|
|
|
|
|