#Notepad++ json convert to csv how to
#Notepad++ json convert to csv full
#Notepad++ json convert to csv code
#Notepad++ json convert to csv free

I’ve explained it in detail in Import JSON to Excel post. I’m using a library VBA-JSON to parse JSON in VBA. Let’s read a JSON file, parse it and convert it to CSV file.

#Notepad++ json convert to csv how to

Now You know how to read a file and write a file.

#Notepad++ json convert to csv code

CSV File will be saved in the same as folder as the excel file in which this code is executed.

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.

Change the columnsNum according to your data.

ColumnsNum can also be made dynamic but usually, Columns are not very high in number so I mentioned the number of columns manually here.

Range('A10000').End(xlUp) method gets the last used cell in column A.

I have set the range to be dynamic so that same code works for 3 rows and also 1000 rows.

RowData = rowData + Chr(34) + Trim(cell.Offset(0, i).Value) + Chr(34)

If i = (columnsNum - 1) Then 'Check if last column Here’s the code to write data to a CSV file Sub writeToCSVfile()ĭim rng As Range, columnsNum As Integer, cell As Variant, rowData As String Let’s say I have some data as shown in Image below 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 CSV file is nothing but a text file with rows separated by line and columns separated by a comma. Now Let’s understand how to write to a CSV file.

Print method writes to a new line each time it is called. It’s self-explanatory if you read the first Code explanation. Print #fileNum, 'I am writing to a text file using VBA!' Writing to a file is pretty simple in VBA. Then open the file using Open For Input method, read the entire file and store it in jsonString.In the above code, you can just use fileNum = 1 but it might cause some issues when you are working with multiple files simultaneously.

#Notepad++ json convert to csv free

FreeFile method is used to get the next free file number which will be used as a temporary memory stack.

#Notepad++ json convert to csv full

You can just set it to full file path if required. I saved my JSON file in the same folder as the excel file so I used to get the current path. JsonString = Input(LOF(fileNum), fileNum) 'LOF Function returns the size of the file in bytes Here’s the code to read a file in VBA – Public Sub readfile()ĭim jsonData As String, jsonString As String 'body': 'est rerum tempore vitae sequi sint nihil reprehenderit dolor beatae ea dolores neque fugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis qui aperiam non debitis possimus qui neque nisi nulla' 'body': 'quia et suscipit suscipit recusandae consequuntur expedita et cum reprehenderit molestiae ut ut quas totam nostrum rerum est autem sunt rem eveniet architecto' 'title': 'sunt aut facere repellat provident occaecati excepturi optio reprehenderit',

I’ve saved a sample JSON as posts.json file which looks like image below. I’ve explained FileSystemObject in the Excel JSON article already so let’s use Open File for Input method. Combine above steps – Read JSON file > parse JSON > Write to CSV file.How to write to CSV or Text file in VBA.Check out the previous article to quickly understand How to parse JSON in Excel VBA I’m using VBA-JSON library for parsing JSON data. But if the JSON is complex or needs more customizations then I would convert it using VBA. 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. I recently received a query on how to convert JSON to CSV.