1 2 Previous Next 19 Replies Latest reply on Mar 30, 2011 9:28 AM by philmodjunk

    Importing records into existing

    brett_

      Title

      Importing records into existing

      Post

      I have a CSV file that I'd like to import.  It has dates and several other fields that I can match against fields in FileMaker.  I made a copy of my database to try this out.  Unfortunately, it didn't match dates.  So, rather than filling in the empty fields in existing records, it just adds new records with only the imported values.

      Is there a way to have the import match against dates and modify/append to existing records?  I want the import to fill in matching fields on existing records.

        • 1. Re: Importing records into existing
          Kays

          hey brett,

          check if the date field is a "date" type in both filemaker and the csv file, and check also the date format cuz dd/mm/yy is != than mm/dd/yy.

           

          K|Z 

          • 2. Re: Importing records into existing
            brett_

            Thanks.  I've set it to update matching records and to match against the date field.  The date field format in both is mm/dd/yyyy without leading zeros.  But it doesn't import anything.  Any ideas what I am doing wrong?

            • 3. Re: Importing records into existing
              Kays

              okiii,

              maybe the problem is whith the date format, cuz when u type it manually in a field u typed as dd/mm/yy so try to change the format to that in your isv file.

              K|Z

               

              • 4. Re: Importing records into existing
                brett_

                I'm not following what you mean.  Both formats are the same.

                • 5. Re: Importing records into existing
                  philmodjunk

                  In Manage | Database | Fields, is your date field of type date or of type text?

                  • 6. Re: Importing records into existing
                    Kays

                    in general, if u wanna type a date in an empty field u typed it as dd/mm/yy ( like 30/03/2011 ) right !!

                    the format mm/dd/yy is only used in scripts ( if u wanna create a date format in a script) right !!

                    so if u wanna import an excel file for example ( or an csv file in ur case ), so date format in the isv file (excel file) must be dd/mm/yy and not mm/dd/yy.

                    so in ur csv file the date format must be dd/mm/yy.

                    try it :)

                     

                    K|Z    

                    • 7. Re: Importing records into existing
                      brett_

                      Date.

                      • 8. Re: Importing records into existing
                        brett_

                        in general, if u wanna type a date in an empty field u typed it as dd/mm/yy ( like 30/03/2011 ) right !!

                        No. I type the full year.  Even if I only type a two digit year, it will change it to four digits.

                        the format mm/dd/yy is only used in scripts ( if u wanna create a date format in a script) right !!

                        I don't use scripts.

                        so in ur isv file the date format must be dd/mm/yy.

                        What is an isv file?

                        I don't think the your scenario applies to me.

                        • 9. Re: Importing records into existing
                          Kays

                          sorry meant CSV ( my mistake)

                          u said :

                          "The date field format in both is mm/dd/yyyy without leading zeros"

                          In an import, filemaker enters dates as "dd/mm/year" ( yy or yyyy does not matter ) and not "mm/dd/year", so try to change the date format to dd/mm/year in ur CSV file.

                          K|Z

                           

                          • 10. Re: Importing records into existing
                            philmodjunk

                            In an import, filemaker enters dates as "dd/mm/year"

                            Actually, that depends on your system settings and whether you have set your File to use the System Settings. In the US, the default format is MM/DD/YYYY. These are all formats applied to display an internal integer as a date and to translate data that is input in a date format into said integer. The actual integer stored is the number of days elapsed since an early arbitrary date which is why we can add and subtract dates in calculations.

                            After you import and get your data in new records, if you perform a find for one of the dates imported that should have matched, do you get the two records in your found set?

                            • 11. Re: Importing records into existing
                              brett_

                              ...do you get the two records in your found set?

                              No - it did not import anything.

                              My file is set to use system settings, which is US and like the default you have above.

                              Is it possible to have FileMaker output some type of diagnostic file so I can see what the comparisons look like?

                              • 12. Re: Importing records into existing
                                philmodjunk

                                But you said:

                                "So, rather than filling in the empty fields in existing records, it just adds new records with only the imported values."

                                You should be able to import the data. It should import either with the matching records, add non matching as new (I forget the exact wording here) options selected or just by importing the data without any matching which will create new records for all.

                                Such a diagnostic test is what I'm after here. I want to see if the data in the CSV file's date column is successfully being intrepreted both as a date and as the correct date. It's not the formal, built in test you are looking for, but that kind of test isn't available to us.

                                • 13. Re: Importing records into existing
                                  brett_

                                  I did another import and selected to only add records.  Date was selected as a field to import.  But I don't see any values for the date field.  The other fields where imported.  They are just number fields.

                                  • 14. Re: Importing records into existing
                                    brett_

                                    Ok - the my last post is incorrect.  I had the CSV file date field pairing with a Date Created field, which FileMaker generated when I created the table.  Pairing the CSV date field with the correct date file in FileMaker does import dates.  The imported dates look just like the dates I already have in the database.

                                    1 2 Previous Next