1 Reply Latest reply on Oct 10, 2012 9:49 AM by Mike_Mitchell

    Problems importing selective data from CSV file

    kelpgriffin

      I'm new to FileMaker Pro, and am using version 11.0v4. My organization hosts high volume contact data. We essentially update our database on an ongoing basis as attendees of our events provide us with their contact info. My goal is to import CSV or Excel files containing new pieces of the attendees' information. HOW can I import selective new data without overwriting the entire FileMaker Contact Record? This is important because many attendees do not provide us with ALL of their data every time they give us SOME data (At one event, they may provide us with First Name, Last Name, Phone Number, Address, etc., while at the next event they may only provide us with First Name and Last Name.) If we were to simply overwrite the pre-existing contact record with incoming data, much data would be lost...

        • 1. Re: Problems importing selective data from CSV file
          Mike_Mitchell

          Hello, kelpgriffin.

           

          You've a bit of a thorny problem here. (As you've no doubt noticed.)  

           

          FileMaker provides for an import using "update matching", which is termed an "update - append". That means you import the data based on one or more matching fields. Incoming data overwrite whatever fields you want in records where the selected key field or fields match; records that have no match can, if you choose, create new records.

           

          The basic problem is that you have no identifiable unique key in your incoming data. You have data provided by people at events, essentially with no data validation. That's a mess. You really have only a few solutions that I can think of offhand:

           

          1) Try to match on First and Last Name. Risky. What happens if you have more than one John Smith? You'll end up overwriting John Smith A with data from John Smith B. Bad. Or what if you have the same guy named John Smith in your database, but he shows up as Johnny Smith at the event? You wind up with a duplicate record. Equally bad.

           

          2) Import the incoming data into a scratch table (i.e., an "in between" table) and then run a script to identify potential duplicates, based on First and Last Name. Better. You can have a screen that compares existing data to incoming data (using a relationship) and allow the user to choose which data to retain. Then run a script that sets the database to the chosen data. Downside: Time-consuming for the user.

           

          3) Try to match on as many fields as you have. This is a little better than 1, since you tighten the requirements for a match. However, it virtually requires a manual reconciliation because you'll have to decide whether a John Smith who lives at 123 Main Street is the same as a John Smith who lives at 456 Lonely Way. Same guy with a new address, or two John Smiths?

           

          In any case, you'll definitely want to perform some validations on the incoming data - stripping out leading / trailing carriage returns, stray characters, etc., to make sure your matches work properly.

           

          Maybe someone else here has a better idea. Good luck!

           

          Mike