JSON в Excel: чотири підходи та де кожен втрачає точність

Коротко: плаский масив об'єктів або об'єкт, що обгортає масив, найшвидше конвертувати через онлайн-інструмент; використовуйте Power Query, коли дані оновлюються щотижня, pandas — коли багато полів треба чистити, а скрипт Node — лише коли конвертація має стати частиною конвеєра; але визначте, що означає «один рядок», перш ніж торкатися вкладених даних чи неузгоджених ключів.

Сама конвертація не складна; складно потім виявити, що числа стали текстом, а кілька рівнів вкладеності згорнулися в один. Прочитайте структуру даних перед вибором методу — і позбавите себе переробки.

1. Спершу з'ясуйте, який у вас JSON

Відкрийте файл і подивіться на найзовнішніший рівень:

Перші два конвертуються безпосередньо з майже 100% успіхом; для останніх трьох спершу визначте, що означає «один рядок», інакше отримана таблиця буде непридатною.

2. Підхід 1: онлайн-інструмент (1 хвилина)

Відкрийте JSON у таблицю, вставте дані → оберіть роздільник і заголовок → експортуйте CSV → відкрийте в Excel. Підходить для кількох десятків до кількох тисяч рядків добре структурованих даних.

Три речі, за якими треба стежити:

• Ви отримуєте CSV, а не xlsx. Коли Excel відкриває CSV, він вгадує кодування за системною локаллю, тож китайський текст може стати «кракозябрами». Надійніше — скористатися «Дані → З тексту/CSV» і явно обрати UTF-8.
• Ідентифікатори, що починаються з 0, втрачають нулі. "007" у CSV є текстом, але Excel автоматично перетворює його на число 7. Під час імпорту встановіть цей стовпець як текст або додайте префікс у JSON.
• Довгі числа стають науковою нотацією. Національні ID і номери замовлень довші за 15 цифр втрачають точність; такі поля вже мають бути рядками в JSON і залишатися в текстовому форматі після імпорту.

3. Підхід 2: вбудований у Excel Power Query (багаторазовий)

Коли ваші дані оновлюються щотижня, скористайтеся вбудованим у Excel Power Query: налаштуйте один раз, а далі просто натискайте «Оновити»:

1. «Дані» → «Отримати дані» → «З файлу» → «З JSON»;
2. Після вибору файлу ви потрапляєте в редактор Power Query, де бачите структури List / Record;
3. Натисніть іконку розгортання праворуч від заголовка стовпця, щоб розгортати вкладені поля рівень за рівнем;
4. Коли назви й типи стовпців виглядають правильно, «Закрити й завантажити».

Перевага в тому, що вкладеність можна розгортати в кілька стовпців шар за шаром, а типи — призначати вручну; ціна — перше налаштування потребує розуміння різниці між List і Record: List — це масив, Record — об'єкт, і розгортаються вони в різних напрямках.

4. Підхід 3: Python + pandas (багато полів, треба чистити)

复制import pandas as pd

# Об'єкт, що обгортає масив: спершу дістатися рівня списку
rows = pd.read_json('data.json')['data']

# Розгорнути вкладені поля в кілька стовпців
frame = pd.json_normalize(rows, sep='_')

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

Ключовий параметр json_normalize — sep, який визначає стиль найменування стовпців після розгортання вкладених полів; user_name зручніше використовувати в Excel, ніж user.name. Масиви масивів (як-от tags) потребують додаткового кроку explode:

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

Цей крок розбиває один рядок на кілька — єдиний спосіб зберегти зв'язок «один-до-багатьох» — ціною дублювання решти полів.

5. Підхід 4: скрипт Node (коли конвертація мусить стати частиною конвеєра)

复制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');

Дві деталі, які не варто пропускати: запишіть \uFEFF (BOM) на початку, щоб Excel розпізнав UTF-8, і екрануйте подвійні лапки всередині комірок як "", інакше стовпці зсунуться.

6. Три речі, які конвертація втрачає завжди

Крім того, null і «поле відсутнє» в таблиці виглядають однаково (обидва — порожні комірки), але в бізнес-сфері означають різне. Коли треба їх розрізняти, явно перетворюйте null на рядок "null" у JSON або додайте службовий стовпець _has_field.

7. Перевірка після конвертації: не дивіться лише на перший рядок

Після конвертації вибірково перевірте принаймні три речі:

1. Кількість записів: кількість рядків CSV має дорівнювати довжині масиву + 1 (заголовок); відсутній рядок зазвичай означає, що кінцеве переведення рядка проковтнули;
2. Стовпець із найвищою часткою null: багато порожніх комірок указують на неузгоджені назви ключів — поверніться й перевірте, чи оригінальний JSON не використовує два написання (userId і user_id);
3. Найдовший рядок: вкладені масиви зазвичай потрапляють туди; підтвердьте, що нічого не обрізано.

Коли потрібна зворотна операція (Excel/CSV назад у JSON), скористайтеся CSV у JSON і пам'ятайте, що заголовок трактується як назви ключів — зміна заголовка є зміною назв полів.

Часті питання

Як обрати між онлайн-інструментом, Power Query і pandas?

Використовуйте онлайн-інструмент для кількох десятків до кількох тисяч рядків добре структурованих даних, які ви конвертуєте один раз. Використовуйте Power Query, коли дані оновлюються щотижня: його налаштовують один раз і оновлюють одним кліком. Використовуйте pandas із json_normalize, коли багато полів треба чистити, і скрипт Node, коли конвертація мусить стати частиною конвеєра.

Чому китайський текст у CSV стає «кракозябрами»?

Експорт — це CSV, а не xlsx, і Excel вгадує кодування за системною локаллю, тож UTF-8 без BOM показує «кракозябри». Запишіть BOM \uFEFF на початку файлу або відкрийте його через «Дані → З тексту/CSV» і явно оберіть UTF-8.

Вкладені об'єкти розгортати чи розбивати на додаткові рядки?

Розгортайте їх рівень за рівнем, а не вставляйте рядками: Power Query розгортає вкладені поля в стовпці, а json_normalize з sep робить те саме в pandas. Масиви масивів потребують додаткового кроку explode, що розбиває один рядок на кілька. Передача глибокого об'єкта в String(obj) дає лише [object Object].