3 Replies Latest reply on Aug 18, 2015 6:05 PM by wigobucks

    FileMaker Pro Advanced 14 continues to become unresponsive during import of large CSV file

    wigobucks

      Title

      FileMaker Pro Advanced 14 continues to become unresponsive during import of large CSV file

      Post

      FYI:  Copied in part from my post earlier made in FileMaker Community - not sure which site is more active.

      After about a 6 year hiatus from using FileMaker (or doing any programming for that matter), I have been given a new role with my company that involves analyzing large amounts of data.  Since I previously had over 6 years of experience working with FileMaker (4,5 & 6 and even going way back to Nutshell back in late 80s), I decided to tackle this project with FM.  I upgraded to FileMaker Pro Advanced 14 - using Windows on both my laptop and a PC but haven't gotten past this problem just getting the data in my FM file. 

      Because the data has over 9 million records it was provided to me in 2 csv files.  After much time setting up the field mapping to then have the first import crash, I decided to setup the import in a script so i wouldn't have to repeat the field mapping but one more time.  I was able to import most of the data (I have 8.6 million records imported) but my last 4 attempts have ended with the import just hanging - FM eventually is considered unresponsive and I'm forced to abort the script and close FM and try again (after waiting hours) but some data gets added but not all of it.  Of course, I can't tell the import to start at specific record of the CSV file so I've set up the import to match on the primary tracking key and add new records. 

      At this point I'm thinking of making a clone and starting over and going back to the original source data file (csv) since the import has hever completed the process for this 2nd csv file.  Here are my questions:

      1) I've always assumed the source CSV file remains unchanged during an import into FileMaker.  Could anything have happened to the csv file during the import process (when it crashed / became unresponsive)?

      2) Is this potentially a unique issue to FileMaker Pro Advanced 14 crashing?

      3) To troublesheet and minimize the time to import, I deleted all calc and autocalc fields in the database (I started with the FM "Starter" Contacts DB with no initial records). And then I deleted the creation and modification timestamps which I intially had added.  But each time I've tried the import again, I get the same result - FM stops responding and just sits there with nothing more imported - even after waiting over an hour.

      4) Once I do get the data imported completely, is it recommended to keep this data (9 million records) in a seperate file altogether or is it fine to keep in the same file but obviously a different table since this specific data will remain static.  In other words, will having such a large amount of data in the file cause everything to slow down in that file even when no fields in this table are being referenced?

      5) As I am going to be creating hundreds of different reports from this data-  are there any suggestions regarding setting up FM in one file with many tables vs multiple files with fewer tables? 

      Below I copied a shot of the screen when the import process got completely hung and stopped importing.  But so far when it stops it is never at the same # for "Bytes Remaining".  Thanks for any help!

       

      Kevin

      FileMaker Import Process Screenshot.jpg

        • 1. Re: FileMaker Pro Advanced 14 continues to become unresponsive during import of large CSV file
          schamblee

          This site is being combined with the community forum at the end of this month.  This forum is based on old software and gets hacked / spammed several times a day. 

          I would suggest using 'report an issue' at the top of this site.

          • 2. Re: FileMaker Pro Advanced 14 continues to become unresponsive during import of large CSV file
            philmodjunk

            Sounds like a known issue--though I don't have any reports confirming or disproving that this is still an issue in FileMaker 13 and 14.

            For More Information see:     Import of a large CSV file halts after 4GB

            This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

            It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

            • 3. Re: FileMaker Pro Advanced 14 continues to become unresponsive during import of large CSV file
              wigobucks

              Thanks for the advice Stacy and I was going to use "report an issue", but my final attempt to complete the import worked. 

              I basically started over using the same starter solution but imported the tables and the scripts (with the field mapping already set) but without the records. * I had tried a cloned copy earlier but for some reason that didn't work so I tried this method * Plus I deleted all extra fields in the starter solution that had any auto calculations other than the primary key field. 

              I set the script to automatically run and complete the import but set global time stamps to see how long it would take for each of the 4 files to import.  Came home from dinner, and just under 9.3 million records were imported into 2 different tables with no errors.  The 4 source CSV files were 1.213 GB, 1.036 GB, 1.470 GB, and 0.346 GB and were imported in 76, 86, 30, and 7 minutes respectively.  So obviously I wasn't dealing with files that were too large (files larger than 4GB were referenced in the other post that PhilMod shared - thanks for that insight. 

              Any specific feedback to these other 2 questions I didn't get a response to yet?

              4) Once I do get the data imported completely, is it recommended to keep this data (9 million records) in a seperate file altogether or is it fine to keep in the same file but obviously a different table since this specific data will remain static.  In other words, will having such a large amount of data in the file cause everything to slow down in that file even when no fields in this table are being referenced?

              5) As I am going to be creating hundreds of different reports from this data-  are there any suggestions regarding setting up FM in one file with many tables vs multiple files with fewer tables?

               

              Thanks,