Our Preferred Hosting Provider - Award Wining FileMaker Pro Hosting
Results 1 to 4 of 4
  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    7
    Downloads
    0
    Uploads
    0

    Large import from excel with unique id questions...

    I'm fairly new to FMP and I am running into a couple problems setting up my new solution. I've been working through the VTC tutorials and I guess I am not far enough yet.

    I have a large amount of scraped data broken down into 3 categories - companies, contacts and projects essentially. I had a unique ID generated during the scrape for each project, but not for the contacts of the companies. Unfortunately, Im dealing with a huge number of duplicate companies and contacts dues to the raw dump of data. I was planning on validating the data during import, generating a serial number for both the contacts and the companies so I could use the projects table as a join table and a many to many setup. I couldn't figure out how to merge the duplicates during import by validation and to save the project id reference from the deleted company record to the merged company record.

    Any direction would be greatly appreciated.

    Bryan
    Warning: This is an Old Thread
    This discussion is older than 420 days. information contained in it may no longer be current

  2. #2
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    5,972
    Location
    netherlands
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer, FM 12 Server, Older Versions 2-8
    OS
    Windows & Mac
    Skill Level
    Developer
    Downloads
    0
    Uploads
    0

    Re: Large import from excel with unique id questions...

    how to merge the duplicates during import by validation
    Not sure if that's even possible automatically.

    Importing the duplicate data and then removing the duplicates is possible however using a script.
    If you have a big flat file import of an excel sheet, then I think I'd create an import table that holds all values.
    then:
    - prewash the fields using the TrimAll() function to remove extraneous spaces
    - create a temp field concatenating for each table type (contacts = first name + last name + company name for instance) to identify unique records
    - sort by that field
    - You should consider manually checking your data because in the raw data, typos may be abundant and prevent you from recognising two contacts as identical (f.i. John Brown and John Brwon).
    - Then loop through the records, comparing the temp field with the previous field and setting the serial ID for the target table +1 if necessary
    - Repeat this for the other target tables
    - assuming all ecxel rows are unique projects, import the temp table as a whole into the projects table including the newly created serial IDs as foreign keys for contacts and companies (only the relevant fields).
    - then import same into contact table and company table (only the relevant fields).
    - then run a 'remove duplicates' script over Contacts and Companies tables based on serial ID. I think there are several examples of such scripts on this site.
    - remember to import the Company ID into the Contacts table also.

    If you need more help, please post again.
    Stick to what you don't know.

    http://www.maartenwitberg.nl


    -------

  3. #3
    Junior Member
    Join Date
    Apr 2012
    Posts
    7
    Downloads
    0
    Uploads
    0

    Re: Large import from excel with unique id questions...

    Maarten,

    Thanks for the response. I feared that was the case. I will attempt to work through your steps although Ill be honest, I'm not as versed in FMP as I'd like to be so it will take me some time.

    What shall I do about the project ID's that are attached to the duplicate entries? A project may have 1-5 companies associated with it but some of the companies have close to 150 projects in progress. ABC TV for instance has only one mailing address but 134 projects so I need to make sure the project associations still exist after I merge the contact entries. Do repeating fields work for this? Should I import a full table with all the project id's and duplicate companies to act as a reference table?

    Here is what I'm referring to:
    https://www.box.com/s/ab24c4294afc62675469

  4. #4
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    5,972
    Location
    netherlands
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer, FM 12 Server, Older Versions 2-8
    OS
    Windows & Mac
    Skill Level
    Developer
    Downloads
    0
    Uploads
    0

    Re: Large import from excel with unique id questions...

    What shall I do about the project ID's that are attached to the duplicate entries? A project may have 1-5 companies associated with it but some of the companies have close to 150 projects in progress.
    Oh I misunderstood that. Excuse me for reading over the reference to a many-to-many setup you made in the first post. But your join table is not going to be the projects table. There should be a separate join table holding company IDs and project IDs. So you would have to do the procedure of finding the duplicate projects also, and import stuff into the join table also before removing the duplicate projects.

    Hope that helps. And I am assuming contacts are linked to companies and not directly to projects? Or is that necessary also? f.i. if different contacts of the same company are related to different projects? Then you need another join table for that I guess.
    Stick to what you don't know.

    http://www.maartenwitberg.nl


    -------

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pre-import data questions.
    By bdpla in forum FileMaker Pro 11
    Replies: 5
    Last Post: 05-08-2012, 12:08 AM
  2. Importing Data from Excel with unique ID
    By jmille44@mac.com in forum FileMaker 11 Advanced
    Replies: 3
    Last Post: 08-02-2011, 09:15 PM
  3. Unique Import
    By drsmith in forum FileMaker Pro 10
    Replies: 1
    Last Post: 11-16-2009, 11:35 AM
  4. Import DATE from Filename of the Import Data Excel File
    By barryfjweber in forum FileMaker Pro 10
    Replies: 5
    Last Post: 06-26-2009, 04:04 PM
  5. Best way to import large record?
    By barbapapa04 in forum Exporting and Importing
    Replies: 2
    Last Post: 02-18-2006, 09:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a free-to-join community where you can boost your FileMaker expertise, build better solutions and solve your technical challenges. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.