Convert Json to csv and csv to Json in Excel VBA

I recently received a query on how to convert JSON to CSV. Usually, If the JSON file is small or has a simple structure then I would use any of the online converters to quickly convert it to CSV. But if the JSON is complex or needs more customizations then I would convert it using VBA.

I’m using VBA-JSON library for parsing JSON data. Check out the previous article to quickly understand How to parse JSON in Excel VBA

Getting started

Let’s understand this in 3 steps.

1. How to read a file in VBA
2. How to write to CSV or Text file in VBA
3. Combine above steps – Read JSON file > parse JSON > Write to CSV file

Read JSON file using VBA

JSON file can be read in 2 ways in VBA.

1. Using FileSystemObject
2. Using Open File for Input method

I’ve explained FileSystemObject in the Excel JSON article already so let’s use Open File for Input method.

I’ve saved a sample JSON as posts.json file which looks like image below. You can also download it from here

JSON looks like this –

[
{
"userId": 1,
"id": 1,
"title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
"body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
},
{
"userId": 1,
"id": 2,
"title": "qui est esse",
"body": "est rerum tempore vitae\nsequi sint nihil reprehenderit dolor beatae ea dolores neque\nfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis\nqui aperiam non debitis possimus qui neque nisi nulla"
}
]

Have a look at the code once. Here’s the code to read a file in VBA –

Public Sub readfile()
Dim jsonData As String, jsonString As String
myfile = Application.ActiveWorkbook.Path & "\posts.json"
fileNum = FreeFile
Open myfile For Input As #fileNum
jsonString = Input(LOF(fileNum), fileNum) 'LOF Function returns the size of the file in bytes
MsgBox (jsonString)
Close #fileNum
End Sub

Code Explanation

1. I saved my JSON file in the same folder as the excel file so I used Application.ActiveWorkbook.Path to get the current path. You can just set it to full file path if required.
2. FreeFile method is used to get the next free file number which will be used as a temporary memory stack. In the above code, you can just use fileNum = 1 but it might cause some issues when you are working with multiple files simultaneously.
3. Then open the file using Open For Input method, read the entire file and store it in jsonString

Write to text file in VBA

Writing to a file is pretty simple in VBA. Here’s the code

Sub writefile()
myfile = Application.ActiveWorkbook.Path & "\sample.txt"
fileNum = FreeFile
Open myfile For Output As #fileNum
Print #fileNum, "I am writing to a text file using VBA!"
Close #fileNum
End Sub

Code Explanation

It’s self-explanatory if you read the first Code explanation. Print method writes to a new line each time it is called.

Write to CSV file in VBA

Now Let’s understand how to write to a CSV file. CSV file is nothing but a text file with rows separated by line and columns separated by a comma.

We can directly export an excel file as CSV but I’m showing this example only to explain the CSV format and how to programmatically generate them so that this knowledge can be used in converting JSON to CSV

Let’s say I have some data as shown in Image below

Here’s the code to write data to a CSV file

Sub writeToCSVfile()
Dim rng As Range, columnsNum As Integer, cell As Variant, rowData As String
Set rng = Range(Sheets(1).Range("A1"), Sheets(1).Range("A10000").End(xlUp)) 'Dynamic range
columnsNum = 3
myfile = Application.ActiveWorkbook.Path & "\test.csv"
fileNum = FreeFile
Open myfile For Output As #fileNum
For Each cell In rng
rowData = ""
For i = 0 To (columnsNum - 1)
If i = (columnsNum - 1) Then 'Check if last column
rowData = rowData + Chr(34) + Trim(cell.Offset(0, i).Value) + Chr(34)
Else
rowData = rowData + Chr(34) + Trim(cell.Offset(0, i).Value) + Chr(34) + ","
End If
Next
Print #fileNum, rowData
Next
Close #fileNum
MsgBox ("complete")
End Sub

Code Explanation

1. I have set the range to be dynamic so that same code works for 3 rows and also 1000 rows. Range("A10000").End(xlUp) method gets the last used cell in column A. In this case, Range becomes A1:A3
2. ColumnsNum can also be made dynamic but usually, Columns are not very high in number so I mentioned the number of columns manually here. Change the columnsNum according to your data.
3. Then we open a file, Loop through each row, Loop through each column in the row and concatenate the columns to one row separated by a comma and print the row data and close the file after the loop is complete. CHR(34) = Double Quote
4. CSV File will be saved in the same as folder as the excel file in which this code is executed.

Convert JSON to CSV

Now You know how to read a file and write a file. Let’s read a JSON file, parse it and convert it to CSV file. I’m using a library VBA-JSON to parse JSON in VBA. I’ve explained it in detail in Import JSON to Excel post. If you haven’t read that then go have a look before you read this.

Here’s the sample JSON

Sample JSON looks like this –

[
{
"userId": 1,
"id": 1,
"title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
"body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
},
{
"userId": 1,
"id": 2,
"title": "qui est esse",
"body": "est rerum tempore vitae\nsequi sint nihil reprehenderit dolor beatae ea dolores neque\nfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis\nqui aperiam non debitis possimus qui neque nisi nulla"
}
]

Code to convert above JSON to CSV

Sub jsonToCSV()
Dim jsonData As String, JSON As Object, jsonString As String, rowData As String

jsonFile = Application.ActiveWorkbook.Path & "\posts.json" 'Input json file
jsonfileNum = FreeFile
Open jsonFile For Input As #jsonfileNum
jsonString = Input(LOF(jsonfileNum), jsonfileNum)
Set JSON = ParseJson(jsonString)
Close #jsonfileNum

csvFile = Application.ActiveWorkbook.Path & "\posts.csv" 'Output csv file
csvFileNum = FreeFile
Open csvFile For Output As #csvFileNum
Print #csvFileNum, "userId,Id,title,body" 'Print Headers

rowData = ""
For Each item In JSON
userIdField = formatField(item("userId"))
idField = formatField(item("id"))
titleField = formatField(item("title"))
bodyField = formatField(item("body"), False) 'Not adding comma to last field
rowData = userIdField + idField + titleField + bodyField
Print #csvFileNum, rowData
Next
Close #csvFileNum
End Sub

Function formatField(val, Optional addComma As Boolean = True)
If (addComma = False) Then
formatField = Chr(34) + Trim(val) + Chr(34)
Else
formatField = Chr(34) + Trim(val) + Chr(34) + ","
End If
End Function

Code Explanation

1. Firt we open the JSON file, read the data, parse it and store it to JSON variable.
2. Print headers manually. Remove this line if not required.
3. Loop through each object in JSON and get appropriate fields. Each filed has to be wrapped within quote and comma has to be added for all fields except for last field.
4. formatField function wraps the fields with quotes and comma
5. Finally print each rowData and close the file!

Note that this code works only for above JSON structure. You have to modofy the code to match your JSON structure. Read JSON tutorial here and understadn the structure.

Convert CSV to JSON in VBA

Let’s convert the above generated CSV to JSON. Here’s the code.

Public Sub csvToJsonfile()
Dim rng As Range, items As New Collection, myitem As New Dictionary
csvfileName = "posts.csv"
csvfilePath = Application.ActiveWorkbook.Path
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & csvfilePath & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
strSQL = "SELECT * FROM " & csvfileName
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst
Do
myitem("userId") = rs("userId")
myitem("id") = rs("id")
myitem("title") = rs("title")
myitem("body") = rs("body")
items.Add myitem
Set myitem = Nothing
rs.MoveNext
Loop Until rs.EOF
jsonFile = Application.ActiveWorkbook.Path & "\generated-posts.json"
jsonfileNum = FreeFile
Open jsonFile For Output As #jsonfileNum
Print #jsonfileNum, ConvertToJson(items, Whitespace:=2)
Close #jsonfileNum
rs.Close
MsgBox ("complete")
End Sub

Code explanation

I’m using ADODB recordset to read CSV file. We can also use Open as Input method and read line by line getting each row. But csv doesn’t always mean that new line = new row. Sometimes there may be line breaks in the fields. So I’m using ADODB connection.

1. First open CSV file using ADODB connection. Read ADODB.Open method’s documentation here – Open Method (ADO Recordset)
2. Loop through each row and add the fields to a dictionary and push that dictionary into items collection and convert it to JSON using ConvertToJson method
3. Finally write the JSON to a new file using Open For Output method

Wrapping up

Read JSON tutorial for beginners, understand JSON and adapt the code for your requirement!

If you have and questions or feedback, comment below.

• Author
• Recent Posts

Ranjith kumar

A CA- by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing.

Latest posts by Ranjith kumar (see all)

• Flutter lookup failed in @fields error (solved) - July 14, 2023
• Free open source alternative to Notion along with AI - July 13, 2023
• Threads API for developers for programmatic access - July 12, 2023