Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 25, 2022 10:53 AM
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!
Solved! Go to Solution.
Aug 25, 2022 07:13 PM
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:
Aug 25, 2022 11:43 AM
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:
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.
Aug 25, 2022 12:14 PM
Wow, thank you!! I have a feeling this will take me a little while to dig through. Very much appreciated!
Aug 25, 2022 07:13 PM
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:
Aug 25, 2022 08:10 PM
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:
Aug 25, 2022 08:12 PM
That’s great to hear! :grinning_face_with_big_eyes: :raised_hands:
Aug 26, 2022 11:05 AM
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.
Aug 26, 2022 02:43 PM
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: