6 Replies Latest reply on Jun 28, 2016 9:04 AM by Markus Schneider

    True? Import recs w/matching names works w/.xlsx but not .txt or .xls

    wfgclapp

      I have been testing something and am disappointed with my conclusion. I'm hoping i'm wrong. Would like to hear if anyone can confirm my conclusion.

       

      My conclusion: Given the choice between file types of .xls, .txt, and .xlsx for your import file, the only one that works using matching field names is .xlsx

       

      I am setting up a procedure to import data into Filemaker from a legacy flat file system. For import, I can create delimited .txt files or .xls file but NOT .xlsx files.

       

      I REALLY need the import process to work based on matching field names. The reason for this (not that it should matter) is I want to protect against fields being added into the legacy system (without notification to the Filemaker developer (me)) and the imports then getting screwed up because the field order is different. FYI, I am doing this import now, but using "custom field order." Works fine. Until some bonehead adds a field in the legacy file and doesn't tell me.

       

      Anyway...I have tested 8 ways to Sunday and I CANNOT get import w/matching names to work for either tab-del .txt files or .xls files. But, works like a charm for .xlsx files.

       

      Can anyone confirm that this is indeed, "the way it is?"

       

      Thanks!

        • 1. Re: True? Import recs w/matching names works w/.xlsx but not .txt or .xls
          brsamuel

          I can neither confirm nor deny your findings, but I will say that I abandoned hope a long time ago.  Import has been too fragile for too long.  I always script imports using an intermediate table, then process the just-imported data.  This gives me the opportunity to match source & destination columns regardless of user changes - or error out gracefully - without polluting the solution.

          • 2. Re: True? Import recs w/matching names works w/.xlsx but not .txt or .xls
            karina

            That's the way it is

             

            At least that's my opinion (after a lot of trial and error)

            Matching names

            Field names that match in both the target table and the source. FileMaker Pro matches field names dynamically each time you choose this option. (This option is not available when the source data does not contain field names.)

            Maybe you can write a short manual for the Boneheads with a note, that if they add a field, they should add it on the end.

             

            If you have the choice to import a xml file, you can create a XLST to match the names. But depending on the number of fields you want to import writing a XSLT can be a lot of work.

             

            Maybe there's a plugin or a another work around

            • 3. Re: True? Import recs w/matching names works w/.xlsx but not .txt or .xls
              user19752

              "Import Records" script step don't store import order as "matching field names", you can see it with printing script.

              The option is there for only users' convenience for setting order, unfortunately... let's post product idea.

              • 4. Re: True? Import recs w/matching names works w/.xlsx but not .txt or .xls
                alangodfrey

                I learn something new every day: I thought the 'Matching names' option was only available when importing from a FM file.  If the data is already in a FM file, why do you export it to an interim format?  Why not just import directly from the source file?  Then 'Match by name' is your friend and will always be correct.  Except, of course, when someone adds another field to the source file (it will still be kinda 'safe' though, as it won't import that field at all).  However if you are developing in a new solution, while others with admin access to the source file are also developing in it, have you not got a bigger problem to manage that just matching fields?

                • 5. Re: True? Import recs w/matching names works w/.xlsx but not .txt or .xls
                  wfgclapp

                  alangodfrey wrote:

                   

                  I learn something new every day: I thought the 'Matching names' option was only available when importing from a FM file. If the data is already in a FM file, why do you export it to an interim format? Why not just import directly from the source file? Then 'Match by name' is your friend and will always be correct. Except, of course, when someone adds another field to the source file (it will still be kinda 'safe' though, as it won't import that field at all). However if you are developing in a new solution, while others with admin access to the source file are also developing in it, have you not got a bigger problem to manage that just matching fields?

                  The original data is NOT in an FM file. Coming out of old legacy system, flat files.

                  • 6. Re: True? Import recs w/matching names works w/.xlsx but not .txt or .xls
                    Markus Schneider

                    it's the way it is..

                     

                    But:

                    - import the flat file into a FileMaker-helper-file (or table), then do the import into Your desired table/file