Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как извлечь и разбить данные из ячейки с JSON в соседние ячейки?
 
#1
27.07.2018 12:26:16
Добрый день.
Внутри ячейки в Excel находится JSON  текст. Подскажите пожалуйста, каким образом можно извлечь и разбить в соседние ячейки содержимое JSON? Ниже пример JSON.Заранее спасибо всем ответившим!
Код
{
  "array": [
    1,
    2,
    3
  ],
  "boolean": true,
  "null": null,
  "number": 123,
  "object": {
    "a": "b",
    "c": "d",
    "e": "f"
  },
  "string": "Hello World"
}
Изменено: rudyboy - 27.07.2018 12:28:12
 
 
 
#2
27.07.2018 12:39:23
А какой результат ожидаете получить?
Вот горшок пустой, он предмет простой...
 
 
 
#3
27.07.2018 12:50:05
PooHkrd,
Если возможно, то так как на скриншоте.
 
 
 
#4
27.07.2018 13:19:59
Вариант на Power Query. Если с кавычками в заголовках столбцов вам прямо принципиально заморочиться, то это сможете переименовать в редакторе запросов самостоятельно.
Изменено: PooHkrd - 27.07.2018 13:21:39
Вот горшок пустой, он предмет простой...
 
 
 
#5
27.07.2018 13:26:50
Доброе время суток
И ещё одна версия на Power Query

PooHkrd, А стоит ли заморачиваться с таким количеством шагов для преобразования записи в таблицу?
Код
Table.FromRecords({Источник})
 
 
 
#6
27.07.2018 13:44:31
Спасибо большое, Андрей VG, и PooHkrd,.Предложенные вами способы решения покрывают мой вопрос :)  
 
 
 
#7
27.07.2018 13:48:30
Цитата
Андрей VG написал:
А стоит ли заморачиваться с таким количеством шагов
Это все кнопочный хардкор, там руками вообще ничего не дописано, кроме собственно функции Json.Document
Изменено: PooHkrd - 27.07.2018 13:49:01
Вот горшок пустой, он предмет простой...
 
 
 
#8
27.07.2018 13:57:36
PooHkrd, а можете подробнее пояснить порядок шагов при развертывании JSON в таблицу?  
 
 
 
#9
27.07.2018 14:33:32
Какую конкретно строку скрипта нужно пояснить?
Код
let
    Источник = Json.Document( Excel.CurrentWorkbook(){[Name="js"]}[Content]{0}[Column1] ),
    #"Преобразовано в таблицу" = Record.ToTable(Источник),
    #"Транспонированная таблица" = Table.Transpose(#"Преобразовано в таблицу"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица", [PromoteAllScalars=true]),
    #"Развернутый элемент object" = Table.ExpandRecordColumn(#"Повышенные заголовки", "object", {"a", "c", "e"}, {"object.a", "object.c", "object.e"}),
    #"Извлеченные значения" = Table.TransformColumns(#"Развернутый элемент object", {"array", each Text.Combine(List.Transform(_, Text.From), ",#(lf)"), type text})
in
    #"Извлеченные значения"
Вот горшок пустой, он предмет простой...
 
 
 
#10
27.07.2018 15:17:15
PooHkrd, в первую очередь я не совсем понимаю первую строку, где идет ссылка на таблицу. CurrenWorkbook ссылается на текущую книгу, а что означает js?
Преобразование в таблицу, транспонирование, повышение заголовка вопросов не вызывают.
Развернутый элемент object. если я правильно понял, идет через expand to columns (иконка в правом углу заголовка object)?
Array вы преобразовывали так: клик на иконку в правой части заголовка - Extract Values - insert Step - custom - запятая в качестве разделителя и Line feed в качестве последующего действия - ok, все верно?
Изменено: rudyboy - 27.07.2018 15:17:28
 
 
 
#11
27.07.2018 15:28:27
js - это именованная ячейка, посмотрите в Диспетчере имен. Код
Код
= Excel.CurrentWorkbook(){[Name="js"]}[Content]{0}[Column1]

забирает значение из текущей книги, диапазона js из первой строки и столбца Column1, здесь можно ознакомиться подробно с данной функцией на примере забора данных из смарт-таблиц.
Цитата
rudyboy написал:
все верно?
Да. Только в качестве разделителя не только запятая, но и символ перевода строки.
Изменено: PooHkrd - 27.07.2018 15:33:02
Вот горшок пустой, он предмет простой...
 
 
 
#12
27.07.2018 16:47:58
Спасибо, PooHkrd,  разобрался. Все заработало и на других JSON по этой же схеме.
 
 
 
#13
30.07.2018 12:37:34
Уважаемые  PooHkrd, Андрей VG, и другие посетители форума. В продолжение темы с JSON в ячейке Excel. Подскажите пожалуйста, каким образом можно решить следующую задачу: в колонке разположены ячейки с JSON. Из каждой индивидуальной ячейки с JSON необходимо извлечь, например, определенное числовое значение или строку и разместить в соседнюю ячейку. Отличие от предыдущего вопроса в том что ячеек с JSON, например, 100. Каким образом можно решить проблему извлечения нужных данных для такого количества ячеек с JSON?
 
 
 
#14
30.07.2018 12:40:22
Так Андрей же дал пример с таблицей в посте №5. Нужно только в его скрипте указать чтобы разворачивалась не вся запись, а конкретное её поле.
Вот горшок пустой, он предмет простой...
 
 
 
#15
30.07.2018 12:58:13
PooHkrd, в общих чертах способ понятен но несколько мелочей от меня ускользают. Поясните пожалуйста:
1) Необходимо ли именовать всю будущую область данных, или же можно именовать только область с JSON ячейками?
2) Не вполне понятно, каким образом реализован шаг "toRecs" (кажется, в нем Андрей превращает содержимое JSON в record, которое затем можно развернуть и извлечь отдельные элементы). Можно ли этот шаг реализовать не через код, а посредством кнопок в редакторе (как в вашем случае)?
 
 
 
#16
30.07.2018 14:33:56
Чисто кнопками вряд ли получится. Все равно придется руками в код лезть. Другой вопрос, что ручное вмешательство можно минимизировать в рамках вашего круга познаний данного инструмента.
Самый просто вариант, это сделать кнопками обработку одной ячейки как в моем примере, чтобы вы понимали логику процесса, после чего из этого запроса легким движением руки сделать функцию (вот здесь хозяин сайта показывал как это можно сделать), после чего вызвать эту функцию уже в запросе обрабатывающем массив однотипных ячеек.
Как время появится - покажу пример обработки, если сами не додумаетесь как это сделать.
Вам хоть какой из параметров нужно оставить?
Изменено: PooHkrd - 30.07.2018 14:35:51
Вот горшок пустой, он предмет простой...
 
 
 
#17
30.07.2018 14:44:45
PooHkrd, если говорить о JSON из первого поста, то number. Но меня в целом интересует принцип извлечения нужной информации из JSON в соседнюю ячейку.
Также поясните пожалуйста, по возможности, что в коде означает строка
Код
Source = Excel.CurrentWorkbook(){[Name="json_scr"]}[Content][[JSON]]
А точнее, каким образом нужно подготовить страницу для корректной внесения в редактор всего столбца с JSON ячейками?
 
 
 
#18
30.07.2018 14:51:58
Вот вариант преобразования по алгоритму из поста 16.
Готовить особо ничего не надо: на листе1 таблица-источник, на листе 2 таблица результат.
Вставляйте в столбец источника ваши ячейки обновляйте таблицу-результат и получите искомую вами таблицу.
В запросе js_mass последний шаг это разворачивание нужных вам столбцов, если нужны данные только столбца number то разворачивайте только его.
Изменено: PooHkrd - 30.07.2018 14:52:13
Вот горшок пустой, он предмет простой...
 
 
 
#19
31.07.2018 10:31:23
Цитата
PooHkrd написал:
руками вообще ничего не дописано, кроме собственно функции Json.Document
его тоже не обязательно руками писать:

и при таком подходе (трансформация столбца) не нужна функция, если ячеек много, всё делаем кнопками хоть для одной, хоть для столбца одинаково
Код
// json
let
    Source = Excel.CurrentWorkbook(){[Name="json"]}[Content],
    #"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"array", "boolean", "null", "number", "object", "string"}, {"array", "boolean", "null", "number", "object", "string"}),
    #"Expanded {0}1" = Table.ExpandRecordColumn(#"Expanded {0}", "object", {"a", "c", "e"}, {"object.a", "object.c", "object.e"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded {0}1", {"array", each Text.Combine(List.Transform(_, Text.From), ",#(lf)"), type text})
in
    #"Extracted Values"
F1 творит чудеса
 
 
 
#20
31.07.2018 10:35:45
Круто, повторил, спасибо. Жизнь еще проще, чем мне казалось.
Вот горшок пустой, он предмет простой...
 
 
 
#21
31.07.2018 11:59:51
Максим Зеленский, PooHkrd, спасибо огромное. Все воспроизвел, идеально подходит для решения моей задачи.
 
 
 
Страницы: 1
Читают тему
Наверх