7 Replies Latest reply on Dec 18, 2015 3:19 AM by beverly

    Import Records from csv misses lines

    twelvetens

      Hi There,

       

      I've got a weird one here...

       

      Importing from a csv file (UTF-8 encoded), with about 18,000 lines in it.

       

      Created a dedicated import table, setup import script to Add New records (table is emptied prior to import each time).

       

      The import runs fine, no errors, but only about 9,500 lines are imported. Can't really see any rhyme or reason as to why some lines aren't being imported - it seems to get not just the first 9,500 lines, indeed, the first and last lines are included, and many in-between, but just missing about 11,000 lines!

      Have interrogated the source file to see if there's anything 'odd' about some of the missing lines, but they seem identical, in terms of form (not values) to all the other lines that come in fine.

       

      There's no duplicate entries in the import lines.

       

      There's no 'updating' going on here, just adding all records.

       

      Any ideas?

        • 1. Re: Import Records from csv misses lines
          Extensitech

          Darn, got pretty far into a response before I caught your last "no updating" line.

           

          Could you show a screenshot of the import step? Also, in the lower-left, click the right arrow so the first line of data is showing and a count of the records FM is seeing. Is that 18,000 or ~9,500?

           

          Another thought: is it possible that the end of line character is incorrect, at least in some cases? That could cause two rows to be interpreted as one, and the fields from the second would be in different columns and therefore not imported.

           

          Kind of shooting in the dark here. Let me know if I hit anything!

           

          Chris Cain

          Extensitech

          • 2. Re: Import Records from csv misses lines
            electon

            Yes, I had same csv import issues too. Record count would be cut off at some point.

            It's most likely you have a wrong character somewhere in the data row(s) that messes it up.

            If column values in csv were in quotes, you wouldn't have this problem. Only thing filemaker will import the quotes and will have to be massaged.

            Maybe a number column that has a "," for decimal? Or a text field.

             

            In my case I switched to TAB separation ( exporting of data from other system was done by myself ) to avoid quoting everything.

            • 3. Re: Import Records from csv misses lines
              rouelf

              James, try opening the csv with a spreadsheet and or a text editor (free TextWrangler). See if all is ok, and save with appropriate OS end of line characters. Do a count of carriage returns into a variable, see if it matches the number of lines. Import the file into a container field, then export it as File.txt into the Temporary path, then Insert From URL to a WebViewer. Or import the csv file directly into a WebViewer using Import From URL, see if all lines are there.

               

               

              Probably best, just drop the csv file on the FM icon, it should create a new FM database, see if all the records are present.

              1 of 1 people found this helpful
              • 4. Re: Import Records from csv misses lines
                Markus Schneider

                any chances that a line contains line-breaks or other odd characters? Check the import files using a text-editor (BBEdit, TextWrangler, etc)

                check one of the lines (and the line before..) that are missing (TextEditors will have line-numbers)

                • 5. Re: Import Records from csv misses lines
                  StephenWonfor

                  I'll second the conversion of the CSV to FMP format.  Much can be revealed that way.  You would, for example, see extra fields if, as Chris mentioned, data rows were getting glued together by missing EOL characters.  If you find odd or inexplicable characters you might be able to do something like copy the csv file contents and paste into a text field in FMP then use Filter(that text field;"letters and numbers and quotes etc. and line feeds") to get that data into another field.  Copy that, paste to excel, save as CSV then import.  Barely elegant but would, I suspect, work.

                  This would be OK for one-time import but would be a serious drag for doing often.  In the "often" mode I'd imagine you could script some fancy data parsing of the clean data to write the data into your solution.


                  Stephen

                   

                  "Commas in The New Yorker fall with the precision of knives in a circus act, outlining the victim." ~E. B. White

                  • 6. Re: Import Records from csv misses lines
                    twelvetens

                    Thanks to everyone who's contributed to this. Having inspected the file with text wrangler, I think it certainly looks like malformed data, or odd EOL characters or something. I should have stated earlier that this file is produced automatically by a third party system, and needs to be imported every night as part of an automated schedule, so I think I'm going to need to go back to the system that generates the file and see if I can make some changes there. Interesting that electon found that a tab separated data format cured his/her problems, I'll try that and see what I can find.

                     

                    Thanks again to the community, you never fail me!

                     

                    James.

                    • 7. Re: Import Records from csv misses lines
                      beverly

                      James, yes, definitely find out what the EOL is for the file(s). It could be CRLF which some apps (even wrangler) might "convert" for human consumption and not reveal the true EOL. And FM import might then interpret as CRCR (thus a blank line!)

                       

                      And some apps (even FM) might convert a 'return-in-field' upon export such that the values for EOL is CR, but the in-field part is the "odd" character. These could be re-imported incorrectly. (although FM14 can export .tab/.txt and re-import with no problems)

                       

                      beverly