horizontal lines
Gigasheet Primary logo
  • Jason Hines

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.


For those uninitiated, JSON stands for JavaScript Object Notation and is a lightweight data-interchange format. It's essentially a "text format that is completely [programming] language independent but uses conventions that are familiar to programmers." If you're not a programmer, it can be a bit intimidating to work with large files of JSON data. JSON is often used to send data between computers and databases and most modern programming languages include code to generate and parse JSON data. JSON is an ideal format for larger data sets that have a hierarchical structured relationship, but this structure also makes it difficult to analyze in tools that expect data to be in rows and columns.


How to Convert Large JSON In Gigasheet


Good news! It's as easy as 1, 2, 3.

  1. Login to Gigasheet. If you don't have an account you can create one for free.

  2. Upload your .json file. You can zip a large json file to save time.

  3. 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.


Converting JSON to CSV
Converting JSON to CSV

Here's how it works. Gigasheet accepts two possible JSON file structures:

  1. The entire file is a JSON object

  2. 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.

JSON Object Structure

Example of Structure 2: Each line is a JSON object.

Each line is a JSON object structure

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)

in

    # “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.




About Gigasheet


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.

Recent Posts

Explore sample data from IMDB

NO SIGN IN REQUIRED