9 Replies Latest reply on Sep 26, 2016 5:23 AM by philmodjunk

    Import records by matching name problem

    kurt.bleicken

      We are importing files from different clients on a frequent basis. The files are .csv files. The script step starts out 'matching names' and the import works perfectly.

       

      Then along comes a different client and there may be one or two additional fields added to their import. The import gets all screwed up because the script step has changed itself to import by 'last order'.

       

      The only way I see to do a work-around for this problem is to create a new layout for each client, then in the script go to that particular client layout and run a unique script step for that client.

       

      Seems to me if you set the script to 'matching names' it should stay that way.

       

      I hope someone has a better solution.

        • 1. Re: Import records by matching name problem
          David Moyer

          Hi,

          I'm curious how you can use "matching names" with .csv's.  (I'm using 14.)  Does the first record contain field names?

          I used to import data from many external vendors as comma or tab-separated text.  Because my vendors all supplied completely different data, I just created a new FM file for each vendor.  Then I'd use scripts to programmatically create customers, orders, line items, etc. in the hosted database.  Each import file acted as a "custom filter".  Bottom line, a separate table (and/or layout) and script is appropriate for each client, in your situation.

          • 2. Re: Import records by matching name problem
            kurt.bleicken

            Hi David,

             

            FM doesn't seem to have a problem with the first row field names  . . . doesn't import them.

             

            I'm kind of thinking that a separate script may work OK.  One for each client. Not sure I need a different layout. I'll test that out.

             

            But still, why doesn't Filemaker keep the settings??? Is this a bug? 

             

            Kurt

            • 3. Re: Import records by matching name problem
              David Moyer

              Each Import Records script step should be able to retain its own import order.  Matching Names is not something that can be "remembered" within the script step, I don't believe.  I think it just remembers the last import order  Also, I think that deleting fields from your table can disrupt this, but I feel like I've read otherwise recently.

              • 4. Re: Import records by matching name problem
                David Moyer

                Oh, you can also just Show Dialog in the Import and manually select Matching Names (within a single script).

                Also, will you give me a quick example of the first line of the .csv with the field names?  I'm wondering how they're formatted.  Thanks.  (It doesn't work for me.)

                • 5. Re: Import records by matching name problem
                  philmodjunk

                  I'm able to use a script to import from CSV files using FMP 15 and the matching names option. This option is retained in the script as well. As far as I know, it always has. I created a file in Excel with column names that were exactly the same as field names in my FileMaker test file. Creating a second file with added columns and the left to right column order changed, still imported the data and matched column name to field names.

                   

                  But NO field mapping info--including Matching names is retained in a script unless you have also specified the field by name in the Specify File Dialog. This file must be an actual file and accessible at the time that you set up the field mapping options.

                   

                  But when you use the same script to import records from files of different names, it's very likely that you are letting the script open a dialog for selecting the file that you wish to import instead of specifying the name in the Specify File Dialog and thus no field mapping is retained. I'm not sure that's what's happening here, but it matches what you describe.

                   

                  There is a way to work around this limitation. You can use Insert File in a script to insert a file by reference into a container field. The script can then extract the file path from this container field and assign it to a path variable. You can then use this path variable in the Specify File Dialog. BUT, you won't get that needed field mapping unless you also use Add File to add a reference to an actual file in addition to putting in the $Path variable. So your Specify File Dialog looks like this:

                   

                  $Path

                  File://your file reference to an actual file goes here.

                   

                  While defining the script step, $Path does not exist and the script step references the file. When you execute the script, $Path has a valid reference to a file and so it is used to reference the file for import.

                  • 6. Re: Import records by matching name problem
                    kurt.bleicken

                    Hi David,

                    The .csv file we receive from clients has the first row field names separated by commas. All subsequent data rows have quotes around each field and then separated by commas. As a note if you open the .csv file with that format in Excel, Excel removes all the quotes.

                    • 7. Re: Import records by matching name problem
                      David Moyer

                      Thanks Kurt,

                      I just got my test working a few minutes ago.  That will be useful in the future.

                      What I did was to write a one line script - Import Records from my test .csv, matching names, no dialog.  That worked fine.  Then I edited my .csv (in Notepad) to swap two of the field names in the top row.  The script ran as expected - it routed the wrong data to the swapped fields.

                      Before:

                      "key_MainRecord","NumberCode","temp"

                      "1","2","0"

                      After:

                      "key_MainRecord","temp","NumberCode"

                      "1","2","0"

                      I also tried adding a new column to the .csv and that was automatically incorporated into the scripted import as long as the field already existed in FM.  If I had to create the field, the script had to be updated.

                      • 8. Re: Import records by matching name problem
                        kurt.bleicken

                        Hi Phil and David,

                         

                        Phil, your comment:

                         

                        I'm able to use a script to import from CSV files using FMP 15 and the matching names option. This option is retained in the script as well. As far as I know, it always has. I created a file in Excel with column names that were exactly the same as field names in my FileMaker test file. Creating a second file with added columns and the left to right column order changed, still imported the data and matched column name to field names.

                         

                        Phil, I think your line of thought is correct but I haven't had time to implement it yet. We are using the $path approach putting the file into a container . . . but for some reason while the file is imported Filemaker can't 'see' the field names so it shrugs and thinks, Oh well, I'll use the last order. I believe that is why 'matching names' is not working. David, I think this is the right approach.

                         

                        I have to fix the $path setup as you suggest in your post. I'm suspicious of the redefination of $path in line 91 by adding & $fileName.

                         

                        As to our process, our script first downloads the new file from our SFTP site and inserts it into a container field. Then we set the variable spath as you suggest.

                         

                        2016-09-25_08-34-11.png

                         

                         

                        Thank you for your investigation, much appreciated.

                         

                        Kurt

                        • 9. Re: Import records by matching name problem
                          philmodjunk

                          but for some reason while the file is imported Filemaker can't 'see' the field names so it shrugs and thinks, Oh well, I'll use the last order.

                           

                          This is covered in my previous post. When setting up the script step, include both a $Path variable on one line and a valid actual reference to a source file in another. Also, enabling the dialog causes this mapping not to be retained because you have just set up the script to ask the user for this info at the time the script executes--which is why I use the Insert File method

                           

                          In your code, I can't tell you if line 91 will work or not as I don't see any step that assigns a value to the $FileName nor to the $Path variables. And I don't see the purpose to line 92 unless you are just saving the value to inspect later to see if the script is correct.

                           

                          I frequently use Insert File to insert a file "by reference" into a container field--usually a global container field to get the reference to a file selected by the user. I can then extract the full file path to the file--including the file name from that container field and the file can then be located anywhere in the system for which the user has at least "read" access and they can use the insert file dialog to select the file in order for the script to import it.