Converting Multiple Dates in a Single CSV Cell Before or After Import

128 1
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

I have a CSV file delivered weekly with information about grant opportunities. I've been importing this file into Airtable, however, many of these grants include multiple submission deadlines throughout the year so I'm struggling to find the best way to be able to use the submission deadline as a true date field for sortability.

The CSV file has a text-formatted column for dates. The cells also include "Submission: " before each date. I was planning on find/replacing out the "Submission: " text and then formatting the text as a date, but some of these cells actually contain multiple entries. For example:

Grant 1Submission: November 15, 2024
Grant 2Submission: September 01, 2024
Submission: March 01, 2025
Grant 3Submission: August 15, 2024
Submission: January 01, 2025
Submission: April 15, 2025

Question 1: What is the best way to handle a record that has multiple "due dates"? Should I try to create a new Table and make each date its own linked record? Should I manually duplicate each grant record so I can have separate records for each deadline?

Question 2: What is the best workflow with the least number of steps to get this into Airtable and formatted properly? I have no control over the output of the CSV file so I'll likely have to do some editing in Excel before importing, that's fine. I'm also happy with using the CSV import feature - no need to fully automate this or bring in a third-party application if I don't have to.

1 Reply 1
5 - Automation Enthusiast
5 - Automation Enthusiast

For 1) Do you need to store the previous dates or only the most recent one? As far as how the table is set up, you could a) create multiple date columns per record (first submission, second submission, etc) as long as this is a manageable set of dates and you're comfortable working with them that way or b) (my preference) is to do what you're saying about making a new record for each submission, and then depending on what you need from this data, I'd want to link them to a second table, one record per grant. 
2) If you're totally ok with manual editing each time, you could make some excel formulas (or try text to columns if there is a newline character you can split on) and then it could be as simple as copy-pasting new lines to your submissions table if the columns are in the same order as the grid view you're pasting into.