Visual Basic and SQL Server – Exporting Data (CSV)

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. CSV, or Comma Separated Value files, are one such file format that allows for both of these scenarios.

Below is an example of how Visual Basic can be used to export data to a CSV file called ‘personexport.csv’, from a SQL Server database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting and importing data.

Firstly, a connection to the database is established, the CSV file path and name are set and a check is made to see if the path actually exists. If it does, a query is executed to extract the data from the database and the CSV file is opened for writing. The table headers are then added to the CSV file, followed by the rows of data, one by one. Finally, confirmation of a successful export is provided. A ‘Try-Catch-Finally’ block is used to catch any errors that may occur, as well as close the database connection and CSV file, regardless of whether the export is successful or not.

' Database connection variable.
Dim connect = New SqlConnection(
   "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
   "User Id=DemoUN; Password=DemoPW")

Try

   ' Connect to database.
   connect.Open()

Catch ex As Exception

   ' Confirm unsuccessful connection and stop program execution.
   Console.WriteLine("Database connection unsuccessful.")
   End

End Try

' Export path and file.
Dim exportPath As String = "C:\demo\"
Dim exportCsv As String = "personexport.csv"

' Stream writer for CSV file.
Dim csvFile As StreamWriter = Nothing

' Check to see if the file path exists.
If Directory.Exists(exportPath) Then

   Try

       ' Query text.
       Dim sqlText As String =
           "SELECT id, firstname, lastname, title, dob " &
           "FROM dbo.person " &
           "ORDER BY id"

       ' Query text incorporated into SQL command.
       Dim sqlSelect As New SqlCommand(sqlText, connect)

       ' Execute SQL and place data in a reader object.
       Dim reader As SqlDataReader = sqlSelect.ExecuteReader()

       ' Stream writer for CSV file.
       csvFile = New StreamWriter(exportPath + exportCsv)

       ' Add the headers to the CSV file.
       csvFile.WriteLine(String.Format("""{0}"",""{1}""," _
                                       & """{2}"",""{3}""," _
                                       & """{4}""",
                                       reader.GetName(0),
                                       reader.GetName(1),
                                       reader.GetName(2),
                                       reader.GetName(3),
                                       reader.GetName(4)))

       ' Construct CSV file data rows.
       Do While reader.Read()

           ' Add line from reader object to new CSV file.
           csvFile.WriteLine(String.Format("""{0}"",""{1}""," _
                                       & """{2}"",""{3}""," _
                                       & """{4}""",
                                       reader(0),
                                       reader(1),
                                       reader(2),
                                       reader(3),
                                       reader(4)))

       Loop

       ' Close the reader.
       reader.Close()

       ' Message stating export successful.
       Console.WriteLine("Data export successful.")

   Catch ex As Exception

       ' Message stating export unsuccessful.
       Console.WriteLine("Data export unsuccessful.")
       End

   Finally

       ' Close the database connection and CSV file.
       connect.Close()
       csvFile.Close()

   End Try

Else

   ' Display a message stating file path does not exist.
   Console.WriteLine("File path does not exist.")

End If

The CSV file produced contains the following data. Note that, although dates in an SQL Server database are stored in the format YYYY-MM-DD (four digit year, two digit month and two digit day), they are output to CSV in a day, month, year and time format.

"id","firstname","lastname","title","dob"
"1","Bob","Smith","Mr","20/01/1980 00:00:00"
"3","Fred","Bloggs","Mr","07/05/1975 00:00:00"
"4","Alan","White","Mr","20/03/1989 00:00:00"
"5","Fiona","Bloggs","Mrs","19/05/1985 00:00:00"
"6","Zoe","Davis","Miss","11/07/1979 00:00:00"
"7","Tom","Ingram","Mr","04/10/1971 00:00:00"
"8","Karen","Thomas","Mrs","08/03/1969 00:00:00"
"9","Samantha","Yates","Miss","27/08/1995 00:00:00"