11 Replies Latest reply on Mar 6, 2012 2:47 PM by philmodjunk

    Import multiple Excel ranges to tables

    MikeF

      Title

      Import multiple Excel ranges to tables

      Post

      Need a script that will ...

      Ask what Excel file I want to import from.

      Import three specific ranges from that one file into three specific tables in FM.

      Range 1 > Table 1

      Range 2 > Table 2

      Range 3 > Table 3

      The Excel range names are the same all the time.

      The specific file is different all the time.

      The import is Add Records only.

      Thanks,

        Mike

        • 1. Re: Import multiple Excel ranges to tables
          philmodjunk

          Trick number one:

          add a container field to your layout and use the Insert File Step to insert a copy of the excel file into the container field "by reference". This pops up an open file dialog where the user can select the excel file to import from. You can then use text functions to extract the file path and file name from the container field into a variable.

          Then you add 3 import records steps that all specify the variable in the file source dialog.

          Trick number two:

          When I first got such a script working, I found that when I specified a variable as the file source, I couldn't seem to retain the other settings for the import (field mapping, etc.) I found that I could first set up the import records steps by explicitly specifying an example file, then setting up the options for the import records step. (And you can specify your ranges in one of those dialogs). I'd then return to the specify file dialog and insert the variable name on it's own line placed above the file name.

          Apparently, the fact that the variable does not contain a value when the script is not running causes this issue, but at least it is easily solved by using an example copy of the file during the time you develop the script.

          • 2. Re: Import multiple Excel ranges to tables
            MikeF

            Hi Phil,

            Am back after being off the grid for awhile.

            How exactly would I accomplish the following via script?  Wpuld I add the container field to the first table I'm importing the first excel range into, ie Range 1 > Table 1 ..??

            "add a container field to your layout and use the Insert File Step to insert a copy of the excel file into the container field "by reference". This pops up an open file dialog where the user can select the excel file to import from. You can then use text functions to extract the file path and file name from the container field into a variable".

            Thanks,

              Mike

            • 3. Re: Import multiple Excel ranges to tables
              philmodjunk

              I usually use a container field with global storage as I only need one such field to use with my import script. A field with global storage specified will be accessible from any layout and any record, found set and script in the file--so it won't matter where you define it. I often create a "globals" table for creation and use of all global fields except those who are used in a relationship.

              • 4. Re: Import multiple Excel ranges to tables
                MikeF

                I actually could use a link to the original file in my "first table".

                By any chance, do you have an example, possibly with some script lines?

                Thanks,

                  Mike

                • 5. Re: Import multiple Excel ranges to tables
                  philmodjunk

                  I've used this method more for setting up "upgrade" scripts where a user  receives a clone of a new version of their data file and they run it to select their previous copy for import, but the basic steps are the same:

                  Go to Layout [Globals] //specify a layout where the global container field, gImportFile has been placed
                  Set Error capture [on]
                  Insert File [Reference ; Globals::gImportFile]
                  If [ get ( LastError ) = 0 // user did not click cancel in dialog box]
                     Set Error Capture [off]
                     Go to Layout [DataImport] //specify layout based on target table--not strictly necessary, but useful during testing.
                     Set Variable [$Path ; value: Getvalue ( Globals::gImportFile ; 2 ) ] //second line of text in container field is path to file
                     Import Records [No dialog ; $Path ....

                   

                   

                  End IF

                  • 6. Re: Import multiple Excel ranges to tables
                    MikeF

                    Ok, this is getting there. Although haven't tried it with the second and third tables yet.

                    Before I do ...

                    Can't seem to use the Import Records[No Dialog...  , as there are steps that need to be specified.

                    For each Excel range that needs to be imported, the [range] name is the same.

                    So would like to be able to use No Dialog, but need the Import Records step to ..

                     - Automatically choose "Display named ranges".

                     - Then automatically select "Range1".

                     - Then automatically choose "Import" ["matching names" is already set].

                     - Then automatically de-select "Perform auto-enter options while importing" and proceed with the import.

                     ... Can that be accomplished??

                    Thanks,

                      MIke

                     

                    • 7. Re: Import multiple Excel ranges to tables
                      philmodjunk

                      You have to select all of those options when setting up the import records step. The trick is to set up import records to an actual copy of this file first, then go back and edit the file reference so that $Path is the first line in the file reference and the original file reference to the example file is line two.

                      • 8. Re: Import multiple Excel ranges to tables
                        MikeF

                        You've lost me with ...

                         

                        "The trick is to set up import records to an actual copy of this file first, then go back and edit the file reference so that $Path is the first line in the file reference and the original file reference to the example file is line two."

                         

                        ... Especially the last part where    "the original file reference to the example file is line two."

                        • 9. Re: Import multiple Excel ranges to tables
                          philmodjunk

                          Add the Import Records step to your script.

                          Click the specify data source check box to open the specify file dialog. Click Add File and select an existing excel file of the type from which you want to import records.

                          Click Specify import Order and select all your import options including the range.

                          Now select File from the drop down to the right of the Specify Data Source check box to re-open the Specify File dialog. Type in the name of your variable, $Path and press return so that $Pat is listed as line 1 and the path to the file you used to specify the import order is still there as line two.

                          • 10. Re: Import multiple Excel ranges to tables
                            MikeF

                            Well, it works fine now.

                            Looks a bit strange that Import Records [No Dialog, source "d:\imports\fileimport.xlsx";..  is there, as opposed to Import Records [No Dialog, source "$Path"; .

                            So that file path will always be there?    Guess that's fine as long as it works.

                            Thanks Phil.

                              Mike

                            • 11. Re: Import multiple Excel ranges to tables
                              philmodjunk

                              It is a bit strange--thought the same thing when I first figured out with trial and error how to get this to work with a $Path variable.

                              The way external file references work is that you can specify multiple file references in the same dialog. FileMaker works through them from top to bottom until it either finds one that works or it runs out of references, so the $Path variable will always point your script step to the correct file as long as it gets a valid file path. When you work with the step in the script editor, there's no value in path so it switches over to the reference to your example file. You can remove or rename your file once you've got it all working to your satisfaction, but you'll need to put it back or establish a reference to another copy of the file if you have to go back and change the import options for the script step.