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"