9 Replies Latest reply on Aug 16, 2013 3:30 AM by Malcolm

    Can't Import File - 329 Fields, 4,078,571 records

    user17418

      I have tried importing a CSV file into FileMaker. However, each time the import quits at record number 530944. There are 4,078,571 records in the file.

      FileMaker doesn't crash, it just stops at this record consistantly. I have tried with v12 Advanced on Windows and on the Mac, they both stop at the same record.

       

      I have tried only importing 1 field, still stops at the same record.

      I have removed the last imported record with no success. FileMaker just stops at the 530943rd record in the CSV file.

       

      I end up with a 255.5MB FileMaker file.

       

      What things should I look for to get past this one record? Is there a certain encoding I should go with on the CSV?

       

      Thanks

      Zorba

        • 1. Re: Can't Import File - 329 Fields, 4,078,571 records
          s

          Do you have any validation options turned on for the fields you are importing into?

          You may want to look at record number 530944 in your csv file - the first record which is NOT being imported (instead of the last one to be successfully imported).

          • 2. Re: Can't Import File - 329 Fields, 4,078,571 records

            Zorba,

             

            Do you receive an error message, or does the import simply stop and wait for you as though everything was fine? Are you sure that the import was completed -- in other words, is it possible both machines are performing some disk swapping at this time and will continue if left alone?

             

            An import of this size should work, and most likely a row at or near (prior) to the issue are at fault. If you have a text editor that can handle such a file, maybe break it into three chunks, making the middle chunk the smallest and include maybe 100 records prior to the issue. Try importing the middle chunk first to test to see if the problem does reside somewhere there before attaempting the others. Extra quotes? Unintended characters?

             

            Scott

            • 3. Re: Can't Import File - 329 Fields, 4,078,571 records
              user17418

              No validation options are turned on. This is a virgin file.

              Fields are just created and that is it.

               

              I looked ahead at the first failed record, I do not see anything that is odd with the record. But really, not sure what to look for. It looks like normal CSV formatting. Are there hidden or binary characters to look for?

              • 4. Re: Can't Import File - 329 Fields, 4,078,571 records
                user17418

                Thanks for the reply.

                 

                There is no error message. It gives me control back on the FileMaker file. I can scroll, find, etc. The first time I did this, I left for 1.5 hours. (it only takes ~15 minutes to import). I don't think it is doing stuff at the background.

                 

                I tried cutting up the file (which is a chore in itself). I have not had luck there either. Records will not import. That is why I am trying to look for some weird character in the file at that Record point.

                 

                I can share the data to see if someone can see the "character", but I am not sure what to look for. I don't see extra quotes.

                How do I verify the CR/LF is correct? Could it even change part way through a CSV file?

                • 5. Re: Can't Import File - 329 Fields, 4,078,571 records
                  DavidJondreau

                  Could be a corrupt record.

                   

                  You know you can create a found set, and your import will only import those records? Try creating a found set of just that record and import it.

                   

                  Chunking the import shouldn't be too hard. A loop that does something like this....

                   

                  Loop

                  show all records

                  omit multiple  200000*$counter

                  go to record 200001

                  omit multiple ( Get ( FoundCount ) - 200000

                  Exit Loop if [ Get FoundCount = 0 ]

                  Import[]

                  set variable $counter = 1 + $counter

                  End Loop

                  • 6. Re: Can't Import File - 329 Fields, 4,078,571 records
                    user17418

                    David,

                     

                    The file I am trying to import is a CSV file, not a filemaker database.

                    • 7. Re: Can't Import File - 329 Fields, 4,078,571 records
                      ChrisPye

                      I would be inclined to check to CSV file in a text editor that can show hidden and control characters (such as BBEdit on a Mac, not sure what to use on Windows) and look for any odd characters around the area where the import stops.

                      • 8. Re: Can't Import File - 329 Fields, 4,078,571 records
                        peteharris

                        1. Sounds like looking at the file with a good fast text editor is a good idea (as suggested)

                        2. If you needed to try splitting the file again: a tool I had to use once for a really huge file was the split unix command.

                        If on a Mac you can run this from terminal to split the file into managable chunks.

                        3. You may be able to import by importing as a TAB separated file instead (may need to change extension) with each full record into a single field. You could then look at the data that goes into that field and even parse it from there if necessary.

                        Pete

                        • 9. Re: Can't Import File - 329 Fields, 4,078,571 records
                          Malcolm

                          Have you tried opening the CSV file from within Filemaker? This will convert it into a FMP database which you can use as you wish. If it fails to open you may find some message in the log file.

                           

                          malcolm