How To Convert JSON to CSV
If you've ever wanted to work with a large JSON file in a spreadsheet, BI tool, or other application that requires a tabular format, you know how painful it can be to convert JSON to CSV. In this blog we'll show you how you can convert large JSON files to CSV online with Gigasheet. Gigasheet is a no-code, cybersecurity data workbench that allows analysts to work efficiently with huge security datasets – and you can use the free version to convert JSON to CSV.
How to Convert Large JSON In Gigasheet
Good news! It's as easy as 1, 2, 3.
Login to Gigasheet. If you don't have an account you can create one for free.
Upload your .json file. You can zip a large json file to save time.
Click on the file in Your Files list to open it. Once opened you can filter, sort, split columns and more.
That's it! When you're done simply export the file as a CSV.
Here's how it works. Gigasheet accepts two possible JSON file structures:
The entire file is a JSON object
There is one JSON object per line in the file
In Information Security we frequently see item 2, one-per-line structure in exports from no-SQL databases (e.g. MongoDB, ELK, etc). In this case each JSON object becomes a row. Gigasheet handles the varying structure by creating a column for each leaf node of the nested sub-objects and sub-lists within an object. This results in a way to create a tabular representation of varying structured data where common fields are represented in the same column across rows and unique fields just show up in their own column.
Example of Structure 1: This file is one giant JSON object.
Example of Structure 2: Each line is a JSON object.
Convert Large JSON To CSV In Excel
Excel can work with JSON files to some extent, and the process varies depending on which version of Microsoft Excel you have installed. Even then you'll be limited by the max number of rows Excel can support. Since the release of MS Office 2007, the number of rows supported by Excel has been 1,048,576. Unfortunately, that's not a lot of data in today's world. (If you're looking to open a big CSV file, check out our other post.)
If your flattened JSON data is less than a million rows, and depending on your computer's performance, you may be able to import the JSON to Excel. In older versions of Excel (Office 2010 - Office 2013), you'll need to use Power Query, and in the Advanced Query editor enter the path to your file and build a query like this:
Replace C:\Users\my.json with your file name and path:
Source = Json.Document(File.Contents(“C:\Users\my.json”)), #”Converted to Table” = Record.ToTable(Source)
# “Converted to Table”
In Newer Versions of Excel you can get JSON data directly from a file using the following steps:
Select Data > Get Data > From File > From JSON. The Import Data dialog box appears.
Search the JSON file, and then select Open.
After you have the JSON data open in Excel you can save the file as a CSV.
Convert Large JSON To CSV With Free Online Tools
There are numerous online JSON to CSV converter websites we tested before building Gigasheet. They all seemed to have shortcomings. Some were overly complex and require the user define a schema. This allows for a lot of control, but it can also be daunting if you're unfamiliar with the data, or JSON in general. Perhaps the most pervasive issue is that most of these tools take JSON data via copy-and-paste or a small file upload. The files we work with are much larger than a clipboard can accommodate and most of these free sites don't support conversion of large 20mb+ files.
On the surface, Gigasheet is a web-based, billion-cell spreadsheet. Behind the scenes, it provides a high-performance big data analytics platform built specifically for information security analysts. Get started now with a free Gigasheet account.