JSON to CSV and SQL: Export API Data

You have a JSON response from an API. The data looks right, but your analyst needs it in Excel, or you need to seed a database, or you are writing docs that need a readable table. JSON is great for APIs but it is not what spreadsheets, SQL databases, or HTML pages consume natively.

Why JSON Does Not Map Cleanly to Tables

JSON supports nesting. CSV, SQL, and HTML tables do not - or they handle it awkwardly. When you have:

You have to decide: flatten address into address_city and address_zip? Drop nested objects? Create a separate table? The answer depends on where the data is going.

Converting JSON to CSV

CSV is the lingua franca of data analysis. Every spreadsheet tool reads it, pandas loads it in one line, and most BI tools accept it as an import format.

The core challenge is flattening. An array of flat objects converts cleanly:

Becomes:

Nested objects require a decision. Most converters use dot notation (address.city) or underscore notation (address_city) for flattening. For arrays within arrays, you typically get one row per item in the deepest array.

The JSON to CSV converter handles this automatically - paste your JSON, see the resulting columns, and download. It picks sensible defaults for nesting.

One practical note: if you are loading the CSV into pandas, check that numeric fields did not get quoted. "1" and 1 behave differently when you start doing aggregations.

Converting JSON to SQL INSERT Statements

Database seeding is one of the most common reasons developers need JSON-to-SQL. You have fixture data in JSON - exported from another database, pulled from an API, or written by hand - and you need to load it into a relational database for local development or a test environment.

Given this JSON:

A well-formed converter produces:

The JSON to SQL converter generates INSERT statements with proper type handling - strings get single quotes, booleans convert to TRUE/FALSE, and null values become NULL. You can paste the output directly into your database client or drop it into a migration file.

A few things to watch when using generated SQL:

The table name placeholder needs to be replaced. Type inference works well for strings, numbers, and booleans, but dates come out as strings. If you have a created_at field with a value like "2024-01-15T10:30:00Z", you will need to either cast it explicitly or adjust your DDL to accept text. For large datasets, PostgreSQL handles big INSERT batches fine, but MySQL has a max_allowed_packet limit that can trip you up if you are inserting thousands of rows at once.

JSON to HTML Tables

When you are writing documentation, generating reports, or building a quick internal tool, an HTML table is often more readable than raw JSON. A list of API endpoints, a product catalog, a permission matrix - these communicate faster as tables than nested objects.

The JSON to HTML table converter generates clean markup with , , , and tags that you can drop into any page. The output is unstyled, so it picks up whatever CSS you already have without conflicts.

If the table is going into Markdown documentation - a GitHub README, Confluence page, or Notion doc - the JSON to Markdown converter generates pipe-table format instead:

GitHub renders these natively. So does any Markdown editor that follows the CommonMark spec.

Choosing the Right Output Format

The right choice comes from what consumes the output. If a human reads it in a browser, use HTML. If a tool reads it for analysis, use CSV. If it loads into a database, use SQL. There is rarely a reason to write any of these by hand when the structure is already in your JSON.

One habit that prevents wasted effort: validate your JSON before converting. A trailing comma or misquoted key will cause most converters to fail or produce wrong output without a clear error. Run it through the JSON validator first, then export.