Import Unique Records from CSV

Tap Forms – Organizer Database App for Mac, iPhone, and iPad › Forums › Using Tap Forms › Import Unique Records from CSV

Viewing 9 reply threads
  • Author
    Posts
  • January 26, 2018 at 9:13 PM #27137

    Aik Pin Ng
    Participant

    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

    January 26, 2018 at 9:29 PM #27138

    Brendan
    Keymaster

    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

    January 26, 2018 at 9:36 PM #27139

    Aik Pin Ng
    Participant

    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

    January 27, 2018 at 2:01 AM #27140

    Brendan
    Keymaster

    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.

    January 27, 2018 at 7:20 AM #27144

    scneophyte
    Participant

    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.

    January 27, 2018 at 8:38 PM #27157

    Aik Pin Ng
    Participant

    Hi Brendan, ok, thanks.

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

    January 28, 2018 at 5:59 AM #27168

    Jose Monteiro
    Participant

    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

    January 28, 2018 at 9:56 AM #27174

    scneophyte
    Participant

    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.

    January 29, 2018 at 1:26 AM #27185

    Jose Monteiro
    Participant

    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

    January 29, 2018 at 6:35 AM #27187

    scneophyte
    Participant

    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
Viewing 9 reply threads

You must be logged in to reply to this topic.