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.

Code Explanation

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.

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.

Then open the file using Open For Input method, read the entire file and store it in jsonString

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

Code Explanation

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

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.

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

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.

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.