You can parse (or deconstruct) the contents of a column with text strings that contain JSON or XML. To help illustrate how to use the Parse command, the following sample data starts with JSON and XML data entered as text fields.
For more information on importing JSON and XML, see Import data from external data sources.
JavaScript Object Notation (JSON) is a common data format, and you can import it into Excel.
To transform the SalesPerson column from text strings to a structured Record column:
-
Select the SalesPerson column.
-
Select Transform > Parse > JSON.
-
Select Record to see the values.
-
Select the Expand
Result
Extensible Markup Language (XML) is a common markup and data format, and you can import it into Excel.
To transform the Country column from text strings to Table values.
-
Select the Country column.
-
Select Transform > Parse > XML.
-
Select Table to see the values.
-
Select the Expand
Result
Tip With a Table, you can also select Aggregate to aggregate data in different ways, such as Sum or Count. For more information, see Aggregate data from a column.
