Hi - just getting into Airtable. I’ve got a spreadsheet with book details that includes authors in a field. I have them in inverted commas ie “Last Name, First Name”. This is so that I can then take the field and link to another record to create a field in an Author table where the authors appear as a record with “Last Name, First Name” in the field. However, once the spreadsheet gets into Airtable (whether pasting or importing) the inverted commas have vanished from sight and so when I link to another record each author creates two records, one with “Last Name” and one with “First Name”.
Is there any workaround for this? I’ve seen the help page about preparing text but that requires adding inverted commas around every author in every record after importing which will be a massive task.
The only solution I’ve come up with so far is to have authors as “Last Name/First Name” or “Last Name-First Name” in the spreadsheet as then there is no comma delimiter problem in linking to another record. Not great so grateful for any wisdom. Can’t believe nobody else has wanted to be able to do something like this!
Thanks, Andrew
Pasting or importing text with inverted commas into a "link to another record" field
It sounds like the problem is occurring when Airtable attempts to use the Author Field of your imported books table to Authors having matching names. If you have only one Author per book you can try disabling “Allow linking to multiple records” when you set up the table or convert the text column to a link. If that doesn’t work for you, try surrounding the author names by inverted commas to prevent Airtable from interpreting the inner comma as a separator between record names. The raw CSV output should look like this:
Title, Author, …
“Book with two authors”,""“Doe, John”","“Doe, Jane”"", …
It sounds like the problem is occurring when Airtable attempts to use the Author Field of your imported books table to Authors having matching names. If you have only one Author per book you can try disabling “Allow linking to multiple records” when you set up the table or convert the text column to a link. If that doesn’t work for you, try surrounding the author names by inverted commas to prevent Airtable from interpreting the inner comma as a separator between record names. The raw CSV output should look like this:
Title, Author, …
“Book with two authors”,""“Doe, John”","“Doe, Jane”"", …
Many thanks Brian. Although the problem wasn’t matching names, your answer made me realise what might be the problem! I had the data in Excel and was cutting and pasting or importing from that and the inverted commas I had managed to insert into each record in the Author field were being removed. When I saw your reference to “raw CSV outpout” I realised I had not saved the spreadsheet as CSV so did that. It looked exactly the same in Excel as before but then when imported or cut and pasted into Airtable the inverted commas remained and the separation of authors worked. Really appreciate your help.
+4I know that this thread is 5 years old, but Brian you just saved me hours of data cleaning with this tip. Thank you!!
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.