1 2 Previous Next 17 Replies Latest reply on May 15, 2016 6:31 PM by Stephen Huston

    Filemaker 14 Import woes

    JimBrear

      I am just starting to implement a major upgrade of our Client Management system which incorporates new requirements needed for reporting services to the Australian Government. I have insisted that as part of the upgrade the clients also upgrade to Filemaker 14 as part of the process. My first imports of a 650,000 record service history file into the new structure (using Filemaker 14 on 3 different computers has resulted in Filemaker  stopping during the process and leaving the files in need of maintenance. Reading the list indicates I may not be the only one who has experience this problem. I have reverted to using Filemaker 13 for the job and it appears to be working OK so far (it takes a couple of days).  This is from a 14.0.05 release and wonder if something is in hand to fix this problem in future versions.

        • 1. Re: Filemaker 14 Import woes

          650,000 records is not very many.

           

          I'm assuming this is FileMaker Windows, correct?

           

          When another forum member had similar problems recently (a FileMaker Windows user), TSGal  looked into this. I'm not sure the status of that import issue investigation by FM Support. That user, as I recall, also tried several different Windows computers, but never tried using a Mac.

           

          From my testing on a mac, however, I created several 16 M row CSV files that imported in about 15 minutes with zero issues. I repeated that multiple times. (On FMP 14, Mac). My experience with FMP14 has also been quite positive. Very few issues, all minor.

           

          You might want to try your solution on the Mac if you haven't already. At least, that way, you might be able to narrow down what is causing the actual problem.

           

          Offer for support: If you create a representative test file using a random data generator (like RANDOMMITE, or similar random data generator) so that no personal (real/actual) information exists in that test file, I would be happy to test a 650,000 import for you on my Mac version of FMPA 14.

           

          HOPE THIS HELPS.

           

          - m

          • 2. Re: Filemaker 14 Import woes
            mikebeargie

            Things I've had to do with CSV's before to make them play nice (not filemaker related):

             

            1) Open the CSV in the text editor and resave it.

            2) Open the CSV in excel and resave it.

            3) Open the CSV with filemaker, let filemaker convert it to .fmp12, and then import from that file.

            4) manually add a header row to a CSV file so filemaker knows how many columns there are.

            5) Use a CSV checker tool (I recommend CSV Lint | Check your CSV files with CSVLint ) to verify that the CSV is even readable.

             

            1 & 2 can help with encoding/character issues.

            3 can help bypass any qualms of trying to import it to a hosted file that may cause issues with record locking and otherwise.

            4 can help if there are malformed rows in the CSV

            5 can help identify if there are malformed rows in the CSV

             

            I have learned to not trust data I am given for its integrity. So maybe check that out as well!

             

            Otherwise, I have had no issues importing large CSV files into filemaker hosted or standalone files in version 14.

            • 3. Re: Filemaker 14 Import woes

              Mike brings up some excellent points.

               

              I regularly have the exact problem (totally reproducible every time) he describes in "2)" above, but with Excel files. If I don't pre-open/save them (no changes to file data or file format), FileMaker has no idea how to import them and nothing happens in my import script! That seems like a bug to me, since I can access them with other programs, like say with .... Excel, but whatever.

               

              Implicit in my posting was to create a generic test file to make sure that works.

               

              Start from simple cases and build up to what you have, methodically.

               

              HOPE THIS HELPS.

               

              - m

              • 4. Re: Filemaker 14 Import woes
                JimBrear

                Hi Morkus

                 

                 

                 

                The three machines I used are running Windows 10.   I am importing from one Filemaker file into another so Mike’s helpful hints re csv do not apply (but will be filed for future reference).

                 

                 

                 

                I do not have a Mac to try this on and as I am on a Monday morning deadline for the upgrade do not time to experiment.

                 

                 

                 

                However, the win 10 machine that crashed twice using Filemaker 14 is now 200,000 records into the remainder of the job using 13 advanced with no hint of a problem.

                 

                 

                 

                I would be interesting to know what changed between versions.  I have never had any import problems in the past except for the occasional power outage, which is a bit hard to blame on Filemaker.

                 

                 

                 

                Best Wishes

                 

                 

                 

                Jim Brear

                 

                COAMAS Services

                 

                PO Box 1542 Armidale NSW 2350

                 

                Mobile Phone: 0429 428 700

                 

                Home Office Phone: (02) 6772 3933

                 

                email: jbrear@coamas.com.au <mailto:jbrear@coamas.com.au>

                 

                Web  http://www.coamas.com.au <http://www.coamas.com.au/

                 

                COAMAS - Community Options, Advocacy, Management and Accounting Software

                • 5. Re: Filemaker 14 Import woes

                  I can't say what changed between version 13 and 14 except that the other user I mentioned also went back to 13 to do the import. The other common denominator between you and the other poster was ... wait for it ... "WINDOWS"

                   

                  However, without a representative file example, I can't try the import on a Mac. The funny thing is that with the Mac, you probably would meet your deadline.

                   

                  In my testing on the Mac and version 14 of FMPA, I wasn't able to have any import problems with files up to 16 GB. 15 minutes the import was done. No hints of issues.

                   

                  --------

                   

                  You should definitely file a problem report with FileMaker since crashing, yes, even on "Windows" (though possibly not surprising), shouldn't happen. Period.

                   

                  Thanks for your reply.

                   

                  - m

                  • 6. Re: Filemaker 14 Import woes
                    beverly

                    had you tried the export as FileMaker for reimport? This could be export from FM13, convert the file (or not) and import into FM14.

                     

                    could there be any restrictions on any file(s) where a particular VERSION is allowed/not? (File Options: Minimum version allowed...)

                    beverly

                    • 7. Re: Filemaker 14 Import woes
                      JimBrear

                      Thanks for the replies everyone.

                       

                       

                       

                      I was able to complete the job by using Filemaker 13 to do the import with no problems.

                       

                       

                       

                      Having some time to tinker this weekend I decided to test the import process on a newer version of Filemaker Advanced than the 14.0.4 I had used before reverting to 13. This is a Filemaker to Filemaker import.

                       

                       

                       

                      Same problem!  A number of hours into the import Filemaker shut down with 11600 records imported.

                       

                       

                       

                      It looks like the problem has not been fixed. 

                       

                       

                       

                      The importing has been tried on 3 different Windows 10 computers, so it is not machine specific.  Unfortunately, I do not have ready access to a Mac which I am assured would do the job. That may be the case, but is irrelevant. The function should just work on either platform.

                       

                       

                       

                      Best Wishes

                       

                       

                       

                      Jim Brear

                       

                      COAMAS Services

                       

                      PO Box 1542 Armidale NSW 2350

                       

                      Mobile Phone: 0429 428 700

                       

                      Home Office Phone: (02) 6772 3933

                       

                      email: jbrear@coamas.com.au <mailto:jbrear@coamas.com.au>

                       

                      Web  http://www.coamas.com.au <http://www.coamas.com.au/

                       

                      COAMAS - Community Options, Advocacy, Management and Accounting Software

                      • 8. Re: Filemaker 14 Import woes

                        If you can post a "clean" copy of the data, I offered to try to import it for you, as a test, on a Mac using the current version of FMPA.

                         

                        The common denominator in all your tests seems to be Windows (and, of course your data).

                         

                        Since the FMP 14/Windows issue you faced has been posted before with no resolution from FM, that I'm aware of, I would like to see what happens on the Mac. In my own testing with 16,000,000 rows and a > 8 GB import file, the Mac had no import issues and did the import in about 15 minutes the two test trials I ran.

                         

                        Of course, you'd have to create a representative test file, but there are free tools like "RANDOMMITE" or Web sites that would help you do that.

                         

                        If you can create a clean (that is, with non personally identifiable info) test file on your machine that crashes, post it here (a zip file would be fine), and I'll try to import it.

                         

                        HOPE THIS HELPS.

                         

                        - m

                        • 9. Re: Filemaker 14 Import woes
                          siplus

                          I would also make a list of all indexed fields, then set indexing to none for all of them before import - but leaving "Automatically create indexes as needed" checked.

                          • 10. Re: Filemaker 14 Import woes
                            Stephen Huston

                            Besides Indexing, there can be issues with valdated fields and stored calculations. Also with any auto-entry fields if allowed to perform as new records.

                             

                            With these options OFF, I have had no issues importing over 10-million records in a single run, usually in well under an hour on FM14.

                             

                            It can also matter how many fields there are in the table(s).

                            • 11. Re: Filemaker 14 Import woes
                              siplus

                              Completely agree, but from experience the indexing is usually the problem, as it seems to be touched at the arrival of each and every record and it involves all the previous records, unlike validation and auto-enter.

                              • 12. Re: Filemaker 14 Import woes
                                Stephen Huston

                                Agree completely about indexing being an obvious slowdown. One only needs to apply indexing to a new field in a large record set to see how long it takes.

                                • 13. Re: Filemaker 14 Import woes
                                  JimBrear

                                  I am still experimenting here.

                                   

                                   

                                   

                                  The file I am importing has 550 fields and the file I am importing from has 630,000 records.

                                   

                                   

                                   

                                  I turned off all the indexing on the receiver file and attempted another import via FM14.

                                   

                                   

                                   

                                  Did it fly. It was importing at 1500 records per minute (compared with 150 with all the indexing on). However Filemaker shut down after 374,700 records.

                                   

                                   

                                   

                                  Fixed the file and retried the import with Filemaker 13.  With no indexing Filemaker 13 importing 180 records per minute. A couple of days later it is still chugging along with 120,000 records still to go.

                                   

                                   

                                   

                                  I am coming to the conclusion this is a Windows Filemaker 14 / 15 issue. It was very disappointing to see the problem persist in the trial version of 15.

                                   

                                   

                                   

                                  I will experiment with the production 15 when the 13 import successfully concludes.

                                   

                                   

                                   

                                  Best Wishes

                                   

                                   

                                   

                                  Jim Brear

                                   

                                  COAMAS Services

                                   

                                  PO Box 1542 Armidale NSW 2350

                                   

                                  Mobile Phone: 0429 428 700

                                   

                                  Home Office Phone: (02) 6772 3933

                                   

                                  email: jbrear@coamas.com.au <mailto:jbrear@coamas.com.au>

                                   

                                  Web  http://www.coamas.com.au <http://www.coamas.com.au/

                                   

                                  COAMAS - Community Options, Advocacy, Management and Accounting Software

                                  • 14. Re: Filemaker 14 Import woes
                                    crw030

                                    Jim.  I unfortunately have not had the opportunity to try a large file with FMP14, but I too have done multi-million row imports on Windows using FMP12 and FMP13 with success.

                                     

                                    I would echo the comments about indexes and also auto-enter calculations (including created/modified/serial).  I would also be VERY suspect of a text file given to me as "tab delimited" or "csv delimited", as more often than not I find that systems which produce the files do not follow the RULES on creating those files with embedded characters etc all the time.

                                     

                                    By chance do you have access to the FM server desktop?  I'm wondering if there might be something with the Filemaker temp files like an out-of-space issue or something happening.  I would also SUSPECT WINDOWS 10, sorry but it's new so it could be a factor.

                                     

                                    I would be happy to import a dummy file onto a Dev server running on Windows to see if i can duplicate the problem.  Can you advise what version of FMS you are running?  I saw you tried both FMP/FMPA 13/14 clients.  Are you doing these imports over WAN to a Filemaker Server?

                                    1 2 Previous Next