7 Replies Latest reply on May 14, 2014 9:51 AM by philmodjunk

    Importing data

    RosieUnderwood

      Title

      Importing data

      Post

           I have two files located on separate servers and I am importing a large amount of data into both from the same excel sheet (the only difference is some columns are used in one file and some in another). I exported both of the filemaker files onto my MAC and imported the data from the excel into both just to check I was doing it correctly- and it worked for both and I had the same number of records in both the filemaker files. However when I have come to try on the server one file has 3 less records and the other 8 less records despite doing the exact same process with the same excel and filemaker files- can anyone explain why this might happen? I have over 10,000 records so its hard to locate the possible records that haven't imported over.

        • 1. Re: Importing data
          philmodjunk

               Did you get any message telling you how many records were not imported and why? Certain validation field options can be set that will keep a specific record from being imported--such as one that specifies only unique values in a particular field.

               Exactly what options did you select for your import? Are you importing only new records or are you trying to set up an "import matching" type of import?

          • 2. Re: Importing data
            RosieUnderwood

                 On the import summary it said for none of the imports that there were any errors just that a different number of records had been imported. 

                 I am using "import matching" and add remaining records - the "individual" is the matching and then importing others e.g. name, date of birth. I worked fine when I was importing onto the exact same files not on the server but when I tried on the server there were either 3 or 8 less records that didn't import.

            • 3. Re: Importing data
              philmodjunk

                   Be careful with Import Matching. It matches to all records currently in your target table's found set. So if you don't have all your records in the found set before you start the import, you will get different results than if you did have all records in the found set at that time.

              • 4. Re: Importing data
                RosieUnderwood

                     Ah right- when you say "found set" you mean if I have said show all, or if only certain records are showing?

                • 5. Re: Importing data
                  philmodjunk

                       Yes. If you do a Show All Records in the target table first, then the matching is done against every record in your table. If you perform a find that does not find all records, then only those records that are found are matched against and this can result in a new record added (with the settings you described) where a record should instead have been updated.

                       And be careful of the table occurrences involved. It's possible to have multiple boxes (table occurrences) in your relationship graph that all refer to the same table. Each will have their own found set. So you need to do your Show All records on a layout based on the same table occurrence as that selected in your Import Records as the target "table".

                       The table occurrence on which a layout is based is the one selected in the "Show Records From" drop down found in Layout Setup...

                  • 6. Re: Importing data
                    RosieUnderwood

                         It still doesn't seem to work, I have a table that already has around 9000 records in but I am trying to add another 2000 ish from an excel file. Both the filemaker and the excel file have the 9000 records in so I am just updating a few of these with dates of birth from the excel and then adding the next 2000- is there a better way to do this rather than having update matching with the individual ID as the matching and importing the additional data?

                    • 7. Re: Importing data
                      philmodjunk

                           It still doesn't seem to work,

                           Sorry, but that doesn't give me any new information to work with. You'll need to figure out exactly how it isn't working. Is it adding new records that should match to existing records? Are some records not being imported at all? Are records being updated that should be added as new records?

                           As I suggested in your other thread, you might need to check the values and data types of your Excel column and FileMaker field to make sure that the values are correctly matching in all cases where they should match.