JSON do Excelu: čtyři přístupy a kde každý ztrácí věrnost

Stručně: ploché pole objektů nebo objekt obalující pole se nejrychleji převede online nástrojem; Power Query použijte, když se data obnovují každý týden, pandas, když je mnoho polí k čištění, a skript Node jen tehdy, když se převod musí zapojit do pipeline – ale co znamená „jeden řádek“, rozhodněte dřív, než se dotknete vnořených dat nebo nekonzistentních klíčů.

Převod sám není obtížný; obtížné je následně zjistit, že se čísla stala textem a že se několik úrovní vnoření zhroutilo do jedné. Přečtěte strukturu dat před volbou metody a ušetříte si předělávání.

1. Nejprve zjistěte, jaký druh JSON máte

Otevřete soubor a podívejte se na nejvyšší úroveň:

První dva se převedou přímo s téměř 100% úspěšností; u posledních tří nejprve rozhodněte, co znamená „jeden řádek“, jinak je výsledný list nepoužitelný.

2. Přístup 1: online nástroj (1 minuta)

Otevřete JSON do tabulky, vložte data → vyberte oddělovač a hlavičku → exportujte CSV → otevřete v Excelu. Vhodné pro několik desítek až několik tisíc řádků dobře strukturovaných dat.

Tři věci, na které si musíte dát pozor:

• Dostanete CSV, nikoli xlsx. Když Excel otevře CSV, hádá kódování podle národního prostředí systému, takže čínský text může být mojibake. Spolehlivější je použít „Data → From Text/CSV“ a explicitně vybrat UTF-8.
• Identifikátory začínající 0 ztratí nuly. "007" je v CSV text, ale Excel ho automaticky převede na číslo 7. Během importu nastavte tento sloupec jako text, nebo k němu v JSON přidejte prefix.
• Dlouhá čísla se stanou vědeckým zápisem. Rodná čísla a čísla objednávek delší než 15 číslic ztratí přesnost; taková pole už v JSON mají být řetězce a po importu musí zůstat v textovém formátu.

3. Přístup 2: vestavěný Power Query v Excelu (opakovaně použitelný)

Když se vaše data obnovují každý týden, použijte Power Query vestavěný v Excelu: nakonfigurujte ho jednou a poté jen klikněte na „Refresh“:

1. „Data“ → „Get Data“ → „From File“ → „From JSON“;
2. Po výběru souboru se ocitnete v editoru Power Query, kde vidíte struktury List / Record;
3. Klikněte na ikonu rozbalení na pravé straně hlavičky sloupce a rozbalte vnořená pole úroveň po úrovni;
4. Až názvy sloupců a typy vypadají správně, „Close & Load“.

Výhodou je, že vnoření lze rozbalit do více sloupců vrstvu po vrstvě a typy lze přiřadit ručně; cenou je, že první nastavení vyžaduje pochopení rozdílu mezi List a Record – List je pole, Record je objekt a rozbalují se jiným směrem.

4. Přístup 3: Python + pandas (mnoho polí, nutné čištění)

复制import pandas as pd

# Objekt obalující pole: nejprve se dostanete na úroveň seznamu
rows = pd.read_json('data.json')['data']

# Rozbalte vnořená pole do více sloupců
frame = pd.json_normalize(rows, sep='_')

frame.to_excel('out.xlsx', index=False)

Klíčovým parametrem json_normalize je sep, který určuje styl pojmenování sloupců po rozbalení vnořených polí; user_name se v Excelu používá snadněji než user.name. Pole polí (například tags) vyžadují navíc krok explode:

复制frame = frame.explode('tags')

Tento krok rozdělí jeden řádek na několik – jediný způsob, jak zachovat vztah jedna k mnoha, za cenu duplikace zbývajících polí.

5. Přístup 4: skript Node (když se musí zapojit do pipeline)

复制import { readFileSync, writeFileSync } from 'node:fs';

const rows = JSON.parse(readFileSync('data.json', 'utf8'));
const headers = [...new Set(rows.flatMap((row) => Object.keys(row)))];
const escapeCell = (value) => `"${String(value ?? '').replaceAll('"', '""')}"`;
const lines = [
  headers.join(','),
  ...rows.map((row) => headers.map((key) => escapeCell(row[key])).join(',')),
];

writeFileSync('out.csv', '\uFEFF' + lines.join('\n'), 'utf8');

Dva detaily, které byste neměli vynechat: napište na začátek \uFEFF (BOM), aby Excel rozpoznal UTF-8, a dvojité uvozovky uvnitř buněk escapujte jako "", jinak se sloupce posunou.

6. Tři věci, které převod vždy ztratí

Také null a „pole chybí“ vypadají v listu stejně (obě jsou prázdné buňky), ale v obchodní doméně znamenají něco jiného. Když je potřebujete rozlišit, převeďte v JSON null explicitně na řetězec "null", nebo přidejte sloupec s příznakem _has_field.

7. Ověření po převodu: nekoukejte jen na první řádek

Po převodu namátkou zkontrolujte alespoň tři věci:

1. Počet záznamů: počet řádků CSV se má rovnat délce pole + 1 (hlavička); chybějící řádek obvykle znamená, že se spolkl koncový zalomení řádku;
2. Sloupec s nejvyšším podílem null: mnoho prázdných buněk naznačuje nekonzistentní názvy klíčů – vraťte se a zkontrolujte, zda původní JSON nepoužívá dvě varianty zápisu (userId a user_id);
3. Nejdelší řádek: obvykle tam přistanou vnořená pole; potvrďte, že se nic nezkrátilo.

Když potřebujete opačnou operaci (Excel/CSV zpět do JSON), použijte CSV do JSON a pamatujte, že hlavička se považuje za názvy klíčů – změna hlavičky znamená změnu názvů polí.

Časté dotazy

Jak se rozhodnout mezi online nástrojem, Power Query a pandas?

Online nástroj použijte pro několik desítek až několik tisíc řádků dobře strukturovaných dat, která převádíte jednou. Power Query použijte, když se data obnovují každý týden, protože se nakonfiguruje jednou a obnoví jedním kliknutím. Pandas s json_normalize použijte, když je mnoho polí k čištění, a skript Node, když se převod musí zapojit do pipeline.

Proč se čínský text v CSV změní na mojibake?

Export je CSV, nikoli xlsx, a Excel hádá kódování podle národního prostředí systému, takže UTF-8 bez BOM se zobrazí jako mojibake. Napište na začátek souboru BOM \uFEFF, nebo ho otevřete přes „Data → From Text/CSV“ a explicitně vyberte UTF-8.

Mají se vnořené objekty zploštit, nebo rozdělit do dalších řádků?

Zploštěte je úroveň po úrovni, místo abyste je vkládali jako řetězce: Power Query rozbalí vnořená pole do sloupců a json_normalize s sep udělá totéž v pandas. Pole polí vyžadují navíc krok explode, který rozdělí jeden řádek na několik. Předání hlubokého objektu do String(obj) vrátí jen [object Object].