Importing CSV linking to multiple records whose names contain commas

[Frederik_Faye]

Say I have a base with two tables, A and B, where the primary field of B (a single line text field with the name B_name) has values B, 1 and B, 2, and where A has a field, B_name, linking to B.

And say I now want to use the CSV import tool to import a single record into table A:

A_name,B_name
A1,"B, 1, B, 2"

When I import this using the CSV import tool, the correct links to B, 1 and B, 2 are not established. How can I either

1. Format the CSV I’m importing, or
2. Configure the import in the CSV import tool

such that the correct links are established?

Thank you!

[TheTimeSavingCo]

Hmm, are we able to change the commas to something else?

E.g. from B, 1, B, 2 to B - 1 - B - 2 or something, with the requisite changes to the data in Table B as well?

When we paste comma separated values into a linked field Airtable treats them as a separate records, so that may be causing the weirdness

[Frederik_Faye]

Hi @Adam_C , thanks for your answer.

The change from , to e.g. ; or - is of course possible, but it is highly undesirable, due to decreasing the readability of the records (which was a key motivator for using Airtable for my client in the first place). Also, just to be sure, you meant

E.g. from "B, 1, B, 2" to "B - 1, B - 2"

right?

And yes, I agree that that parsing strategy is what is causing the weirdness, but I was wondering if there was a way around it. For instance, if I was able to set ; to be the expected separator/delimiter of the CSV Import Tool, that would fix it, but so far, I haven’t found such an option.

[ScottWorld]

@Frederik_Faye

Given what you wrote in your original post, Airtable is importing your data properly. If you put quotes around a value, that will get imported as one value, not multiple values.

It is true that Airtable’s CSV Import tools only support comma-separated text as delimiters. However, even if you changed your delimiter to a semicolon, and used a tool like Make that supports semicolons as a delimiter (see screenshots below), your value would either get imported as 1 value or 4 values, not 2 values.

For people reading this thread in the future, it’s very easy to use other delimiters such as a semicolon ; or a carriage return or a colon : or any other delimiter that you would like when importing a CSV file into Airtable — but the catch is that you would need to use an external tool like Make to handle your CSV importing for you.

You would need to spend a few minutes setting up your Make scenario for importing, but once it’s set, you could just drop your file into a cloud drive like Google Drive, and then Make would parse the file and add the records into Airtable.

See screenshots below for how you can easily set your own delimiter in Make’s Parse CSV module:

[TheTimeSavingCo]

Frederik_Faye:

E.g. from "B, 1, B, 2" to "B - 1, B - 2"

Yeah that’s what I was thinking of

re: the readability thing
Ah understood. Hmm, well, you could possibly separate the data into different columns via formulas? Mainly suggesting this as I don’t think you’re going to find an easy way past the whole “commas treated as new records” thing in linked records, and this is my go to way of handling it heh

You could also do some URL encoding for the data before it gets imported, so you’d end up with "B%2C1,B%2C2", and then use a formula field to convert the %2C’s back into commas after in your second table?

(Edited to remove a mistake I made with the CSV import tool; turns out having spaces between the values breaks the import pretty hard!)

[st_phan]

I just had the same problem when migrating data:
1. We have a table for our inventory (each row is one thing that can/has been bought)
2. We have a separate table for customers
3. Our customer names can contain commas, such as "Dr., John Smith"

We exported one table that contains the inventory and Airtable (almost) correctly creates entries in the customer table based on the column value ... however the Airtable CSV-Import extension seems to ignore quotes around the names (using semicolon as CSV-delimiter also didn't work):

CSV:

1, "Dr., John Smith", 458, "Some other data"

Result:
Gets transformed into two customers: "Dr." and "John Smith"

________

Solution:
We exported the CSV with the commas replaced by semicolons (e.g. "Dr.; John Smith") and replaced it later using an automation script (based on this).