Forum Discussion

GrahamHaskin's avatar
GrahamHaskin
Qrew Trainee
4 years ago

Deleting Non-Matching Records when Importing Excel File

I have a table that is filled with the data for all users in a particular system.
That system comes from another department, and they provide me with that user data in the form of an Excel file (we're talking over 6,000 users, for context)
The issue is that users leave, and so their "line" in the provided Excel file also leaves.
Is there a way that, when I upload the Excel file to my existing table, if an existing table record isn't matched to an Excel line, the table record gets deleted?
I could just wipe the table of all its data periodically and upload fresh, but I'm hoping there's an easier way.
Thank you in advance.

------------------------------
Graham Haskin
------------------------------
Data import and export
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    Is there a unique identifier like an Employee ID# in the file?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • GrahamHaskin's avatar
      GrahamHaskin
      Qrew Trainee
      4 years ago
      Yes, they all have a unique ID number, and it's currently set up as the key record field.

      ------------------------------
      Graham Haskin
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        Hey so what I do in the situation is to create a copy of the target table so that all the field names match exactly. There is a function when you were sitting on the table homepage to copy this table.  Name that Table Import Scratchpad.

        Tentatively, if your field names in your CSV import do not perfectly match the field names in QuickBase then create the scratchpad table by importing the CSV and letting it create the fields.

        Import the data into that table.

        Make a relationship between the scratch pad table and your life table so that one live record has many scratchpad imports. 

        That allows you to determine for the live records whether or not they have data in the scratch pad import set.

        I typically then create a Helper table with a single record in it. It will be record ID number one. Then I make summary fields and buttons there to be able to have a URL formula button to purge out any live records where the import set does not contain that record.  

        So then the process would be to have a button on the helper record to clear the scratchpad. Then another button to purge the live records which need to be deleted, and of course he would show account for a sanity check of how many records you're about to delete, and then another button to import the users from the scratch pad into the live table. You can also have a sanity check summary total on that helper table record to show the number of new users who will be created.


        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------