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.
• Длинные числа становятся научной нотацией. Номера удостоверений и заказов длиннее 15 цифр теряют точность; такие поля в JSON уже должны быть строками и после импорта обязаны оставаться в текстовом формате.

3. Подход 2: встроенный Power Query Excel (многоразовый)

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

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

Плюс в том, что вложенность можно раскрывать в несколько столбцов слой за слоем, а типы назначать вручную; цена — при первой настройке нужно понять разницу между List и Record: List — это массив, Record — объект, и раскрываются они в разных направлениях.

4. Подход 3: Python + pandas (много полей, нужна чистка)

复制import pandas as pd

# Object wrapping an array: first reach the list level
rows = pd.read_json('data.json')['data']

# Expand nested fields into multiple columns
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].