Yesterday, I discussed reading XML content in my Read XML with VBA and PowerShell article. Today, I thought I’d see if the same was true with working with JSON data.
Like with my XML article, this article focuses on simply reading/retrieving data from either a web source or a file.
If you’ve already read the XML article, you’ll see that the processing, once you manage to load the JSON content is basically all the same. So it is refreshing to see that you can, for a very large part, handle all these different data formats with the exact same techniques and code. Learn once, apply to everything!
For this article I am using a JSON Sample taken from JSONPlaceholder – Free Fake REST API, specifically the users sample.
Also, as with any of my PowerShell articles, the base functions like: PS_Execute and PS_GetOutput come from my first article entitled: VBA – Run PowerShell Command.
Web Content vs File Content
The first thing to understand is that working with web JSON is initiated differently then when working with a JSON file from a PC or Server.
Web JSON
To load JSON coming from the web you would use a PowerShell command like:
$response = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/users' -UseBasicParsing
File JSON
To load JSON coming from a file on your PC/server you would use a PowerShell command like:
$file = 'C:\Users\Dev\Documents\users.json' $response = (Get-Content $file -Raw)
Now that this is out of the way, the remainder of the reading/processing/… is all the same regards of the JSON source.
Extracting Information From JSON
The basic PowerShell command to retrieve data from JSON is something along the lines of:
$response = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/users' -UseBasicParsing
$combined = $response | ConvertFrom-Json | ForEach-Object {$_.name}
$result = $combined -join '~~'
Write-Host $result
with the above sample code we would get returned a listing of user names separated by a ~~ delimiter.
Now that we have the basic PowerShell command, we can write a simple VBA wrapper to run the PowerShell command, retrieve the output (a delimited string) and then convert it into a VBA array.
From the Web
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/users' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response | ForEach-Object {$_." & sElement & "}" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
From a Local File
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$file = 'C:\Users\Dev\Documents\users.json'" & vbCrLf & _
"$response = (Get-Content $file -Raw) | ConvertFrom-Json" & vbCrLf & _
"$combined = $response | ForEach-Object {$_." & sElement & "}" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
Regardless of whether you are reading JSON from a web server or a file, we can test both versions out by simply doing:
Public Sub ListItems()
'Output the list of returned values to VBA immediate window
Dim aItems() As String
Dim vItem As Variant
Dim i As Long
aItems() = ReadJSON("email")
For Each vItem In aItems
i = i + 1
Debug.Print i, vItem
Next
End Sub
which will then return a list like
1 Sincere@april.biz 2 Shanna@melissa.tv 3 Nathan@yesenia.net 4 Julianne.OConner@kory.org 5 Lucio_Hettinger@annie.ca 6 Karley_Dach@jasper.info 7 Telly.Hoeger@billy.biz 8 Sherwood@rosamond.me 9 Chaim_McDermott@dana.io 10 Rey.Padberg@karina.biz
Unique Values
Let us switch for a moment and use the ToDo sample JSON. For starters, let us simply extract a lists of userids.
We can employ the exact same basic code as shown above.
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response | ForEach-Object {$_." & sElement & "}" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
Public Sub ListItems()
Dim aItems() As String
Dim vItem As Variant
Dim i As Long
aItems() = ReadJSON("userid")
For Each vItem In aItems
i = i + 1
Debug.Print i, vItem
Next
End Sub
which when run will return a long list (200 entries) of the various userids associated with the various ToDos. The issue however is that we have countless duplicates and wouldn’t it be nice if we could get a simple unique listing. Thankfully, PowerShell has the Select-object -unique command for just such a need. We need only append our PowerShell command to:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response | ForEach-Object {$_." & sElement & "} | Select-object -unique" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
and now when run, we get a much more manageable listing of userids
1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10
Sorting Values
The only issue with the above is the ordering of the returned values; that is to say we have no guarantee as to the order the data is returned to use and should ever rely on it being in a specific order.
Once again, PowerShell is already prepared to help us with the Sort-Object command for just such a need.
Appending our PowerShell once more, we get:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response | ForEach-Object {$_." & sElement & "} | Select-object -unique | Sort-Object" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
and this will ensure that the returned values are in fact sorted in Ascending order, resulting in
1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10
Now, if I wanted a Descending ordered list I can simply add the -Descending parameter to Sort-Object, thus the function becomes:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response | ForEach-Object {$_." & sElement & "} | Select-object -unique | Sort-Object -Descending" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
which will in turn result in us getting back the following output:
1 10 2 9 3 8 4 7 5 6 6 5 7 4 8 3 9 2 10 1
Filtering JSON Data
Up until now, we have been extracting completing listing from the supplied JSON stream, but what if we wanted to filter the data to only get specific data? Nothing could be easier as PowerShell has everything you need to apply filters.
Single Criterion
So, working the ToDo list some more, let’s extract a list of ToDos for userid 5. Just like in SQL, in PowerShell we need to apply a Where clause. So we would alter our basic command to something like:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response| Where{$_.userid -eq '5'} | ForEach-Object {$_." & sElement & "}" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
and thus will it will return an output of
1 suscipit qui totam 2 voluptates eum voluptas et dicta 3 quidem at rerum quis ex aut sit quam 4 sunt veritatis ut voluptate 5 et quia ad iste a 6 incidunt ut saepe autem 7 laudantium quae eligendi consequatur quia et vero autem 8 vitae aut excepturi laboriosam sint aliquam et et accusantium 9 sequi ut omnis et 10 molestiae nisi accusantium tenetur dolorem et 11 nulla quis consequatur saepe qui id expedita 12 in omnis laboriosam 13 odio iure consequatur molestiae quibusdam necessitatibus quia sint 14 facilis modi saepe mollitia 15 vel nihil et molestiae iusto assumenda nemo quo ut 16 nobis suscipit ducimus enim asperiores voluptas 17 dolorum laboriosam eos qui iure aliquam 18 debitis accusantium ut quo facilis nihil quis sapiente necessitatibus 19 neque voluptates ratione 20 excepturi a et neque qui expedita vel voluptate
Multiple Criteria
Example 1 – AND
Let’s explore applying multiple criterion. Let’s us not only look to retrieve all the ToDo items for userid 5, but let’s restrict the returned items to only include those which have not been completed yet, so with completed = false.
To do so, we would alter our function to be like:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response| Where{$_.userid -eq '5' -and $_.completed -eq 'false'} | ForEach-Object {$_." & sElement & "}" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
and now, the function returns the following 12 items, rather than the original 20
1 suscipit qui totam 2 quidem at rerum quis ex aut sit quam 3 et quia ad iste a 4 incidunt ut saepe autem 5 laudantium quae eligendi consequatur quia et vero autem 6 sequi ut omnis et 7 molestiae nisi accusantium tenetur dolorem et 8 nulla quis consequatur saepe qui id expedita 9 in omnis laboriosam 10 odio iure consequatur molestiae quibusdam necessitatibus quia sint 11 vel nihil et molestiae iusto assumenda nemo quo ut 12 debitis accusantium ut quo facilis nihil quis sapiente necessitatibus
Example 2 – Or, In, Match
What about OR, Like, …? No worries there either as PowerShell has all the standard criteria and even some new ones.
For this example, let us retrieve a list of ToDo items for users 5 and 7. Be careful here, even though we say ‘and’ in English, in SQL this is an OR case. So our function becomes
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response| Where{($_.userid -eq '5' -or $_.userid -eq '7')} | ForEach-Object {$_." & sElement & "}" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
and will result in an output of
1 suscipit qui totam 2 voluptates eum voluptas et dicta 3 quidem at rerum quis ex aut sit quam 4 sunt veritatis ut voluptate 5 et quia ad iste a 6 incidunt ut saepe autem 7 laudantium quae eligendi consequatur quia et vero autem 8 vitae aut excepturi laboriosam sint aliquam et et accusantium 9 sequi ut omnis et 10 molestiae nisi accusantium tenetur dolorem et 11 nulla quis consequatur saepe qui id expedita 12 in omnis laboriosam 13 odio iure consequatur molestiae quibusdam necessitatibus quia sint 14 facilis modi saepe mollitia 15 vel nihil et molestiae iusto assumenda nemo quo ut 16 nobis suscipit ducimus enim asperiores voluptas 17 dolorum laboriosam eos qui iure aliquam 18 debitis accusantium ut quo facilis nihil quis sapiente necessitatibus 19 neque voluptates ratione 20 excepturi a et neque qui expedita vel voluptate 21 inventore aut nihil minima laudantium hic qui omnis 22 provident aut nobis culpa 23 esse et quis iste est earum aut impedit 24 qui consectetur id 25 aut quasi autem iste tempore illum possimus 26 ut asperiores perspiciatis veniam ipsum rerum saepe 27 voluptatem libero consectetur rerum ut 28 eius omnis est qui voluptatem autem 29 rerum culpa quis harum 30 nulla aliquid eveniet harum laborum libero alias ut unde 31 qui ea incidunt quis 32 qui molestiae voluptatibus velit iure harum quisquam 33 et labore eos enim rerum consequatur sunt 34 molestiae doloribus et laborum quod ea 35 facere ipsa nam eum voluptates reiciendis vero qui 36 asperiores illo tempora fuga sed ut quasi adipisci 37 qui sit non 38 placeat minima consequatur rem qui ut 39 consequatur doloribus id possimus voluptas a voluptatem 40 aut consectetur in blanditiis deserunt quia sed laboriosam
A Little More Complexe
Taking things further, just to illustrate things a little more, you can continue to build upon the above to filter and only return those items that are completed for userid 5 and 7. Thus, the function then becomes:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response| Where{($_.userid -eq '5' -or $_.userid -eq '7') -and $_.completed -eq 'true'} | ForEach-Object {$_." & sElement & "}" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
outputting the following results
1 suscipit qui totam 2 quidem at rerum quis ex aut sit quam 3 et quia ad iste a 4 incidunt ut saepe autem 5 laudantium quae eligendi consequatur quia et vero autem 6 sequi ut omnis et 7 molestiae nisi accusantium tenetur dolorem et 8 nulla quis consequatur saepe qui id expedita 9 in omnis laboriosam 10 odio iure consequatur molestiae quibusdam necessitatibus quia sint 11 vel nihil et molestiae iusto assumenda nemo quo ut 12 debitis accusantium ut quo facilis nihil quis sapiente necessitatibus 13 inventore aut nihil minima laudantium hic qui omnis 14 provident aut nobis culpa 15 ut asperiores perspiciatis veniam ipsum rerum saepe 16 voluptatem libero consectetur rerum ut 17 nulla aliquid eveniet harum laborum libero alias ut unde 18 qui molestiae voluptatibus velit iure harum quisquam 19 et labore eos enim rerum consequatur sunt 20 placeat minima consequatur rem qui ut 21 aut consectetur in blanditiis deserunt quia sed laboriosam
Let’s Sort It As Well
Finally, to give a complete example, let’s take the same example as above, but sort the output (as we saw earlier). To do so we would alter the function to be like:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$combined = $response| Where{($_.userid -eq '5' -or $_.userid -eq '7') -and $_.completed -eq 'true'} | ForEach-Object {$_." & sElement & "} | Sort-Object" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
and in turn it returns:
1 aut consectetur in blanditiis deserunt quia sed laboriosam 2 debitis accusantium ut quo facilis nihil quis sapiente necessitatibus 3 et labore eos enim rerum consequatur sunt 4 et quia ad iste a 5 in omnis laboriosam 6 incidunt ut saepe autem 7 inventore aut nihil minima laudantium hic qui omnis 8 laudantium quae eligendi consequatur quia et vero autem 9 molestiae nisi accusantium tenetur dolorem et 10 nulla aliquid eveniet harum laborum libero alias ut unde 11 nulla quis consequatur saepe qui id expedita 12 odio iure consequatur molestiae quibusdam necessitatibus quia sint 13 placeat minima consequatur rem qui ut 14 provident aut nobis culpa 15 qui molestiae voluptatibus velit iure harum quisquam 16 quidem at rerum quis ex aut sit quam 17 sequi ut omnis et 18 suscipit qui totam 19 ut asperiores perspiciatis veniam ipsum rerum saepe 20 vel nihil et molestiae iusto assumenda nemo quo ut 21 voluptatem libero consectetur rerum ut
Let’s Really Push Things To The Extreme
I thought I’d end with a more complexe example to really try to illustrate how almost anything is possible.
Let us return a list of ToDo titles for userid 5 and 7 that have not been completed and then let’s order the returned list by userid in ascending order and then by title in descending order.
Our function would then become something like:
Function ReadJSON(sElement As String) As Variant
Dim sCmd As String
Dim sItems As String
sCmd = "$request = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing" & vbCrLf & _
"$response = $request | ConvertFrom-Json" & vbCrLf & _
"$response = $response | Where{ ($_.userid -eq '5' -or $_.userid -eq '7') -and ($_.completed -eq 'false') } | Sort-Object -property @{Expression='userid';Descending=$false}, @{Expression='title';Descending=$true} " & vbCrLf & _
"$combined = $response | ForEach-Object { $_.title } " & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
ReadJSON = Split(sItems, "~~")
End Function
Do notice that I needed to use a more complicated version of the Sort-Object command to be able to specify the sort object for each element.
It returns the following:
1 vel nihil et molestiae iusto assumenda nemo quo ut 2 suscipit qui totam 3 sequi ut omnis et 4 quidem at rerum quis ex aut sit quam 5 odio iure consequatur molestiae quibusdam necessitatibus quia sint 6 nulla quis consequatur saepe qui id expedita 7 molestiae nisi accusantium tenetur dolorem et 8 laudantium quae eligendi consequatur quia et vero autem 9 incidunt ut saepe autem 10 in omnis laboriosam 11 et quia ad iste a 12 debitis accusantium ut quo facilis nihil quis sapiente necessitatibus 13 voluptatem libero consectetur rerum ut 14 ut asperiores perspiciatis veniam ipsum rerum saepe 15 qui molestiae voluptatibus velit iure harum quisquam 16 provident aut nobis culpa 17 placeat minima consequatur rem qui ut 18 nulla aliquid eveniet harum laborum libero alias ut unde 19 inventore aut nihil minima laudantium hic qui omnis 20 et labore eos enim rerum consequatur sunt 21 aut consectetur in blanditiis deserunt quia sed laboriosam
Importing a Complete File Into a Table
Today, I thought I’d show how we could take the above knowledge and import the ToDos JSON data into an Access database table.
First things first, we need to create a table to house this information. Below is a very basic DLL example, simply run it in a query editor and the todos table will be created.
CREATE TABLE todos (
userId Long,
id Long,
title Text(255),
Completed YesNo
);
Now, that our table has been created we are ready to proceed with importing the data.
Read The JSON Data Multiple Times
With what we alreay saw previously, one could simply make multiple reads of the JSON data and process each element separately and we could do something like:
Public Sub ImportJSON_Ineeficient()
Dim auserId() As String
Dim aid() As String
Dim atitle() As String
Dim acompleted() As String
Dim vItem As Variant
Dim i As Long
auserId() = ReadJSON("userId")
aid() = ReadJSON("id")
atitle() = ReadJSON("title")
acompleted() = ReadJSON("completed")
For Each vItem In aid
CurrentDb.Execute "INSERT INTO todos (userId, id, title, completed) " & _
"VALUES(" & auserId(i) & ", " & aid(i) & ", '" & _
atitle(i) & "', " & acompleted(i) & ")", dbFailOnError
i = i + 1
Next
End Sub
This most definitely works, but is certainly not the most efficient way to do things. For a one time thing, sure, but if this is something you’ll need to do regularly, perhaps we could improve upon it.
Read The JSON Data Only Once
So the idea with the function below was to only read the JSON data once and concatenate each ‘row’ of data together with a delimiter that I could then split on.
Here is the final product.
Function ImportJSON() As Variant
Dim sCmd As String
Dim sPSCmd As String
Dim sItems As String
Dim aJSON As Variant
Dim aElement As Variant
Dim vItem As Variant
Dim i As Long
Dim db As DAO.Database
Set db = CurrentDb
sCmd = "$response = Invoke-WebRequest -Uri 'https://jsonplaceholder.typicode.com/todos' -UseBasicParsing | ConvertFrom-Json" & vbCrLf & _
"$combined = $response | ForEach-Object { """"""$($_.userId)--$($_.id)--$($_.title)--$($_.completed)"""""" }" & vbCrLf & _
"$result = $combined -join '~~'"
sItems = PS_GetOutput(sCmd)
aJSON = Split(sItems, "~~")
For Each vItem In aJSON
aElement = Split(aJSON(i), "--")
db.Execute "INSERT INTO todos (userId, id, title, completed) " & _
"VALUES(" & aElement(0) & ", " & aElement(1) & ", '" & _
aElement(2) & "', " & aElement(3) & ")", dbFailOnError
i = i + 1
Next
Set db = Nothing
End Function
If you review the code, you’ll notice the main difference being in the ForEach-Object
ForEach-Object { """"""$($_.userId)--$($_.id)--$($_.title)--$($_.completed)"""""" }
this concatenates each element with a — delimiter.
Once that is done, then I simply loop over the items, as with the previous technique, but this time I can break the array value into individual elements, so split it again. So in one reading of the JSON data I commit it all.
Also, the above reads the entire dataset, but as illustrated above in the various examples, you can apply filters, … and INSERT only what you are after.
Double Quotes Your Worst Enemy!
I’d like to point out one particularly here. The actual PowerShell script ForEach-Object line is
ForEach { "$($_.userId),$($_.id),$($_.title),$($_.completed)"}
which converted over to VBA would normally become
"ForEach { ""$($_.userId),$($_.id),$($_.title),$($_.completed)""}"
yet this does not work?! The working version, after hours of trial and error, and hair pulling is
ForEach-Object { """"""$($_.userId)--$($_.id)--$($_.title)--$($_.completed)"""""" }
and funnily enough, when output to the immediate window produces
ForEach-Object { """$($_.userId)--$($_.id)--$($_.title)--$($_.completed)""" }
which does not work directly in PowerShell, yet work when run through WScript.Shell?! All that to say single quotes are always best, and if you get into commands requiring double quotes it would seem you need to quadruple them up.
PowerShell And JSON
As with XML, PowerShell has shown itself to be very powerful and flexible with working with JSON! You simply need the basic starting point and then build upon it with commands like
- Where
- Sort-Object
- Select-Object -unique
until you get exactly what it is you were looking for.
I truly hope these article are helpful to get you going with accessing and manipulating your data, regardless of the format it comes to you in!
A Few Resources on the Subject
- VBA – Run PowerShell Command
- ForEach-Object (Microsoft.PowerShell.Core) – PowerShell | Microsoft Docs
- Select-Object (Microsoft.PowerShell.Utility) – PowerShell | Microsoft Docs
- Sort-Object (Microsoft.PowerShell.Utility) – PowerShell | Microsoft Docs
- about Comparison Operators – PowerShell | Microsoft Docs
- JSON
- JSONPlaceholder – Free Fake REST API
- Read XML with VBA and PowerShell