4 Replies Latest reply on Aug 26, 2009 11:33 AM by ACOPcharity

    Importing two excel databases as one merged filemaker database?



      Importing two excel databases as one merged filemaker database?


      Hello Everyone,


      I am trying to import two different excel files into filemaker as one database. Basically it is two different excell files with information about students in our music program. One file was cretaed through a on-line system that we use and the other from access. They do not correlate perfectly since one has many more studen records than the other. the thing is that one has for example parent information that the other does not and the other has class information that the other does not. So basically I would like to merge both into filemaker to get the most ouf of them. How could I go about doing that. I already imported one, I was thinking of trying to import the other one and maybe there is a way to have filemaker recognize duplicates based on student names and then just adding the information that is missing inot the existing record and creating new records if they are already not created. Am I dreaming here? Is it even possible?

      P.S. Keep in mind the two excel lists do not correlate perfectly such as Adam is in line 1, Ana in line 2 sort of thing, since one has more or other students than the other so I couldn'ty just copy and paste collums into one file or would not even want to macth them manually since there is almost 2,000 records.



      Thank you

        • 1. Re: Importing two excel databases as one merged filemaker database?

          You have a lot of different potential issues here and some may only be solvable through human intervention and with that many records that's a huge job.


          First you have to get your data into filemaker. You can perform two imports that import the data into the same filemaker table. That may or may not be practical depending on the way the data is stored in your excel files. In order for this to work, you have to be able to map each column of both spread sheet files to a matching field in the filemaker table. After you import the data, there are a large number of ways you can use finds and sorts--both manually and in a script to find and resolve duplicate entry issues.


          You can also import the data into separate tables and then merge the data into a third table with some type of script that helps merge your data and eliminate duplicates. Either method can work it just depends on how similar your two files are in format and structure--as well as how "clean" the data is.


          An issue you'll need to look out for with a data set this large: "How do you tell if two records for "John Smith" refer to the same person or two people that have the same name? If you have some kind of ID number, then it's easy. If not, then you may have to examine a great number of other fields to figure out which case is correct--this is where human intervention may be needed to identify duplicates.

          • 2. Re: Importing two excel databases as one merged filemaker database?

            Thanks PhilModJunk! Yes, the excell file is setup in a way that i can import by matching excell colums to matching filemaker fieldsw. Also, there is a number that identifies the child so we can know if it is the same or not based on that number rather than just basing ourselves on the name. However, for what I gathered one of the solutions would be creating two diferent tables and then using a script to merge and delete duplicates correct? Does that involve complictaed scripting? As of now just thinking of it I must say I think I don;t have enough knowledge for that? Would that be the only way or is there a way to do this without usind scripts? Otherwise I feel that without the script for deleting duplicates we could do it manually by sorting by name and then looking at the student number to make sure it is a duplicate. But I am still not sure I would be able to merge both without complictaed scripts in a way that for example the parent info in one excell list would merge into the same record of the student of the other excel list which has classroom info so then I would have one record for the student with both information in filemaker.  Would I be forced to put both imports and then tranfer one type of intfo into the other record and then delete the one I just copied from to dlete duplicates?

            P.S. I apologize in advance if I am slow in getting this, but I must confess I am a real beginner at filemaker but I am doing my best! :)



            • 3. Re: Importing two excel databases as one merged filemaker database?

              Sometimes you have to ask a few questions before you can really suggest a solution.


              It doesn't sound like you need to import into two separate tables.


              If you import all your data into one table you could do the following manually:

              enter find mode and put an ! in the student id field. Click perform find and you'll get all the records with duplicate entries in this field.

              Sort the found set by this student id field. Now you've grouped your records so that if you view the records with a list or table view where each record is a single row of data, you will see records with duplicate ID's clustered together. You can now compare fields and use drag and drop to copy data from one record to the matching field in another record. This may be all you need to do.


              You can also create a script to do this where the script pulls up the same kind of sorted found set and then loops through the record copying data from other records with the same student ID number to fill in blanks and then deletes the unecessary duplicate records. Whether you can make this work or not will depend on how much effort you want to put into developing a script.

              • 4. Re: Importing two excel databases as one merged filemaker database?
                   Thanks PhilModJunk!!! I believe that the first solution will be the one for us!!! Sounds like something I could actually do :) I will try it this week. Thanks!