Processing data, splitting it into multiple tables, breaking apart cells, and tying it all back together with a reference field

[John_Krueger1]

Hey Airtable community!

I’ve got a quite large data set that is not very pretty. I need to process it so that I can make use of the data in a real way.

The key issue I am looking to solve is that I need to:

Break apart long data fields into individual records on a different table that are linked back to the original record

ex: Master Table Column is “Locations” and in each record there are a list of up to 10-20 locations listed in a format like this:

[{“addr_1”:“3340 Peachtree Road”,“city”:“Atlanta”,“state_prov”:“Georgia”,“country”:“United Kingdom”,“is_hq”:false},{“addr_1”:“Regus Officecenter Badenerstrasse 47 Zürich +41 79 88 44 998”,“city”:“Zurich”,“state_prov”:“NA”,“country”:“Switzerland”,“is_hq”:false},{“addr_1”:“WeWork Friedrichstraße 76 +49 30 5200 4596 4”,“city”:“Berlin-Mitte”,“state_prov”:“Berlin”,“country”:“Germany”,“is_hq”:false}]

I want to export this data into a Table called “Locations” where each address is cleaned up, listed individually and linked back to the original record.

Does anyone have any suggestions for extensions/tools/scripts that could be used for something like this?

Thanks in advance!

[Kamille_Parks]

John_Krueger1:

[{“addr_1”:“3340 Peachtree Road”,“city”:“Atlanta”,“state_prov”:“Georgia”,“country”:“United Kingdom”,“is_hq”:false},{“addr_1”:“Regus Officecenter Badenerstrasse 47 Zürich +41 79 88 44 998”,“city”:“Zurich”,“state_prov”:“NA”,“country”:“Switzerland”,“is_hq”:false},{“addr_1”:“WeWork Friedrichstraße 76 +49 30 5200 4596 4”,“city”:“Berlin-Mitte”,“state_prov”:“Berlin”,“country”:“Germany”,“is_hq”:false}]

This format is called JSON. In this specific case, it is an array of JSON values.

To get this into another table, I would recommend using a script and/or automation. If your data comes in with curly quotes like shown in your post, use a formula field to fix them. If your quotes in your data are straight you don’t need the formula.

REGEX_REPLACE({JSON Field}, "(“|”)", '"')

You can use a script like this one assuming:

1. The names of all the columns in your Locations table exactly match the properties of your objects (addr_1, city, state_prov, …)
2. All fields are text fields, except your boolean values (in this case, “is_hq”) which should be checkboxes
3. Your Locations table has a Link to Another Record field pointing at your original table and that field is named “Link to Original Table”
4. You want to click a button on each record to convert that record’s JSON into several linked records in the Locations table.
5. New records should always be created instead of merging duplicates where they may occur

let table1 = base.getTable("Name of Original Table")
let record = await input.recordAsync("Select record", table1)

let JSONtext = record.getCellValueAsString("Name of Field with JSON in it")

let JSONparsed = JSON.parse(JSONtext)

let updates = JSONparsed.map(x => {
    return {fields: {...x, "Link to Original Table": [{id: record.id}]}}
})

let table2 = base.getTable("Locations")

while (updates.length > 0) {
    await table2.createRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

The script would need to be adjusted if you want to (a) process the entire original table (or a view in your original table) in one go instead of record-by-record, or (b) run the script inside an automation.

[John_Krueger1]

Wow, thank you!! I have a feeling this will take me a little while to dig through. Very much appreciated!

[ScottWorld · Accepted solution]

@John_Krueger1

As the non-scripter of the group, I have to take a different approach that doesn’t involve writing JavaScript code, so I parse all of my JSON with Make.

With just one simple step — the Parse JSON module — you can see in the screenshot below that Make has taken your sample JSON data as the input, and for the output, it has split it up into 3 bundles (i.e. 3 records) which you can then have it automatically add into Airtable:

[John_Krueger1]

Oh wow! This is perfect!! As a non-scripter/coder myself, I was envisioning something exactly like this. Thank you so much :slightly_smiling_face: This seems like it’ll be perfect for my use case since I’m already using Make as the automation chain and adding this layer should fit in perfectly :grinning_face_with_big_eyes:

[ScottWorld]

That’s great to hear! :grinning_face_with_big_eyes: :raised_hands:

[John_Krueger1]

This is working great for about half my columns, thanks Scott!

One follow up question:

I have some data that is formatted slightly differently, example:

[“Data & Analytics Consulting Competency”,“Machine Learning Consulting Competency”,“DevOps Consulting Competency”,“Financial Services Consulting Competency”,“Migration Consulting Competency”,“Digital Customer Experience Consulting Competency”]

The Parsing tool is picking each list item up as a bundle, but I am unable to link them to Airtable in the following step. I know this is probably approaching more of a “Make” help thread, but I appreciate any input.

[ScottWorld]

Are you trying to combine them all together as an array to dump them together into one Airtable field?

There are many different ways of doing this, but the easiest way is to probably just use the Array Aggregator to combine them all into a comma-separated array that you can dump into an Airtable field.

p.s. If your business needs ongoing professional Airtable/Make technical support, and you have a budget to hire an expert Airtable/Make consultant, please feel free to contact me through my website:

Airtable consulting — ScottWorld

ScottWorld • Best Airtable Consultants, Airtable Experts, Airtable Consulting,...

ScottWorld • Best Airtable Consultants, Airtable Experts, Airtable...

ScottWorld provides the world's best Airtable consultants, expert Airtable training, best Airtable consulting, and Claris FileMaker Pro developers.