11 Replies Latest reply on Jun 12, 2012 12:33 AM by beckett85_1

    CSV Import missing columns

    beckett85_1

      Title

      CSV Import missing columns

      Post

      Anyone had any problems with importing large CSV files?

       

      I have a large CSV file, most rows only have 26 columns but on the 810th row it will not import the 26->50th column, any ideas? some csv files seem to work and some dont and all are of the same format,

       Did it manually and it still wont let me at in the extra columns when the import dialog popped up, even when i clicked through the sample to the 810th record AND could see the data

       

      Cheers

       

      Anth

       

        • 1. Re: CSV Import missing columns
          Sorbsbuster

          I would suspect that you have an additional character in one of the fields on the 810th record - probably an extra tab or return.  What happens if  you generate the CSV file with only the records from 810 on, and then import that?

          What happnes if you import it into Excel - do you get the same problem?

          • 2. Re: CSV Import missing columns
            beckett85_1

             Ok have discovered after a large amount of testing & issues, the CSV files i have in many cases do NOT have the comma on every row

             

            ie

            row 1-100 will have 5 fields (5 commas)

            row 101-5000 will have 10 fields (10 commas)

            row 5001 will have 30 fields (30 commas)

            Discovered that filemaker will only base the number of fields to import based on the first 500 rows

            For example, if

            row 1-100 has 5 commas
            Row 101-600 has 7 Commas
            Row 601.... has 10 commas

            Filemaker will only allow an import of up to 7 fields

            IF Row 601 started at row 500 this would import fine

            Any comments?

            • 3. Re: CSV Import missing columns
              philmodjunk

              How are you looking at the data? In table view? there's a limit to how many columns you can see at one time. It's possible that the data imported but that you can't see the data until you hide some fields on the left so that more on the right become visible.

              If you use a list view layout, you shouldn't encounter this limitations.

              • 4. Re: CSV Import missing columns
                Sorbsbuster

                If your theory is correct, why not sort them in reverse order and re-try the import?

                • 5. Re: CSV Import missing columns
                  beckett85_1

                  Not to do with the table order, its on the import screen when i select import records, it wont allow me to map the fields, had a preset script that maps 50 fields, when i even ran it with the dialog it does not allow for extra fields to be mapped (example i was looking at had 24 fields for the first 800 rows and then 50 fields for the next 7000 rows) wouldnt let me map more than 24 fielsd which was REALLY strange because even when clicking through the rows on the import view, even after clicking 600 times i was able to see the extra fields but not map anything!

                  Manually adjusting the information is possible, but the system imports and analyses roughly 2000 at a time, so the resorting etc isnt possible as the files come straight from the government

                  • 6. Re: CSV Import missing columns
                    Sorbsbuster

                    I thought you said that your tests showed that FM limited the number of fields it would import by checking the first 500 records, and that the number of fields increased in your data after 500.  I was suggesting that if you sort the data in the reverse order, so that the records with most fields were in the first 500 then it would prove your theory.  You could take the government-supplied file and sort it in Excel, for instance, then present it for import to FM.

                    I created an Excel file with 50 columns (Field1, Field2...Field50) and 610 records.  There was data in only the first 3 fields, except for the last record, which had data in every field.

                    I created a FM file from the Excel file.  I imported the data.  It displayed all 50 fields in Table Layout, and in the import dialogue offered all 50 fields to import.  It imported them correctly.

                    I believe the problem is caused by the actual data in the file.  I still think the earlier questions are valid:

                    "What happens if you generate the CSV file with only the records from 810 on, and then import that? What happens if you import it into Excel - do you get the same problem?"

                    • 7. Re: CSV Import missing columns
                      beckett85_1

                      Fair confusing issue - if you have a csv file with records 810 on, then within the first 500 rows you will have the MAXIMUM number of commas,

                      def see where your coming from, however when you open it and resave the csv file with excel it imports it BECAUSE excel puts the extra commas in, your exactly spot on that its caused by the data, but only in part.

                      If you open that CSV file up in notepad, you will find there are 49 commas on ALL 610 rows,

                      I actually deleted rows from the csv file to move the lower records up and it imported fine
                      You just have to have the MAXIMUM number of commas within the first 500 rows

                      original file(will only import 4 fields)

                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      ...... to row 600

                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7
                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7
                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7
                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7
                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7
                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7

                       

                      if you open and save the excel file it does the following

                      Resaved from excel File - note extra commas (will import 7 fields)

                      Field 1, Field 2, Field 3, Field 4,,,
                      Field 1, Field 2, Field 3, Field 4,,,
                      Field 1, Field 2, Field 3, Field 4,,,
                      Field 1, Field 2, Field 3, Field 4,,,
                      Field 1, Field 2, Field 3, Field 4,,,
                      ...... to row 600

                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7....

                      Modified File (will import 7 fields)

                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      Field 1, Field 2, Field 3, Field 4
                      ...... to row 400

                      Field 1, Field 2, Field 3, Field 4, Field 5, Field 6, Field 7....

                      so irrespective of resorting, excel puts in the extra commas that ENSURES the correct number of fields gets picked up

                      if you delete 100 records from the top, doesnt matter where you delete em from, if you make sure WITHIN the first 500 records there are the maximum number of fields it's fine, however my prob is that these files DONT always have the commas within the first 500 records

                      • 8. Re: CSV Import missing columns
                        Sorbsbuster

                        At least you have a work-around - import to Excel first.  You can create a macro for the CSV ->Excel import and create a script for the Excel -> FM import, so it would all seem seamless again to the user.

                        • 9. Re: CSV Import missing columns
                          beckett85_1

                           Hmmm, explain your thoughts of a workaround? at the moment, a robot computer downloads 2000 csv files and imports, is it possible to get fmp to automatically open each one in excel and save it? If this is possible i'll be verrrrrrrrrrrrrrrrry happy

                          • 10. Re: CSV Import missing columns
                            Sorbsbuster

                            Sorry for the delay - I thought I'd posted a more detailed answer.

                            Presumably you have some automated process that downloads the files, then imports them to FM.  I don't know how that automation is triggered or manageed.  What I'm suggesting is that you set an Excel file between the download part and the FM import part.  So you would (all automatically):

                            - download the text file as now
                            - FM would run an import script (just like now, however you organise that scheduled task)

                            BUT

                            - the FM file does not trigger a straight import.
                            - the first step of the import script is to open an Excel file that you will have created.
                            - the Excel file has a macro set to import the data from the text file.  It is set to 'Run on open'
                            - Back in the FM script (perhaps after a Pause [for specified period] script step to make sure there has been time for the import to complete) you change the import to be from the Excel file

                            You could make them two separate scheduled scripts - one to open (and hence import the data into )the Excel file, and an hour or so later run the Filemaker import script.

                            • 11. Re: CSV Import missing columns
                              beckett85_1

                              Manual Steps

                              1. Have written a script with iMacros that downloads roughly 3000+ Data files, as zip files

                              2. Theres a function in the database where you select the folder

                              3. The database goes through, unzips each file which results in the csv file

                              4. The database Imports the information from every csv file into the database, based on a specific record/field inside the csv file it allocates the data to the correct client/month, and updates the foreign keys in the imported data (takes a fair while as you could imagine)

                              LAST RESORT would be a macro that opens eachs csv file through excel and resaves it, only problem is the current process takes about 3 days to run, so the time would be drastically lengthened!

                              Appreciate your thoughts! this is up there as the current number 1 solution to the prob