VBScript 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.

The example below exports data from a table called ‘person’, which contains five columns, ‘id’, ‘firstname’, ‘lastname’, ‘title’ and ‘dob’, from within an SQL Server database, to a CSV file in a specified location.

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

Firstly, the CSV file path and name are set, and a check is made to see if the file path exists. If it does exist, a connection to the database is established and the data is retrieved using an SQL statement. If data has been returned, the CSV file is created and the header row is written to the file. The data rows are then constructed, one by one, and added to the file as well. The ‘chr’ function is used to add double quotes around the items of data in the data rows.

A rolling seven day backup is also included. This makes a copy of the CSV file that has just been created, giving it a name that includes the index number for the day of the week, along with the day itself, for example, 'personexport-2-monday.csv', for the backup on a Monday. Here, Sunday is classed as the first day of the week, with an index value of one. Note that the backup is only done for the first time that this is run in a given day. Backups are then overwritten each week.

Feedback is provided as to the success or failure of the task.

' File system object.
Dim fso
Set fso = WScript.CreateObject("Scripting.FileSystemObject")

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

' Check if the file path exists.
If fso.FolderExists(exportPath) Then

    ' Database connection.
    Dim connection
    Set connection = WScript.CreateObject("ADODB.Connection")
    connection.Open "Provider=SQLOLEDB.1;Data Source=MSSQLSERVERDEMO;" & _
	            "Initial Catalog=Demo;UID=DemoUN;PWD=DemoPW"
	
    ' SQL to retrieve data from the person table.
    Dim sqlText
    sqlText = "SELECT id, firstname, lastname, title, dob " & _
              "FROM dbo.person " & _
              "ORDER BY id"
	
    ' Retrieve the data into a record set.
    Dim recordSet
    Set recordSet = connection.Execute(sqlText)
	
    ' Check if any records have been returned.
    If recordSet.EOF Then
	
        ' Message stating no data to export.
        WScript.Echo "There is no data to export."
	
    Else
	
        ' Create the CSV file.
        Dim csvFile
        Set csvFile = fso.CreateTextFile(exportPath & exportCsv, True)
		
        ' Add the header row to the CSV file.
        csvFile.WriteLine("""id"",""firstname"",""lastname"",""title"",""dob""")
		
        ' Data row variable.
        Dim dataRow
	
        ' Process the rows of data.
        Do While Not recordSet.EOF
		
            ' Construct the data row.
            dataRow = Chr(34) & recordSet("id") & Chr(34) & ","
            dataRow = dataRow + Chr(34) & recordSet("firstname") & Chr(34) & ","
            dataRow = dataRow + Chr(34) & recordSet("lastname") & Chr(34) & ","
            dataRow = dataRow + Chr(34) & recordSet("title") & Chr(34) & ","
            dataRow = dataRow + Chr(34) & recordSet("dob") & Chr(34)
			
            ' Add the row to the CSV file.
            csvFile.WriteLine(dataRow)
			
            ' Move to the next record.
            recordSet.MoveNext
		
        Loop

        ' Close the CSV file.
        csvFile.Close

        ' Today's date.
        Dim today
        today = date()

        ' Construct the backup file name.
        Dim exportBackupCsv
        exportBackupCsv = Left(exportCsv, Instr(exportCsv, ".")-1) & "-" & Weekday(today) & _ 
            "-" & LCase(WeekdayName(Weekday(today))) & ".csv"
        
        ' Check if the backup file already exists.
        If fso.FileExists(exportPath & exportBackupCsv) Then

            ' Get the backup file.
            Dim backupFile
            Set backupFile = fso.GetFile(exportPath & exportBackupCsv)

            ' Check if the last modified date is not the same as today's date.
            If Not(today = CDate(Left(backupFile.DateLastModified, 10))) Then

                ' Copy the CSV export.
                fso.CopyFile exportPath & exportCsv, exportPath & exportBackupCsv, True

            End If

        Else

            ' Copy the CSV export.
            fso.CopyFile exportPath & exportCsv, exportPath & exportBackupCsv, True

        End If

        ' Message confirming successful data export.
        WScript.Echo "Data export successful."
	
    End If
	
    ' Close the record set and database connection.
    recordSet.Close
    connection.Close

Else

    ' Message stating file path does not exist.
    wscript.Echo "File path does not exist."

End If

The CSV file produced contains the following data.

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