VBScript and SQL Server – Exporting Data (JSON)
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. JSON or JavaScript Object Notation files are an alternative file format to CSV, that can sometimes be used 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 JSON 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 JSON 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 JSON file is created. The opening curly brace and person element is then added to the file, followed by the data rows in JSON format. The ‘chr’ function is used to indent the text, where appropriate, using a tab, as well as incorporate double quotes, when needed. Finally, the person element and opening curly brace is closed.
A rolling seven day backup is also included. This makes a copy of the JSON 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.json', 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 exportJson exportPath = "C:\demo\" exportJson = "personexport.json" ' 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 JSON file. Dim jsonFile Set jsonFile = fso.CreateTextFile(exportPath & exportJson, True) ' Add the opening curly brace to the file. jsonFile.WriteLine("{") ' Open the person element. jsonFile.WriteLine(chr(9) & chr(34) & "person" & chr(34) & ": [") ' Process the rows of data. Do While Not recordSet.EOF ' Add opening curly brace for record. jsonFile.WriteLine(chr(9) & chr(9) & "{") ' Add the id. jsonFile.WriteLine(chr(9) & chr(9) & chr(9) & chr(34) & _ "id" & chr(34) & ": " & _ Cstr(recordSet("id")) & ",") ' Add firstname. jsonFile.WriteLine(chr(9) & chr(9) & chr(9) & chr(34) & _ "firstname" & chr(34) & ": " & _ chr(34) & recordSet("firstname") & chr(34) & ",") ' Add the lastname. jsonFile.WriteLine(chr(9) & chr(9) & chr(9) & chr(34) & _ "lastname" & chr(34) & ": " & _ chr(34) & recordSet("lastname") & chr(34) & ",") ' Add the title. jsonFile.WriteLine(chr(9) & chr(9) & chr(9) & chr(34) & _ "title" & chr(34) & ": " & _ chr(34) & recordSet("title") & chr(34) & ",") ' Add the dob. jsonFile.WriteLine(chr(9) & chr(9) & chr(9) & chr(34) & _ "dob" & chr(34) & ": " & _ chr(34) & recordSet("dob") & chr(34)) ' Move to the next record. recordSet.MoveNext ' Close curly brace for previous record. ' Include comma if not last record. If recordSet.EOF Then jsonFile.WriteLine(chr(9) & chr(9) & "}") Else jsonFile.WriteLine(chr(9) & chr(9) & "},") End If Loop ' Close the person element. jsonFile.WriteLine(chr(9) & "]") ' Close the opening curly brace. jsonFile.WriteLine("}") ' Close the JSON file. jsonFile.Close ' Today's date. Dim today today = date() ' Construct the backup file name. Dim exportBackupJson exportBackupJson = Left(exportJson, Instr(exportJson, ".")-1) & "-" & Weekday(today) & _ "-" & LCase(WeekdayName(Weekday(today))) & ".json" ' Check if the backup file already exists. If fso.FileExists(exportPath & exportBackupJson) Then ' Get the backup file. Dim backupFile Set backupFile = fso.GetFile(exportPath & exportBackupJson) ' 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 JSON export. fso.CopyFile exportPath & exportJson, exportPath & exportBackupJson, True End If Else ' Copy the JSON export. fso.CopyFile exportPath & exportJson, exportPath & exportBackupJson, 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 JSON file produced contains the following data.
{ "person": [ { "id": 1, "firstname": "Bob", "lastname": "Smith", "title": "Mr", "dob": "1980-01-20T00:00:00" }, { "id": 3, "firstname": "Fred", "lastname": "Bloggs", "title": "Mr", "dob": "1975-05-07T00:00:00" }, { "id": 4, "firstname": "Alan", "lastname": "White", "title": "Mr", "dob": "1989-03-20T00:00:00" }, { "id": 5, "firstname": "Fiona", "lastname": "Bloggs", "title": "Mrs", "dob": "1985-05-19T00:00:00" }, { "id": 6, "firstname": "Zoe", "lastname": "Davis", "title": "Miss", "dob": "1979-07-11T00:00:00" }, { "id": 7, "firstname": "Tom", "lastname": "Ingram", "title": "Mr", "dob": "1971-10-04T00:00:00" }, { "id": 8, "firstname": "Karen", "lastname": "Thomas", "title": "Mrs", "dob": "1969-03-08T00:00:00" }, { "id": 9, "firstname": "Samantha", "lastname": "Yates", "title": "Miss", "dob": "1995-08-27T00:00:00" } ] }