1 Reply Latest reply on Jul 17, 2012 2:02 PM by philmodjunk

    Bringing Excel data in



      Bringing Excel data in


      Hi, I was given some excel sheets that have different data, ie. names and email addresses, names and phone numbers, names and company, names and addresses, and they have different names and numbers of entries in each Excel sheet. I want to add them to a FileMaker Pro contact database I've created, but wonder how to get a ID key field into them that will allow merging on data where possible. There is no key fields now. It's seems rather painful to try and manually match and enter a key field to these Excel sheets, so I'm looking to see if anyone has an answer to do this?

      Also, some users have more than one phone, and in the same field a type of phone, ie. "808-924-5566 Business", I have a separate phone field and category of phone, so each users could have more than one phone/type. I'll need to add these to to the records, so a key ID and a way to bring the phone/category of phone over is needed.

      Thanks - L

        • 1. Re: Bringing Excel data in

          It would seem the only way you can match up any of the data in one excel worksheet to another is by name. If this name data is not perfectly entered (some sheets spell the name differently, more than one person with same name etc., you will have problems with this data. Assuming that you have good data to work with, you can start out with using names as the key for matching records and then, after data is imported and linked, you can transition to using ID numbers in place of names.

          For data like your phone number issue, the linking process is the same, but you'll need to use either an auto-enter calculation during import or a replace field contents operation (with the same calculation) after import to extract th enumber and category data into separate fields. The exact nature of that calculation will depend on how consistently this data is formatted in the work sheet.

          Using just the one example given,

          leftwords ( Numbercategory ; WordCount ( numberCategory ) - 1)

          would extract the phone number and

          Rightwords ( NumberCategory ; 1 )

          would extract the category--but this may not work with all of your data if some cateogires consist of more than one word.