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"
    }
  ]
}