3 Replies Latest reply on Aug 11, 2016 4:14 PM by philmodjunk

    Script to import specific excel worksheet

    jayyo

      Hello,

       

      I have an excel workbook that has two worksheets: Survey and Import. The Import worksheet simply takes all of the fields from the survey and puts them into columns for a straight import into FM via a script I wrote that works well, but it asks for which sheet I want to use for import every time. I'm wondering if there's a line I can write in the script that will specify which worksheet I want to import by the text "Import" since that's the name of the sheet. Thanks!

        • 1. Re: Script to import specific excel worksheet
          philmodjunk

          If you can specify the name and location of the file in the script step, it will also retain the worksheet that you specify at the same time.

           

          Just ran Import Records from FMPA 15 and it imported the specified worksheet without asking me to select the sheet, as I made that selection when setting up the import records step.

          • 2. Re: Script to import specific excel worksheet
            jayyo

            I forgot to mention my script uses a temporary file path to pull from a container [in this case, component_tracking: Import RFQ] that I drop my Excel workbook (with Survey and Import) in as two tabs. So when I run the script, a window pops up and it shows me the name of both sheets and I can choose the Import sheet through a menu.

             

            If I use a temporary file path in this way, can I specify that when I export the container contents it will be an excel sheet and I'm looking for a specific worksheet? I've pasted the code below for reference. I'm guessing it would happen in line 13... Thanks!

             

            Screen Shot 2016-08-11 at 9.26.16 AM.png

            • 3. Re: Script to import specific excel worksheet
              philmodjunk

              Yes.

               

              That variable for the filepath creates an issue during script development that takes a bit of creativity to work around. At the time you set up the import script step, put two file references into the dialog instead of just the $path variable:

               

              $filepath

              Reference to a file that exists and is an example of what you want to import from here

               

              You can use the add file button to add the second line. During script development, $Filepath does not exist and the reference "falls thru" and uses the second row to access a file for import. You can then do the needed field mapping and specify the worksheet that you want and these settings will be retained in your script.

               

              When you run the script, $Path has a valid reference to a file and so the second row is not used. It's a good idea to make sure, by moving or renaming the example file. That way, if some error produces a $Filepath variable with an invalid path, the script doesn't import from the example file.