5 Replies Latest reply on Sep 2, 2014 3:13 PM by philmodjunk

    Problems Importing from Excel - Maintaining Matching Fields

    rsagall_1

      Title

      Problems Importing from Excel - Maintaining Matching Fields

      Post

      I have to import data from Excel to FM 13. Not all the field names match. The first row in the spreadsheet has field names.

      I can manually match the fields each time I do the import and it works fine. My problem is that I can't get the script to remember the import field matching. 

      Thanks

        • 1. Re: Problems Importing from Excel - Maintaining Matching Fields
          philmodjunk

          Does your script use a path variable to find the excel file and import from it?

          • 2. Re: Problems Importing from Excel - Maintaining Matching Fields
            rsagall_1

            No - I manually select the spreadsheet. 

            • 3. Re: Problems Importing from Excel - Maintaining Matching Fields
              philmodjunk

              And that's why you have to re-map the columns each time.

              Here's another approach:

              Define a container field in your database. This field can have global storage specified.

              Set up your script with Insert File to open a dialog where the user selects the excel file they want to use in the import. The file is inserted with the "store a reference" enabled as this then inserts the file path to the selected Excel file.

              The script can then set a $Path variable to the file path extracted from the container field and you can then set up a two line entry in the File Reference Dialog. The upper line is $Path and the one used when the script executes. The lower one is a reference to a file that has the correct format for the import you want to do. This reference need only be valid at the time you set up the script step. It's just there so that you can set up field to column mapping in the script step so that it will be preserved and used when the script executes.

              See this thread on $Path variables. It includes a down load link to a file that you can use to learn more about Container fields, $Path Variables and the script steps that use them: Exploring the use of a $Path Variable in Scripts

              • 4. Re: Problems Importing from Excel - Maintaining Matching Fields
                rsagall_1

                I am not concerned with manually selecting the spreadsheet to import. Before the source of the spreadsheet made some changes in the column location and name. I was able to have the fields on the spreadsheet match up with the fields in the FM solution. 

                 

                • 5. Re: Problems Importing from Excel - Maintaining Matching Fields
                  philmodjunk

                  But manually selecting the file is what is keeping the file from retaining the correct field to column mapping.

                  Before the source of the spreadsheet made some changes in the column location and name. I was able to have the fields on the spreadsheet match up with the fields in the FM solution.

                  Without a script, that can't be guaranteed. FileMaker will remember whatever was done most recently--if the file is not hosted from a server. But if you open this as a client of a hosted database, it will remember what was last done on the host.

                  But if you do not use the option to manually select the file, the script will store the mapping details inside the Import Records script step.

                  And my last post over complicates the minimum details needed. If your file has exactly the same file name each time and you import it from exactly the same folder each time, you can set up the import records script step to always import a file of that name from that location and this also will retain the field to column mapping order.