Import Unique Records from CSV

[Aik Pin Ng]

Hi Brendan,

I am trying to import several thousands records from a csv into an existing form. Is there an easy way for check that if the records already exists, do not import? i.e. only import new and unique records?

What’s the easiest way to do this?

Thank you,
Aik Pin

[Brendan]

Hi Aik,

This would work if you had the Tap Forms generated form_record_id field already in your CSV file.

Tap Forms will use that to lookup whether or not the record exists. If it does, it will update it with the current data. If it doesn’t exist, it will insert it.

Thanks,

Brendan

[Aik Pin Ng]

Hi Brenda,

Thanks. Instead of the form_record_id field, can we specify another field instead? E.g. Name, Address

This is because the csv data is from another source and would not have the form_record_id in it.

Thanks,
Aik Pin

[Brendan]

Hi Aik,

No, I’m sorry but that won’t work. Tap Forms looks for the existence of the form_record_id field in the header row so it knows how to look up the same record in the database in order to update it. In your example, it’s possible there could be a duplicate Name and Address values. They’re not guaranteed to be unique whereas the form_record_id is because the value is based upon a Universally Unique Identifier, which means no two generated IDs will ever be the same in the same app on that device.

[scneophyte]

Hi Aik

What if you exported the form_record_id from TF and added it to your CSV file. Then, use a vLookup formula on your main table to see if the record exists. If it does, then the formula should display it on your main table. If if does not, then the field should be blank.

[Aik Pin Ng]

Hi Brendan, ok, thanks.

scneophyte, thanks for the tips. I will go and explore on this.

[Jose Monteiro]

Hi Aik,

I recently had to extract about 40 thousand records from several PDF files.
Then with a little program in Python I built a CSV file and imported it into TapForms.
If you know Python or any suitable language for this task it will be easy for you to:
1. Export all records in the form to a CSV file.
2. Join the two CSV files you have.
3. Remove any duplicates from the joined file.
4. Import the new CSV file into TapForms (perhaps into another form and delete the old one).

If:
– you are not comfortable with programming
– your data is not ‘top secret’
– you can send me a tfarc (or CSV) of your form and your CSV file

I might be able to help you solve your problem with a little Python program, and send you a CSV or tfarc file with all your records with no duplicates.

Jose

[scneophyte]

Thanks, Jose! I used your idea to solve my conditional import issue…just use python to read the file and then write out a new one if certain headers = criterion.

Now, if only I knew Automator well-enough to automate this…but it’s still faster than manually filtering, pasting, etc.

[Jose Monteiro]

Hi scneophyte,

I’m glad that my suggestion could help you.
I do not know how Automator works; someday day I’ll have to read about it.
Anyway I do not know exactly what problem you have to solve.
Perhaps some mix of a shell script and python program could help you getting the automation you need.

Jose

[scneophyte]

Apologies to Aik for taking over their thread

Yes, I have already run it as a shell script but Automator can run shell scripts and I could, theoretically, create a workflow that I could initiate from Numbers: Export current table to CSV then execute the python program to selectively extract records.

Wouldn’t be hard; just don’t have the spare time to devote to learning it.

[Author]

Posts