DEV Community: Avinash Verma

How to Get JSON into Excel: 3 Reliable Ways (CSV, Power Query, and a Converter)

Avinash Verma — Tue, 14 Jul 2026 05:38:00 +0000

Excel can't open a .json file and make sense of it — you get one cell of raw text. Here are three reliable ways to get JSON into a proper Excel grid, from quickest to most powerful.

Option 1: JSON → CSV → Excel (fastest)

For a flat array of objects — the shape most API responses take — convert to CSV, then open in Excel.

[
  {"id": 1, "name": "Ada", "role": "admin"},
  {"id": 2, "name": "Alan", "role": "editor"}
]

becomes

id,name,role
1,Ada,admin
2,Alan,editor

Save as .csv, double-click, done. jsonviewertool.com/json-to-csv does the conversion in the browser (client-side), or /json-to-excel hands you the sheet directly.

Option 2: Power Query (for nested JSON)

If your JSON has nested objects or arrays, Excel's built-in Power Query handles it:

Data → Get Data → From File → From JSON
Pick your file; the Power Query editor opens.
Click To Table, then use the expand button on any column to flatten nested fields.
Close & Load.

This keeps a live connection — refresh re-imports the file. Great for recurring reports, overkill for a one-off.

Option 3: A script (for automation)

If it's a pipeline, do it in code:

import pandas as pd
pd.read_json("data.json").to_excel("data.xlsx", index=False)

pandas flattens a flat array cleanly; use pd.json_normalize() for nested structures.

Which to use

One-off, flat data → JSON→CSV, open in Excel. Seconds.
Nested data, recurring → Power Query.
Automated pipeline → pandas.

The trap with all three: Excel loves to "helpfully" reformat values — long numbers become scientific notation, leading zeros vanish, and date-like strings become dates. Import ID and code columns as Text to keep them intact.

Turn JSON into SQL INSERT Statements (without writing a script)

Avinash Verma — Mon, 13 Jul 2026 05:23:00 +0000

You've got a JSON array and you need it as rows in a database. Here's how to turn JSON into SQL INSERT statements — the quick path, the scripted path, and the gotchas that corrupt your data.

The shape that maps cleanly

A flat array of objects maps 1:1 to table rows:

[
  {"id": 1, "email": "ada@x.com", "active": true},
  {"id": 2, "email": "alan@x.com", "active": false}
]

becomes

INSERT INTO users (id, email, active) VALUES
  (1, 'ada@x.com', true),
  (2, 'alan@x.com', false);

Keys become columns; each object becomes a row. jsonviewertool.com/json-to-sql generates this in the browser (client-side) — paste JSON, get the INSERT.

Doing it in code

For a pipeline, generate the SQL yourself so you control quoting and batching — but use parameterized queries for anything touching real input. String-built INSERTs are fine for a one-time seed, dangerous for user data.

The gotchas that corrupt data

Quotes in strings. O'Brien breaks a naive '...' wrap. Escape single quotes by doubling them (''), or parameterize.
NULL vs empty string. JSON null should become SQL NULL, not the text 'null' or ''.
Booleans. Postgres takes true/false; MySQL wants 1/0.
Nested objects/arrays. A JSON value that's itself an object doesn't fit a scalar column — store it in a JSON/JSONB column or flatten it first.
Big batches. Thousands of rows in one statement can hit packet limits; chunk into batches of ~500.

Rule of thumb

For a one-off seed or migration, generate the INSERTs and eyeball the output. For anything recurring or user-facing, load via a parameterized query in your app's DB driver — never hand-built SQL strings.

Generate TypeScript Types from JSON (and where the auto-generators trip up)

Avinash Verma — Sun, 12 Jul 2026 06:11:00 +0000

You've got a JSON API response and you want TypeScript interfaces for it. Here's how to generate them fast — and where the auto-generators quietly get it wrong.

The fast path

Paste your JSON, get interfaces:

{ "id": 1, "name": "Ada", "roles": ["admin"], "profile": { "active": true } }

→

interface Root {
  id: number;
  name: string;
  roles: string[];
  profile: Profile;
}
interface Profile {
  active: boolean;
}

jsonviewertool.com/json-to-typescript does this in the browser (client-side), nesting objects into their own interfaces.

Where generators trip up

A generator only sees the ONE sample you give it, which causes predictable gaps:

Nullable fields. If your sample has "avatar": null, the generator infers null — but the real type is probably string | null. Feed it a populated sample, or fix it by hand.
Empty arrays. "tags": [] infers any[] — the element type is unknowable from an empty array.
Optional fields. A field missing from your sample won't appear at all. If the API sometimes omits middleName, mark it middleName?: string.
Unions. A status that's "active" in your sample becomes string, not the literal union "active" | "banned" | "pending". Narrow it manually for the safety.
Numbers that are really enums or IDs. "currency": 840 types as number; you may want an enum or branded type.

When to use a schema instead

If the JSON has a JSON Schema or OpenAPI spec, generate types from that (json-schema-to-typescript, openapi-typescript) — it encodes nullability, optionality, and unions the raw sample can't. Sample-based generation is for quick throwaway typing; schema-based is for anything you'll maintain.

Rule of thumb

Generate from a sample to skip the boilerplate, then read every field — the generator gives you a draft, not a contract. Nullability and optional fields are where the runtime bugs hide.

Generate Go Structs from JSON (with the right tags and types)

Avinash Verma — Sat, 11 Jul 2026 04:56:00 +0000

Go's encoding/json needs a struct to unmarshal into. Here's how to turn a JSON payload into a correct Go struct — tags, types, and the gotchas that cause silent zero-values.

The fast path

{ "user_id": 1, "full_name": "Ada", "is_active": true }

→

type Root struct {
    UserID   int    `json:"user_id"`
    FullName string `json:"full_name"`
    IsActive bool   `json:"is_active"`
}

jsonviewertool.com/json-to-go generates this in the browser (client-side) — exported field names, snake_case preserved in the json: tag.

The gotchas

Exported fields + json tags are mandatory. encoding/json only marshals exported (capitalized) fields, and your JSON keys are usually snake_case — so every field needs a json:"key" tag or the data silently doesn't map (zero values everywhere).
Numbers default to float64. Decoding an arbitrary number into interface{} gives float64. In a typed struct, pick int or int64 for IDs to avoid precision surprises with large values.
Nullable fields → pointers. A JSON null into an int gives 0, indistinguishable from a real zero. Use *int (or sql.NullInt64) when you must tell "absent" from "zero".
omitempty for optional output. Add ,omitempty to a tag to skip zero-value fields when marshalling back out.
Unknown/dynamic keys → map[string]T. If keys vary (a dictionary keyed by ID), model it as a map, not a struct.

For a stable API

Generate the struct once, then commit it and hand-tune the numeric types and pointers. Generators nail the shape and tags; they can't know your nullability or precision needs.

JSON to Python: dataclass, TypedDict, or Pydantic?

Avinash Verma — Fri, 10 Jul 2026 05:42:00 +0000

You've got JSON and you want typed Python objects instead of raw dicts. There are three good targets — dataclass, TypedDict, and Pydantic — and they solve different problems. Here's how to choose.

The shape

{ "id": 1, "name": "Ada", "active": true }

dataclass — plain typed objects

from dataclasses import dataclass

@dataclass
class User:
    id: int
    name: str
    active: bool

Attribute access (user.name), no validation — you still parse the JSON yourself: User(**json.loads(s)). Good for internal data you trust.

TypedDict — a dict with a known shape

from typing import TypedDict

class User(TypedDict):
    id: int
    name: str
    active: bool

Still a plain dict at runtime (user["name"]) — the types are for the type-checker only, no runtime validation. Great for typing data you keep as dicts.

Pydantic — validation at the boundary

from pydantic import BaseModel

class User(BaseModel):
    id: int
    name: str
    active: bool

user = User.model_validate_json(s)  # raises on bad data

Parses and validates — wrong types raise instead of corrupting silently. The right choice for untrusted input (API bodies, config, webhooks).

jsonviewertool.com/json-to-python generates the class for whichever style you pick, from a JSON sample.

How to choose

Trusted internal data, want objects → dataclass.
Keep it as dicts, just want type hints → TypedDict.
Data crossing a trust boundary → Pydantic (validate once, trust after).

The same caveat applies everywhere: a generator infers types from one example, so it can't see nullability, optional fields, or unions. Read the output before you ship it.

How to Convert YAML to JSON in JavaScript and Python (the type traps to avoid)

Avinash Verma — Thu, 09 Jul 2026 05:07:00 +0000

YAML and JSON describe the same data, so converting sounds trivial — until an unquoted yes becomes true and a ZIP code loses its leading zero. Here's how to convert YAML to JSON correctly in JavaScript and Python, and the type traps to watch for.

JavaScript (js-yaml)

import yaml from 'js-yaml';
const data = yaml.load(yamlString);
const json = JSON.stringify(data, null, 2);

Python (PyYAML)

import yaml, json
data = yaml.safe_load(yaml_string)
print(json.dumps(data, indent=2))

Parse YAML into a native object, then re-serialize as JSON. That's the whole conversion — the bugs live in the details.

Trap 1: type coercion

YAML reads unquoted scalars aggressively:

yes, no, on, off become booleans
1.10 becomes the number 1.1 (trailing zero gone)
007 becomes the integer 7 (leading zeros gone)
2024-01-01 becomes a date, not a string

If a value must stay a string — version numbers, ZIP codes, country codes — quote it: zip: "07030".

Trap 2: use the safe loader

Python's yaml.load() can construct arbitrary objects and is an RCE risk on untrusted input. Always use yaml.safe_load(). (js-yaml's default load is safe as of v4.)

Trap 3: multi-document files

A file with --- separators holds multiple documents. safe_load reads only the first — use safe_load_all() in Python or yaml.loadAll() in JS, then emit a JSON array.

Trap 4: anchors and aliases

YAML's &anchor / *alias references expand at parse time, so the JSON output inlines the duplicated data. Expected, but it can balloon the payload.

When you just need it converted once

For a one-off — a CI config, a Kubernetes manifest, a docker-compose.yml — you don't need a script. jsonviewertool.com/yaml-to-json converts it in the browser, 100% client-side, and the reverse JSON to YAML is there too.

Takeaway

The conversion is one function call; the bugs come from YAML's helpful-but-surprising coercion. Quote anything that must stay a string, use the safe loader, and handle multi-document files explicitly.

Fixing broken JSON: why "Unexpected token" usually isn't a JSON problem

Avinash Verma — Wed, 08 Jul 2026 11:41:29 +0000

You call JSON.parse() (or res.json()) and get:

SyntaxError: Unexpected token '<', "<!DOCTYPE "... is not valid JSON

Before you hunt for a missing comma — most of these errors aren't broken JSON at all.

"Unexpected token <" → you got HTML, not JSON

The #1 cause, and it has nothing to do with your JSON. The server returned an HTML page (a 404, a login redirect, a Cloudflare challenge) and your code parsed it as JSON.

const res = await fetch(url);
const text = await res.text();
console.log(text.slice(0, 200)); // <!DOCTYPE html>... there is your answer

Now it's a request problem (wrong URL, missing auth, expired token) — fix the request, not the parser.

"Unexpected token o at position 1" → you double-parsed

The o is from [object Object]. You parsed something already parsed — fetch().then(r => r.json()) parses for you. Don't do it twice.

Actually-broken JSON

When it is malformed, it's almost always: trailing commas ({"a":1,}), single quotes, unquoted keys ({a:1}), comments (JSON has none), Python values (True/None), or missing brackets from a truncated copy-paste.

Fix it fast

Find the exact line with a validator; auto-fix the lenient cases with JSON5.parse() or a repair tool. For a quick browser fix I use https://jsonviewertool.com/json-repair (rewrites trailing commas, single quotes, unquoted keys to strict JSON) and https://jsonviewertool.com/json-validator to pinpoint the error line — both client-side.

10-second triage

Mentions < → HTML, not JSON. Fix the request.
Mentions o at position 1 → you double-parsed.
Anything else → malformed → validate + repair.

What Is JSON? Complete Beginner's Guide (With Examples)

Avinash Verma — Mon, 08 Jun 2026 17:55:31 +0000

JSON is the most popular data format used in APIs, configuration files and modern web apps. In this guide, you'll learn what JSON is, how it works, and how to view and format JSON easily.

What is JSON?

JSON stands for JavaScript Object Notation. It's a lightweight data format that is easy for humans to read and easy for machines to parse. JSON is used everywhere: REST APIs, configuration files, logs, browser storage, mobile apps and more.

A simple JSON example looks like this:

{
  "name": "Avi",
  "role": "Backend Developer",
  "skills": ["Java", "Spring Boot", "Kafka"],
  "experience": 10
}

Basic building blocks of JSON

Object - wrapped in curly braces { } with key-value pairs.
Array - an ordered list inside [ ].
String - text wrapped in double quotes "like this".
Number - integers or decimals (no quotes).
Boolean - true or false.
null - represents an empty or unknown value.

Why JSON became more popular than XML

Before JSON, most systems exchanged data using XML. XML is powerful but verbose. JSON became popular because it is:

Less noisy - fewer characters and no closing tags.
Easy to read - looks like JavaScript objects.
Easy to parse - built-in support in almost every language.
Perfect for web APIs - fits naturally with JavaScript.

Common JSON mistakes

JSON is strict. Small mistakes can break parsing. Some common errors:

Using single quotes instead of double quotes.
Missing commas between properties.
Trailing commas after the last element.
Unquoted object keys (must be in double quotes).

// ❌ Invalid JSON (single quotes & trailing comma)
{
  'name': 'Avi',
  'role': 'Developer',
}

// ✅ Valid JSON
{
  "name": "Avi",
  "role": "Developer"
}

How to view and format JSON easily

Raw JSON from APIs often comes as a single long line, which is difficult to read. You can use an online JSON viewer and formatter to pretty-print and debug your data.

Try this workflow:

Go to JSONViewerTool.com.
Paste your JSON into the left editor.
Click Format JSON -> Right to beautify it.
Switch between Tree and Code modes.
Use Validate to detect syntax issues instantly.

Where JSON is used in real projects

Backend APIs returning response data to frontends.
Microservices communicating with each other.
Configuration files for tools and apps.
Logging structured events in JSON format.
Cloud services (AWS, GCP, Azure) that expose JSON APIs.

Next steps

Now that you know what JSON is, the best way to get comfortable is to experiment. Paste some real API responses into JSON Viewer Tool, explore them in tree mode, and try converting them to CSV or YAML:

Convert JSON -> CSV for Excel or Google Sheets.
Convert JSON -> YAML for DevOps and Kubernetes.

Related tools

JSON vs XML - Which One Should You Use in 2025?

Avinash Verma — Mon, 08 Jun 2026 17:54:42 +0000

JSON and XML are two of the most popular data formats. In this article, we compare them and help you decide which one to use in 2025.

What is JSON?

JSON (JavaScript Object Notation) is a lightweight, text-based format for representing structured data. It is built on name-value pairs and ordered lists, and is extremely popular for web APIs, microservices and configuration files.

{
  "id": 1,
  "name": "Avi",
  "active": true,
  "skills": ["Java", "Spring Boot"]
}

What is XML?

XML (eXtensible Markup Language) is a markup language designed to store and transport data. It uses nested tags and attributes and was widely used for SOAP APIs and document formats.

<user id="1" active="true">
  <name>Avi</name>
  <skills>
    <skill>Java</skill>
    <skill>Spring Boot</skill>
  </skills>
</user>

Syntax and readability

JSON is usually shorter and easier to read. Developers can quickly scan curly braces and arrays, and the structure looks similar to JavaScript objects. XML is more verbose and includes both opening and closing tags, which can be harder to scan in large documents.

JSON uses curly braces and square brackets with quoted keys.
XML uses nested tags with attributes and closing tags.
JSON is often preferred for quick debugging in browser tools.

Tooling and ecosystem

Modern web and mobile development ecosystems are heavily optimized for JSON. Browsers, frontend frameworks, backend frameworks and cloud APIs all treat JSON as a first-class citizen.

Most REST APIs today return JSON by default.
Popular languages have built-in JSON support.
Tools like JSONViewerTool.com make it easy to view, format and validate JSON in the browser.

Performance and size

JSON tends to produce smaller payloads than XML because it has less structural overhead. Smaller payloads mean lower bandwidth usage and faster response times.

When JSON is the better choice

Building REST or GraphQL APIs for web or mobile apps.
Config files for microservices, frontends and automation scripts.
Logging structured events in a consistent format.

If you regularly work with JSON data, try JSON Viewer Tool to format, validate and convert JSON without leaving your browser.

When XML is still useful

Legacy systems and SOAP web services.
Document-centric use cases like Office formats.
Standards that are defined strictly in XML.

Conclusion

For most modern web and backend projects in 2025, JSON is the clear default. XML still matters in some legacy and document-heavy domains, but if you are building APIs or working with frontend applications, JSON will usually be the right choice.

To explore JSON more easily, you can use the online JSON Viewer, or convert your data to other formats like JSON -> CSV or JSON -> YAML.

Related tools

Convert JSON to CSV Easily - A Complete Guide for Developers

Avinash Verma — Mon, 08 Jun 2026 17:54:29 +0000

Need JSON data in Excel or Google Sheets? Learn how to convert JSON to CSV safely and quickly using JSONViewerTool.com.

Why convert JSON to CSV?

CSV (Comma-Separated Values) is a simple tabular format supported by Excel, Google Sheets and almost every BI tool. When you receive JSON from an API but need to analyse it in a spreadsheet, converting JSON to CSV is the easiest workflow.

Business users understand rows and columns.
CSV is compatible with spreadsheets and BI dashboards.
JSON can be complex to analyse directly without tooling.

Flat JSON vs nested JSON

Before converting, it helps to understand the shape of your JSON.

Flat JSON - each object has only simple key-value pairs.
Nested JSON - objects contain other objects or arrays.

[
  { "id": 1, "name": "Avi", "country": "IN" },
  { "id": 2, "name": "Kriti", "country": "US" }
]

The above is flat JSON - perfect for CSV. Each property becomes a column.

How JSONViewerTool converts JSON to CSV

JSONViewerTool.com has a dedicated JSON -> CSV Converter page that handles common cases automatically.

Go to JSON -> CSV Converter.
Paste or upload your JSON on the left side.
Click Convert JSON -> CSV.
View the generated CSV table on the right.
Copy the CSV or download it as a .csv file.

Handling nested objects and arrays

Nested objects and arrays need to be flattened to fit neatly into rows and columns. There are several strategies:

Use dot notation for nested keys (e.g. address.city).
Join arrays into a comma-separated string.
Explode arrays into multiple rows when each item is its own record.

Tips for clean CSV output

Validate your JSON before converting.
Ensure consistent keys across all objects.
Remove irrelevant or extremely nested fields.

Next steps

Ready to try it? Open the JSON -> CSV Converter, paste some real API JSON, and export it to a CSV file for analysis. If you ever need to go in the opposite direction, use the CSV -> JSON Converter.

Related tools

How to Format and Beautify JSON Online (Step-by-Step Guide)

Avinash Verma — Mon, 08 Jun 2026 17:53:40 +0000

Raw JSON is hard to read. A JSON formatter turns it into clean, indented, human-friendly structure. Here's how to do it in seconds.

Why you need a JSON formatter

When you copy JSON from an API, browser console, or log file, it often comes in a single long line. That's impossible to read and debug.

{"id":1,"name":"Avi","role":"Developer","skills":["Java","Spring Boot"],"active":true}

After formatting, the same JSON becomes:

{
  "id": 1,
  "name": "Avi",
  "role": "Developer",
  "skills": [
    "Java",
    "Spring Boot"
  ],
  "active": true
}

How to format JSON online (step-by-step)

Open JSONViewerTool.com.
Paste or type your JSON into the left editor.
Click Format JSON -> Right in the toolbar.
View the formatted output in the right panel.
Switch between Tree, Code and Text modes.

In tree mode, you can expand/collapse objects and arrays to explore nested structures. This is very useful when dealing with deeply nested API responses.

Detecting errors while formatting

If your JSON is invalid, the formatter will fail. Instead of guessing what went wrong, use the Validate button:

Paste JSON on the left.
Click Validate Left (or similar button in your viewer toolbar).
Check the error message and line number.

Common validation errors include:

Missing or extra commas.
Using single instead of double quotes.
Unescaped special characters inside strings.

Beautify vs minify: when to use which

A formatter usually supports both beautify and minify.

Beautify - adds new lines and indentation for humans.
Minify - removes whitespace for machines (smaller payloads).

Use beautified JSON while developing, and minified JSON when sending data over the network.

Formatting JSON for debugging APIs

When debugging backend APIs or microservices, formatted JSON can save time:

Quickly see which fields are missing or null.
Compare original vs expected JSON.
Copy only parts of the JSON structure.

JSONViewerTool lets you use one side as the "original" and the other as "formatted", so you can compare easily.

Next steps

Now that you know how to format JSON, try some advanced workflows:

Convert formatted JSON to CSV for reporting.
Convert JSON to YAML for Kubernetes and DevOps.
Use the JSON Viewer daily for APIs, logs and configs.

Related tools

How to Decode JWT Tokens Safely (Without Sending Them to a Server)

Avinash Verma — Mon, 08 Jun 2026 17:53:27 +0000

JSON Web Tokens (JWT) are everywhere in modern authentication. Here's how to inspect them safely without leaking sensitive data.

What is a JWT?

A JSON Web Token (JWT) is a compact, URL-safe token format often used for authentication and authorization. It consists of three base64url-encoded parts separated by dots.

header.payload.signature

JWT structure: header, payload, signature

Header - contains metadata like the algorithm and token type.
Payload - contains claims such as sub, exp, iat and custom fields.
Signature - ensures integrity when verified with the correct key.

Why online JWT decoders can be risky

Some JWT decoder websites send tokens to a remote backend server for decoding or logging. If your token contains sensitive information, sending it to an untrusted server is dangerous.

Tokens may be logged on the server side.
Sensitive claims might be exposed to third parties.
If a token is still valid, it could potentially be misused.

Decoding JWTs 100% client-side

A safer approach is to use a decoder that works entirely in your browser, with no network calls. The JWT Decoder on JSONViewerTool.com is designed to be 100% client-side.

Open JWT Decoder.
Paste your JWT token into the left editor.
Click Decode JWT.
Inspect the decoded header and payload on the right.

Important: decoding is not verifying

Decoding a JWT simply base64-decodes the header and payload. It does not verify the signature or tell you whether the token is valid or trusted. Always perform proper verification on the server side.

Next steps

Whenever you need to quickly inspect a token, use the online JWT Decoder. For full security, always validate tokens on the server as well.