Как json перевести в таблицу
Перейти к содержимому

Как json перевести в таблицу

  • автор:

Вывод табличных данных Excel в виде JSON для использования в Power Automate

Данные таблицы Excel можно представить в виде массива объектов в формате JSON. Каждый объект представляет строку в таблице. Это помогает извлекать данные из Excel в согласованном формате, который виден пользователю. Затем данные могут быть переданы другим системам с помощью потоков Power Automate.

Настройка: пример файла Excel

Эта книга содержит данные, объекты и форматирование, ожидаемые скриптом.

Лист с входной таблицей.

Вариант этого примера также включает гиперссылки в одном из столбцов таблицы. Это позволяет отображать дополнительные уровни данных ячейки в ФОРМАТЕ JSON.

Лист со столбцом данных таблицы, отформатированными в виде гиперссылок.

Пример кода: возврат данных таблицы в формате JSON

Добавьте следующий скрипт в пример книги и попробуйте его самостоятельно!

Структуру можно изменить в interface TableData соответствии со столбцами таблицы. Обратите внимание, что для имен столбцов с пробелами обязательно поместите ключ в кавычки, например в «Event ID» примере. Дополнительные сведения о работе с JSON см. в статье Использование JSON для передачи данных в скрипты Office и из нее.

function main(workbook: ExcelScript.Workbook): TableData[] < // Get the first table in the "PlainTable" worksheet. // If you know the table name, use `workbook.getTable('TableName')` instead. const table = workbook.getWorksheet('PlainTable').getTables()[0]; // Get all the values from the table as text. const texts = table.getRange().getTexts(); // Create an array of JSON objects that match the row structure. let returnObjects: TableData[] = []; if (table.getRowCount() >0) < returnObjects = returnObjectFromValues(texts); >// Log the information and return it for a Power Automate flow. console.log(JSON.stringify(returnObjects)); return returnObjects > // This function converts a 2D array of values into a generic JSON object. // In this case, we have defined the TableData object, but any similar interface would work. function returnObjectFromValues(values: string[][]): TableData[] < let objectArray: TableData[] = []; let objectKeys: string[] = []; for (let i = 0; i < values.length; i++) < if (i === 0) < objectKeys = values[i] continue; >let object: <[key: string]: string>= <> for (let j = 0; j < values[i].length; j++) < object[objectKeys[j]] = values[i][j] >objectArray.push(object as unknown as TableData); > return objectArray; > interface TableData

Пример выходных данных на листе PlainTable

Пример кода: возврат данных таблицы в формате JSON с текстом гиперссылки

Скрипт всегда извлекает гиперссылки из 4-го столбца (индекс 0) таблицы. Вы можете изменить этот порядок или включить несколько столбцов в качестве данных гиперссылки, изменив код в примечании. // For the 4th column (0 index), extract the hyperlink and use that instead of text.

function main(workbook: ExcelScript.Workbook): TableData[] < // Get the first table in the "WithHyperLink" worksheet. // If you know the table name, use `workbook.getTable('TableName')` instead. const table = workbook.getWorksheet('WithHyperLink').getTables()[0]; // Get all the values from the table as text. const range = table.getRange(); // Create an array of JSON objects that match the row structure. let returnObjects: TableData[] = []; if (table.getRowCount() >0) < returnObjects = returnObjectFromValues(range); >// Log the information and return it for a Power Automate flow. console.log(JSON.stringify(returnObjects)); return returnObjects > function returnObjectFromValues(range: ExcelScript.Range): TableData[] < let values = range.getTexts(); let objectArray : TableData[] = []; let objectKeys: string[] = []; for (let i = 0; i < values.length; i++) < if (i === 0) < objectKeys = values[i] continue; >let object = <> for (let j = 0; j < values[i].length; j++) < // For the 4th column (0 index), extract the hyperlink and use that instead of text. if (j === 4) < object[objectKeys[j]] = range.getCell(i, j).getHyperlink().address; >else < object[objectKeys[j]] = values[i][j]; >> objectArray.push(object as TableData); > return objectArray; > interface TableData

Пример выходных данных на листе WithHyperLink

Использование в Power Automate

Сведения об использовании такого сценария в Power Automate см. в статье Создание автоматизированного рабочего процесса с помощью Power Automate.

Совместная работа с нами на GitHub

Источник этого содержимого можно найти на GitHub, где также можно создавать и просматривать проблемы и запросы на вытягивание. Дополнительные сведения см. в нашем руководстве для участников.

Как конвертировать JSON в CSV

JSON (JavaScript Object Notation) — отличный формат данных, но все его прелести раскрываются, когда работаешь с ним в коде. А вот работать с ним в Экселе совсем неудобно, и тогда нужно конвертировать его в CSV. По запросу «json to csv converter» Гугл выдает кучу различных сервисов, но мне больше всего приглянулся этот.

Добавить JSON в сервис можно одним из трех способов:

  1. Сохранить JSON как файл и указать путь к файлу
  2. Ввести URL с запросом и нажать «Load URL»
  3. Вставить текст JSON

Можно выбрать нужный разделитель для CSV (Output Field Separator).
После того как данные появились в поле для входных данных, нажимаем «Convert JSON to CSV»:

На выходе получаем данные в табличной структуре, разделенные запятой:

Особенность преобразования в том, что сложные объекты JSON’а с многими уровнями вложенности приводятся к простейшему ассоциативному массиву (словарю):

Что потом с этим делать? В первую очередь, открыть CSV в Экселе:

Данные разбиты на 2 строки: в первой строке ключи, во второй — значения. Удобнее будет работать с этими данными если их транспонировать: копируем данные и вставляем на новый лист с транспонированием. Должно получится что-то подобное:

Вот теперь с этим уже можно работать. Например, применить фильтр, который оставит только те пары значение-ключ, у которых ключ содержит «children» и «name»:

Или оставить данные, содержащие «result» и «children», и затем разбить ключи по разделителю «точка» через функцию «Текст по столбцам»:

Естественно, эти методы обработки данных пригодны только для данных из моего примера, но их можно использовать и на других данных, комбинируя формулы и функции Экселя, например, можно искать какой-то маркер в ключе с помощью функции ПОИСК. В принципе, сводится всё к одному: придумать как по ключу получить нужные значения.

JSON из примера можно скачать по ссылке.

Вступайте в группу на Facebook и подписывайтесь на мой канал в Telegram, там публикуются интересные статьи про анализ данных и не только.

Как json перевести в таблицу

Смотрю англоязычный видос там человек открывает json из меню
Данные -> Создать запрос ->из файла -> файл Json
а у меня в меню из файла нет подменю «файл Json»
Подскажите пожалуйста, какие предустановки должны быть у excel 2016 для открытия json файла?

Изменено: jonik38 — 17.02.2018 16:25:03
Пользователь
Сообщений: 14778 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
17.02.2018 16:25:47

Если у Вас нормальный 2016, то на вкладке Данные должна быть группа «Получить и преобразовать данные»(она относится к PowerQuery). Там надо выбрать «получить данные» -Из файла -и уже там есть JSON.

Изменено: Дмитрий Щербаков — 17.02.2018 16:26:01

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.

Пользователь
Сообщений: 10 Регистрация: 14.02.2018
19.02.2018 11:04:49
Респект! Спасибо!
Пользователь
Сообщений: 15 Регистрация: 04.07.2018
04.07.2018 11:29:17

Тоже задался вопросом, где же это меню. А нет его. Офис 2016 х64, установлено только 3 модуля — excel, word, access.
На видео под спойлером показан пример открытия.

Скрытый текст

Привожу 2 картинки.
Одна картинка из видео, где есть раздел в меню — открыть json, а вторая как у меня в офисе.
В моём варианте такой опции, как открытие json нет.
В связи с этим вопрос, подскажите, какой модуль отвечает за появление данной опции.
Что нужно ещё доустановить.
Спасибо.

Прикрепленные файлы

  • Ex_vid.png (104.64 КБ)
  • exc.png (17.65 КБ)

Пользователь
Сообщений: 6602 Регистрация: 22.02.2017
Excel x64 О365 / 2016 / Online / Power BI
04.07.2018 13:27:42

У меня такой кнопки, отродясь не было. Наверное нужно поставить крайние обновления.
А если уж нужно открыть, то вот пример использования функции Json.Document от MS без кнопки.

Вот горшок пустой, он предмет простой.
Пользователь
Сообщений: 11922 Регистрация: 22.12.2012
Excel 2016, 365
04.07.2018 13:45:45
Доброе время суток

Цитата
PooHkrd написал:
Json.Document

Да проще всё. Выбираем из текстового файла. В окне выбора файла выбирает тип Все файлы *.*, указываем нужный json файл. Power Query сам подхватит по расширению файл нужной функцией.

Пользователь
Сообщений: 6602 Регистрация: 22.02.2017
Excel x64 О365 / 2016 / Online / Power BI
04.07.2018 14:05:16
О как! Не знал, спасибо.
Вот горшок пустой, он предмет простой.
Пользователь
Сообщений: 8 Регистрация: 27.02.2019
27.10.2022 10:15:20

Здравствуйте! У меня офис 2013, в excel в пункте меню «power query» есть вкладка «из файла», где можно выбрать: из excel, из текстового или CSV-файла, из XML, из папки. Варианта «из json» нет. Пробую выбрать из текстового файла, как советовали выше, выходит сообщение об ошибке «в конце входного json-файла были обнаружены лишние символы». В окне этого сообщения есть кнопка «изменить», при нажатии на которую выходит окно, где указан путь к файлу, который можно изменить, а также «открыть файл как», где можно выбрать json, текстовый файл и пр., а также книга excel. Выбираю «книга excel», выходит сообщение об ошибке внешняя таблица не имеет предполагаемый формат».
Как мне открыть json в excel в табличном формате? Извините за сумбурное описание, я вообще не понимаю, что такое json и зачем он нужен

JSON в XLS — Конвертировать документ онлайн

1. Нажмите кнопку «FILE» или «URL», чтобы переключаться между локальными файлами или онлайн-файлами. Нажмите кнопку «Выбор файлов», чтобы выбрать локальный файл или ввести URL-адрес онлайн-файла. Формат документа может быть преобразован только в определённый целевой формат. Например, вы можете конвертировать DOC в DOCX, но вы не можете конвертировать DOC в XLSX.

2. Выберите целевой формат. Целевым форматом может быть PDF, DOC, DOCX, XLS, XLSX, PPT, PPTX, HTML, TXT, CSV, RTF, ODT, ODS, ODP, XPS или OXPS и т.Д.

3. Нажмите кнопку «Начало конверсии», чтобы начать конверсии. Выходной файл будет произведен ниже «Результаты конверсий». Нажмите иконку « », чтобы отобразить QR-код файла или сохранить файл на Google Drive или Dropbox.

Сравнить форматы JSON и XLS:
имя JSON XLS
Полное имя JavaScript Object Notation Microsoft Excel Binary File Format
Расширение .json .xls
MIME-тип application/json application/vnd.ms-excel
Разработчик json.org Microsoft
Тип формата Обмен данными документов
Описание JSON (англ. JavaScript Object Notation) — текстовый формат обмена данными, основанный на JavaScript. За счёт своей лаконичности по сравнению с XML, формат JSON может быть более подходящим для сериализации сложных структур. Если говорить о веб-приложениях, в таком ключе он уместен в задачах обмена данными как между браузером и сервером (AJAX), так и между самими серверами. Microsoft Excel — программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS, а также Android, iOS и Windows Phone. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA.
Связанное программное обеспечение Many programming languages Microsoft Office, LibreOffice, Kingsoft Office, Google Docs.
Пример файла sample.json sample.xls
Википедия JSON в Википедия XLS в Википедия

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *