Splitting Data into Multiple Columns

Country of Manufacture is buried in a semi-structured JSON column. Let’s use the split functionality to pull out the data that we want.

  1. Select the StockItems query

  2. Select CustomFields column from StockItems

  3. Investigate the data in the CustomFields column.
    For example, one value looks like this:
    { “CountryOfManufacture”: “China”, “Tags”: [“Radio Control”,“Realistic Sound”,“Vintage”], “MinimumAge”: “10” }

  4. Go to Home Tab > Select Split Column > Select By Delimiter SplitByDelimiter

  5. Split the column by custom delimiter “:” at each occurrence as shown in the screenshot. You will end up with 4 new columns, each split at the colon. The first column will contain “CountryofManufacture”. Split Column by Custom Delimiter

  6. Investigate the data in the column CustomFields.2.
    From our previous record, the value is:
    China, Tags

  7. Split CustomFields.2 column again but this time by comma to isolate the country. Split by Comma

  8. CustomFields.2.1 should have the Country of Manufacture CustomFields.2.1

  9. Select it and clean it up by choosing Add Column Tab > Format > Trim.
    This cleans up extra spaces at the front and back Select Trim

  10. A new column called Trim will appear. Rename it to CountryOfManufacture

  11. Remove all CustomFields Columns, Tags and RowNumber columns from StockItems.
    To do this, Hold Ctrl + Select the previously mentioned columns. Right-click then remove columns Hold Ctrl + Select the previously mentioned columns