Easily enriching publicly available data (and really any other kind of data)
[Disclaimer: the Excel-JSON import offered by the Power Query Excel add-in is only available for Windows — upvote this UserVoice feature request so the team prioritizes adding the PowerQuery Excel add-in for Mac.]
As part of a UW class on information visualization taught by Brock Craft, I found myself changing gears from big data, to much smaller data.
Data wrangling is a necessary step when working with publicly available data and enriching/preparing said data for consumption by apps, services, or even visualizing it directly with libraries such as D3.js. Online services such as the Microsoft Azure Maps service API or the Google Maps API can help to enrich location data by either geocoding, or reverse-geocoding depending on the data available, and format needed. In my case, I had a very simple dataset that had (among many) a column for the name of a US city, and another for the US state for each row.
My goal: enrich each row in the dataset with the latitude and longitude to be able to use D3.js more efficiently.
Tools used
-
— super easy to create and format requests to web APIs. — my favorite lightweight editor — the ultimate application to work with smaller datasets by using pivot tables, look-ups, etc. — used to query each city and state in order to obtain the address, geographic coordinates, and other useful pieces of data, such as county, state initials, etc.
Getting the data from Azure Maps using their API
[As a prerequisite, you need an Azure subscription — you can get some free credit when signing up for Azure]
The first step was to create an Azure Maps account from inside the Azure Portal and obtain an API key. You can see how to do it in the Azure Maps documentation here.
With the Azure Maps API key, I used the Postman app to create a few sample requests and ensure the key was working nicely. You can see how to submit them in the How to Search For an Address section in the Azure Maps docs.
Once the basics worked, I made an Excel pivot table from the original dataset to list the distinct combinations of city and state and used the preview batch request API to send a query with them. This was a better approach than querying for the same city and state hundreds, or thousands of times.
I copied the response data into VS Code and saved it as .JSON to then work with the file in Excel.
Wrangling the data in Excel (Windows only)
Open the JSON file using the Power Query add-in:
Data →Get Data →From File →From JSON
Once you select the file, you’ll see the root nodes in the JSON file. Note that these are links that you can navigate to.
The first step in the Power Query Editor is to convert the nodes into columns.
Record Tools →Convert →Into Table
They will look like this once they are converted:
Click on the item List since it contains all the records we’re interested in. The summary shows the summary of the API request; for this process it is not relevant.
We’ll do the same transforming into table at this level:
After it is converted to a table, you’ll see a small icon that lets you expand the contents into columns. Click it to see the contents of the record object and to expand it:
After expanding, the table will show a record object and a list object in each row. This is because each row represents:
- A query record (the query we did for each row, such as Paris, Texas, US, or Seattle, Washington, US).
- A list of the associated search results for the query which can contain 1 or more results depending on the request settings. In this case, to make my life easier, in the request I specified only one.
If you notice the Postman screenshot, shows the first few rows of the result — you can see how results is in fact a list of records.
With this table, we will do the exact same thing again for each objects column→ Expand to new rows.
After expanding the query summaries, we scroll right, and then expand the results objects column:
This expansion will change the object from list to record because each list has only one record:
We expand this again:
After expanding, we’ll see data, plus some more objects:
From this table, we will expand the address objects once more to get the actual addresses, and the position object to get the latitude and longitude too:
At this point, we’ll see the actual latitude and longitude. Whew!
Load the data into the Excel worksheet
Once we’ve expanded the necessary objects (and if you want, hid unnecessary columns), we can save and load the table into Excel:
Once you load the data into the Excel sheet, it will be ready to use:
From this point on you can filter the data, create a pivot table, or do any other thing that you need to refine the dataset that you’re working with.
Conclusion
This guide covered some basic steps to import a JSON file into excel and how to expand the contents to see the actual data returned by the Azure Maps API.
I loved using the API directly — it took me in total about 20 minutes to geocode about
4,000 distinct addresses, which I then added to my main dataset.
The steps shown here worked well for my needs, but be aware that there are other ways to do this using Python, or even using other services, such as Google Maps. Depending on your specific needs you might need to fine tune the API request, or filter specific fields returned by the API.
With this wrangling step complete, you should be able to import your data into D3.js or other application and start to work on the visualization.
Как открыть json файл в виде таблицы
Tool to view JSON in table form. The tool visually converts JSON to table.
Tool allows to extend visual representation of a property with data from referenced property and highlight selected values.
Use «Merge settings» block to set up extensions and text highlighting. Settings are provided in JSON form.
extensions are set up in ‘extensionsInput’: ‘wrapperobject’ — object to add data to, ‘baseobject’ — object to take data from, ‘wrapperrefproperty’ — property of a ‘wrapperobject’ which value will be used to find ‘baseobject’, ‘baserefproperty’ — property of a base object to search reference by. ‘extensionproperties’ — array of properties to add to wrapperobject, ‘extensionheader’ — header in table for the added property. Leave array ‘extensionproperties’ empty to add all properties from ‘baseobject’.
Highlighting is set up in ‘highlightValuesInput’: ‘property’ — name of a property (in any object) which value should be highlighted if matches setting ‘value’, ‘backgroundColor’ — color to use for highlighting.
Main JSON input and megre settings input display formatted JSON, highlight code and allow to edit input.
Inspired by and based on json2table tool
Comments and suggestions are _______, please leave your message _____: Send
10 удобных инструментов для преобразования JSON в Excel
Преобразование JSON в Excel вручную может быть утомительным процессом. Не волнуйся; некоторые отличные инструменты облегчат вам работу.
Файлы JSON — это не просто текстовые файлы с множеством чисел и строк, это структуры данных, состоящие из объектов и массивов для сохранения порядка их ввода.
Эта статья поможет вам быстро преобразовать структуры данных JSON в пригодный для использования документ Excel. Эти инструменты идеально подходят для сканирования файла JSON и превращения его в лист Excel, которым можно манипулировать.
Что такое файл JSON?
JSON — это аббревиатура от JavaScript Object Notation. Это формат обмена данными, обычно используемый для передачи данных через Интернет. Файлы JSON — это текстовые файлы, содержащие информацию об объекте или массиве объектов, и они определяют каждый элемент как пару имя-значение. Поэтому файлы JSON часто используются для передачи данных между приложениями.
Почему вы хотите преобразовать JSON в Excel?
Сохранение, просмотр и понимание данных лучше в формате Excel. Excel имеет множество функций для выполнения подробной аналитики. Он имеет расширенные функции, которые позволяют извлекать часть необходимых вам данных. Если у вас есть огромный файл JSON и вы хотите выполнить с ним какие-то операции, гораздо проще преобразовать его в Excel, выполнить необходимые операции и извлечения и преобразовать их обратно в JSON.
Насколько полезно хранить и передавать объекты данных в формате JSON?
Структуры данных JSON — отличный способ хранения и передачи объектов данных. Не только строки и числа делают их такими полезными, но и порядок, в котором они были введены, сохраняется для последующего анализа, поэтому, если у вас есть много данных, которые вы хотите отслеживать, использование файла JSON является простой способ запомнить, что все это значит.
С файлом JSON вы можете отслеживать все функции одновременно с его объектной структурой. Порядок, в котором они перечислены, помогает помнить об их соответствующем приоритете. Вот пример вложенного объекта JSON, где у объекта JSON есть другой объект:
Вот некоторые из самых популярных инструментов для преобразования JSON в Excel.
Инструменты преобразования
Инструменты преобразования — один из самых популярных способов преобразования файлов JSON в листы Excel, поскольку он имеет высокий коэффициент конверсии, отличные отзывы и низкую цену. Он также прост в использовании и не требует каких-либо знаний в области программирования или написания сценариев. Этот инструмент предлагает два метода преобразования: с помощью Downloader или с помощью Uploader.
Параметр «Загрузчик» позволяет загрузить файл с компьютера, а затем позволить приложению преобразовать его для вас. Напротив, опция Downloader позволяет вам предварительно просмотреть и отредактировать файл, прежде чем он будет преобразован в документ Excel.
Просто перетащите файл, выберите тип формата Excel (XLSX Excel 2007+, XLS Excel 97-2007) и нажмите кнопку «Выполнить преобразование». Для незарегистрированных пользователей существует ограничение на размер файла 10 МБ и десять конверсий в день. Зарегистрированные пользователи могут загружать файлы размером до 20 МБ и выполнять 30 преобразований в день.
Конвертировать CSV
Конвертировать CSV поможет вам преобразовать файл JSON в документ Excel или CSV. Он создаст документ Excel на основе информации, которую вы ввели в файл JSON, и он будет сохранен как новая книга с именем «FileName.xlsx». Этот инструмент предлагает простой в использовании интерфейс, который эффективно указывает, какие поля вы хотите импортировать и экспортировать из данных JSON, как вы можете видеть на иллюстрации ниже.
Он преобразует файлы с высокой скоростью преобразования и включает в себя расширенные параметры, такие как сортировка столбцов после преобразования, предварительный просмотр файлов JSON перед их преобразованием, фильтрация текста на основе регулярных выражений и т. д. Входными данными JSON может быть массив объектов или один объект имени/ пары значений или формат JSONLines/MongoDb с каждой записью JSON в отдельных строках.
Альтернативным вариантом преобразования файлов JSON является JSON Tool. Этот веб-инструмент позволяет без проблем конвертировать JSON в Excel! Этот инструмент прост в использовании и требует всего пару кликов. Если вы не хотите пользоваться этой услугой, вы также можете скачать программу бесплатно, нажав на кнопку «Скачать» в правой части экрана.
Инструмент JSON to Excel позволяет быстро и легко преобразовывать данные JSON, облегчая визуализацию данных. Устройство интеллектуально обходит весь объект JSON от его корневого узла, преобразовывая каждый элемент один за другим в файл Excel экономичным методом.
В случае неправильного преобразования или проблемы с форматом вашего выходного файла вы можете попросить их переделать его для вас! Предположим, ваш ввод состоит из объектов или массивов, отличных от строковых значений, которые не ожидаются в списке массивов (поскольку они не могут быть преобразованы), таких как экземпляры Date-Time и т. д. В этом случае эти элементы преобразуются обратно в строку. Итак, убедитесь, что любые входные данные, которые вы предоставляете, могут быть превращены в это дело!
Aspose
Aspose это удобный инструмент для преобразования документов JSON в Excel в формате XLSX или CSV. Он имеет различные функции, которые могут быть полезны при преобразовании, такие как скорость преобразования, форматирование даты и времени, форматирование ячеек, экспорт HTML и копирование ячеек с одного листа на другой.
Быстро конвертируйте несколько файлов JSON в нужный формат — PDF, XLSX, DOCX, PPTX, ODS и другие. Загружайте файлы по запросу с максимальным количеством конверсий 10 одновременно!
ЦДКМ — это бесплатный сайт, который конвертирует файлы JSON в документы Excel. Вы можете ввести URL-адрес вашего файла JSON или напрямую загрузить его с локального компьютера, и вы сможете загрузить текстовый файл или документ Excel. Этот веб-сайт имеет множество других функций, включая форматы вывода, такие как CSV, XML и HTML.
ТекстСравнить
ТекстСравнить это инструмент, который сравнивает две строки. Если вы введете два разных файла JSON, он сообщит вам, насколько они похожи. Типы данных для обоих файлов должны быть одинаковыми, а количество объектов в каждом файле должно быть одинаковым.
Это бесплатное онлайн-программное обеспечение, которое может просматривать и преобразовывать файл JSON в документ Excel. Вы можете ввести JSON в поле ввода текста или загрузить файл. Затем, если вы хотите продолжить и преобразовать файл в документ Excel, выберите параметры синтаксического анализа.
Нажмите кнопку «Показать вывод», чтобы просмотреть файл JSON перед его преобразованием. Таким образом, у вас будет возможность увидеть, как будет выглядеть продукт. Загружайте при загрузке, чтобы получить файл Excel.
Преобразовать
Преобразовать — это бесплатное онлайн-программное обеспечение, которое может просматривать и преобразовывать файл JSON в документ Excel. Вот шаги для преобразования файлов JSON в Excel с помощью Aconvert-
- Нажмите кнопку «Выбрать файлы», чтобы выбрать несколько файлов на вашем компьютере, или нажмите кнопку раскрывающегося списка, чтобы выбрать онлайн-файл из URL, Google Диска или Dropbox.
- Преобразование целевых файлов из определенных форматов документов: поддерживается DOCX, XLSX и ODP. Например, он может конвертировать DOC в DOCX, но не может конвертировать ODT в PPT.
- Выберите целевой формат документа, щелкнув соответствующий значок в левом нижнем углу экрана.
- Нажмите «Конвертировать сейчас!» кнопку, чтобы начать пакетное преобразование.
ТаблицаКонвертировать
ТаблицаКонвертировать это самый простой способ преобразовать файл JSON в Excel. Он взимает небольшую плату за свои услуги, но он прост в использовании и отлично справляется с преобразованием вашего файла без каких-либо проблем.
Ссылка открывает TableConvert, но вы также можете использовать этот сайт для быстрой конвертации. Этот веб-сайт не такой быстрый, как TableConvert, но по-прежнему прост в использовании и отлично справляется с выбором ячеек в вашем документе, на которые будут влиять данные из вашего файла JSON.
Этот сайт легко и быстро преобразует файлы JSON в документы Excel, поскольку использует XSLT (преобразование таблиц стилей XML). Если вам нужно что-то, что не так просто в использовании или не преобразует ваши данные таким же образом, это решение для вас, потому что оно быстро преобразует вашу информацию!
Чтобы начать, нажмите «Загрузить» или «Вставьте свою ссылку или данные в один файл». После этого перейдите к шагу 2, где вы можете редактировать онлайн по мере необходимости в редакторе таблиц; после завершения пришло время для шага 3! Вкладки отделяют сгенерированный файл Excel — скопируйте и вставьте в Microsoft Excel или загрузите в виде файла XLS из раскрывающегося меню в нижней части страницы.
BeautifyИнструменты Конвертер Excel в JSON позволяет легко преобразовать любой файл JSON в электронную таблицу Excel всего за несколько кликов! Они предлагают три пакета по цене от 9,99 до 19,99 долларов, а затем надстройки для ваших потребностей в конверсии.
CoolUtils
CoolUtils веб-сайт преобразует файл JSON в документ Excel. Он прост в использовании и не требует каких-либо знаний в области кодирования.
Подведение итогов!
JSON — это формат обмена данными, который предлагает простой, быстрый и надежный способ хранения структурированных данных. Он идеально подходит для больших объемов данных и может использоваться во многих сценариях, например при перемещении из одной базы данных в другую.
Excel широко распространен среди пользователей благодаря своему потрясающему интерфейсу и простоте повседневного использования. Вышеупомянутые удобные инструменты могут помочь преобразовать ваши файлы JSON в электронную таблицу Excel. Эти инструменты сделают всю тяжелую работу, чтобы вы сосредоточились на главном – на результате!
JSON to HTML Table
This is a simple script to convert JSON data to standard HTML table in the simplest and fastest way.
How to use
There’s only one function in this library and accept four parameter that only the first one is required.
Simply call ConvertJsonToTable method and fill the parsedJson parameter.
Example
This is an example of using this library:
Code sample to create a HTML table from JSON:
Code sample explaned:
- First parameter is JSON data
- table HTML id attribute will be jsonTable
- table HTML class attribute will not be added
- Download text will be displayed instead of the link itself
Contributors
Contributing
This is a open-source project. Fork the project, complete the code and send pull request.