JSON в Excel и обратно
Вставьте JSON или загрузите файл: конвертер найдёт массив записей, разложит поля по колонкам, вложенные через точку, и отдаст .xlsx или CSV. Обратный режим собирает JSON из книги Excel: массив объектов, массив массивов или объект по ключу.
Как разбирать
Вставьте JSON или загрузите файл.
Какие структуры JSON превращаются в таблицу и как
JSON бывает плоским и вложенным, с массивом наверху и с массивом в глубине. Восемь случаев, которые встречаются чаще всего, и что из них получается.
| Структура | Пример | Что получится |
|---|---|---|
| Массив объектов | [{"id":1,"name":"Стол"},{"id":2,"name":"Кресло"}] | колонки id и name, по строке на объект |
| Массив в глубине ответа API | {"ok":true,"data":{"items":[…]}} | записью станет items, путь показан над таблицей |
| Вложенный объект | {"адрес":{"город":"Тула","улица":"Ленина"}} | колонки адрес.город и адрес.улица |
| Массив простых значений | {"теги":["акция","новинка"]} | клетка «акция; новинка» |
| Массив объектов внутри записи | {"заказ":5,"товары":[{"sku":1},{"sku":2}]} | компактный JSON в клетке; товары можно выбрать записью |
| Объект объектов | {"00123":{"цена":100},"00124":{"цена":200}} | колонка «ключ» плюс поля объектов |
| Массив массивов | [["id","name"],[1,"Стол"]] | строки как есть, первая может стать заголовком |
| NDJSON | {"a":1} {"a":2} | каждая строка — запись |
Как открыть JSON в Excel через Power Query
Excel открывает JSON без надстроек с версии 2016, и делает это через Power Query. Способ хорош для регулярных выгрузок: шаги запоминаются, и следующий файл обновится по кнопке.
Импорт
Данные → Получить данные → Из файла → Из JSON, выбрать файл. Откроется редактор запросов со списком записей. Если наверху лежит объект, а массив спрятан в поле вроде data, щёлкните по значению List рядом с нужным полем, редактор перейдёт внутрь. Дальше кнопка В таблицу на вкладке «Преобразовать», потом стрелка с двумя направлениями в заголовке единственной колонки: она раскрывает поля записей в отдельные колонки. Вложенные объекты раскрываются той же стрелкой ещё раз. В конце Закрыть и загрузить.
Что идёт не так
Даты приходят текстом, потому что в JSON нет типа даты: столбцу нужно задать тип вручную. Числа с точкой в дробной части Power Query читает по локали, и 12.5 на русской Windows может стать 125 или текстом, лечится типом «Десятичное число» с локалью «Английский». Массивы внутри записи раскрываются в дополнительные строки, и одна запись размножается: заказ с тремя товарами превратится в три строки заказа. Конвертер выше в этом случае склеивает значения в одну клетку, а таблицу товаров даёт отдельно.
Обратно: из Excel в JSON
Штатной кнопки «Сохранить как JSON» в Excel нет. Пути три: макрос на VBA, Power Query с функцией Json.FromValue и последующим экспортом, либо конвертер выше: заголовки становятся ключами, каждая строка объектом.
Именно такой файл получается в режиме «Excel → JSON» по умолчанию. Артикул остался строкой из-за ведущих нулей, цена стала числом, пустые клетки превращаются в null или пропускаются по выбору.
Как конвертер находит записи в JSON
JSON бывает четырёх устройств, и конвертер различает их сам. Массив объектов превращается в таблицу напрямую: ключи в шапку, объект в строку. Ответ API обычно оборачивает данные в объект вроде {"ok": true, "data": {"items": [...]}}, и тогда конвертер обходит дерево, собирает все массивы, ставит первым тот, где больше всего объектов, и показывает путь к нему над таблицей. Объект объектов, где ключами служат артикулы или ID, раскладывается в строки с дополнительной колонкой «ключ». Файл, который не разбирается целиком, пробуется построчно как NDJSON.
Внутри записи вложенный объект даёт колонки с путём через точку, массив простых значений склеивается через точку с запятой, а массив объектов записывается компактным JSON в одну клетку и заодно попадает в список кандидатов, чтобы его можно было развернуть в отдельную таблицу. Числа остаются числами, true и false словами, null пустой клеткой. Обратный режим собирает JSON из книги Excel в одной из трёх форм и приводит типы: «12 500» становится числом, «00123» остаётся строкой из-за ведущих нулей. Текстовый CSV в JSON переводит соседний конвертер.
| Форма JSON | Что станет строкой | Что станет колонкой |
|---|---|---|
| массив объектов | каждый объект | каждый ключ, вложенные через точку |
| объект с массивом в глубине | элемент найденного массива | ключи элемента; путь показан над таблицей |
| объект объектов | каждое значение | колонка «ключ» плюс поля значения |
| массив массивов | каждый вложенный массив | позиции; первая строка может стать шапкой |
| NDJSON | каждая строка файла | ключи объекта строки |
История: от литералов JavaScript до RFC 8259
Формат вырос из литералов объектов JavaScript, которые были в языке с 1995 года. В 2001 году Дуглас Крокфорд с коллегами по компании State Software начал использовать такую запись для обмена данными между браузером и сервером вместо тяжёлого XML, а в 2002 году зарегистрировал сайт json.org с описанием грамматики на одной странице. Крокфорд говорил, что лишь обнаружил формат, который уже существовал внутри языка.
Стандартом JSON стал в июле 2006 года как RFC 4627, в октябре 2013 года его закрепила ECMA-404, а действующее описание вышло в декабре 2017 года как RFC 8259. К этому времени формат вытеснил XML из веб-API почти полностью: он короче, читается людьми и разбирается одной функцией в любом языке. Оборотная сторона: у JSON нет типа даты и нет схемы по умолчанию, поэтому даты приходят строками, а структура ответа у каждого сервиса своя.
Excel научился читать JSON через Power Query: надстройка вышла для Excel 2010 и 2013, а в Excel 2016 стала встроенной вкладкой «Данные». Путь «Из JSON» работает, но требует нескольких шагов с раскрытием списков и записей, и на каждый новый файл эти шаги повторяют. Отсюда постоянный спрос на конвертер, который делает то же за одну загрузку.
Как разложить ответ API и собрать JSON из таблицы
Вставьте JSON или загрузите файл. Над таблицей появятся число записей, число колонок и путь к массиву, который стал источником строк. Если нужен другой массив, например товары внутри заказов, выберите его в списке справа: там перечислены все найденные массивы с числом элементов. Для массива массивов появится галочка, делающая первую строку шапкой. Дальше «Скачать .xlsx», CSV в UTF-8 с BOM или «Копировать для Excel», чтобы вставить клетки прямо в лист.
Для обратного перевода переключитесь на «Excel → JSON», загрузите книгу или вставьте клетки и выберите форму: массив объектов для API и скриптов, массив массивов для компактной передачи, объект по первой колонке для поиска по ключу. Числа и логические значения приводятся к типам, пустые клетки становятся null или пропускаются, отступы задаются списком. Результат пересчитывается при каждом изменении и скачивается файлом .json. Перед сборкой дубли в таблице убирает удаление дубликатов.
| Устройств JSON на входе | 5, включая NDJSON |
| Поиск массива | по всему дереву, до 6 уровней |
| Вложенные объекты | путь через точку |
| Массивы значений | через «; » |
| Форм на выходе | 3 |
| Типы при сборке | число, логическое, null |
| Ведущие нули | остаются строкой |
| Стандарт | RFC 8259, 2017 год |
Как пользоваться
- Подойдёт ответ API, экспорт из базы или сервиса, NDJSON по строке на запись. Если записи лежат глубоко, например в data.items, конвертер найдёт этот массив сам и покажет путь.
- Ключи объектов стали заголовками, вложенные объекты дали колонки через точку: адрес.город. Массивы простых значений склеены через точку с запятой, массивы объектов записаны компактным JSON, их можно выбрать записью в списке «массив».
- .xlsx для Excel, CSV в UTF-8 с BOM для программ, копия для вставки в лист. Числа станут числами, логические значения словами true и false, null пустой клеткой.
- Загрузите .xlsx или вставьте клетки, выберите форму: массив объектов с ключами из заголовков, массив массивов или объект, где ключом служит первая колонка. Числа приводятся к числам, пустые клетки к null или пропускаются.
Частые вопросы
Как открыть JSON в Excel без конвертера
В Excel 2016 и новее: «Данные → Получить данные → Из файла → Из JSON». Power Query покажет список записей, кнопка «В таблицу» превратит его в таблицу, а вложенные объекты раскрываются стрелками в заголовках колонок. Для одноразового файла путь длинный, для регулярной выгрузки удобный: следующий файл обновится по кнопке.
Почему в таблице появились колонки с точками
Так раскладываются вложенные объекты: у записи {"адрес": {"город": "Тула"}} появится колонка адрес.город. Каждое поле сохраняется отдельной колонкой, поэтому ничего не теряется, лишнее удаляется в Excel одним движением.
Что будет с массивом внутри записи
Массив простых значений, например теги, склеивается через точку с запятой. Массив объектов, например список товаров в заказе, записывается компактным JSON в одну клетку; если нужна таблица товаров, выберите этот массив в списке «массив», и записью станет товар.
Что такое NDJSON и читается ли он
Формат, где каждая строка файла — отдельный JSON-объект, без общего массива и запятых между строками; так выгружают логи и большие коллекции. Читается: если текст целиком не разбирается как JSON, конвертер пробует разобрать его построчно.
Как получить JSON нужной формы из таблицы
Массив объектов подходит для API и скриптов: ключи из заголовков, одна запись на строку. Массив массивов компактнее и годится для табличных данных без имён. Объект по первой колонке даёт словарь вида {"00123": {…}}, удобный для поиска по артикулу или ID.
Сколько данных можно обработать
Разбор идёт в браузере, файлы в десятки мегабайт и сотни тысяч записей раскладываются за секунды. На экране первые двести строк, в файл уходят все.
Куда уходит файл
Никуда: разбор и сборка идут на вашем устройстве, ничего не отправляется и не сохраняется.
Похожие инструменты
Объединить две таблицы по ключевому столбцу без формул: подтянуть столбцы из второй таблицы к первой, показать совпадения и строки без пары. Ключи сравниваются без регистра, пробелов и буквы ё, режим «только цифры» для телефонов; XLSX и CSV.
ОткрытьCSV в Excel и обратноКонвертер CSV в .xlsx с автоопределением кодировки и разделителя, без иероглифов и склеенных колонок; обратно из книги Excel делает CSV в UTF-8 с BOM или Windows-1251 с нужным разделителем.
ОткрытьXML в Excel и обратноЛюбой XML в таблицу: сам находит повторяющийся элемент, раскладывает теги и атрибуты по колонкам, отдаёт .xlsx, CSV и JSON; обратно собирает XML из книги Excel с вашими именами тегов.
ОткрытьТаблица онлайнКонструктор таблицы в браузере: шапка, строки и столбцы, вставка из Excel по клеткам, сортировка, суммы, транспонирование, шаблоны для заполнения. Выгрузка в XLSX, CSV, Word, Markdown, HTML, PNG и печать на А4.
ОткрытьРазделить списокРазбить на группы по N строк или на N равных частей. Бить семантику на пакеты.
ОткрытьЧисла: сумма и столбцыСумма столбца, попарное сложение и умножение двух столбцов чисел.
ОткрытьЕщё инструменты для списков
Удаление дубликатов и сравнение двух списков, сортировка, столбец в строку и обратно, нумерация, частотность и шаблонизатор — в разделе 29 инструментов.