Jump to content

Import Script Multiple Non-matching Excel Worksheets


Recommended Posts

I am trying to script an import of three worksheets in the same Excel file. The 3 geographical regions worksheets are not structured the same, i.e. field name rows are not in the same place and are named differently.

The script runs but stops to ask which worksheet to process.

How do I create a variable/parameter in the script for it to proceed sequentially from worksheet 1-3 without stopping?

Thanks

DA

Link to post
Share on other sites
28 minutes ago, BostonFM said:

How do I create a variable/parameter in the script for it to proceed sequentially from worksheet 1-3

AfAIK, you cannot use a variable or a parameter to select the worksheet or the named range to import. It must be hard-coded into the Import Records[] script step. If the source file has 3 worksheets, your script needs to include 3 separate Import Records[] steps, each addressing a specific worksheet.

Note that you must use an actual file to set this up. Once you have selected the worksheet and saved the selection within the sep, you can add a variable to the File Path List to select the file.

 

Link to post
Share on other sites

What would the edited File Path List look like, i.e. syntax?

Thanks!

Here's what it looks like as is. Steps 2, 3, and 4 are supposed to address each worksheet.

<redacted>

Link to post
Share on other sites
6 minutes ago, BostonFM said:

What would the edited File Path List look like

It would look something like this:

image.png.18a51fa0019ff05801ed18024dcefb3c.png

where AnActualFile.xslx is the file you used to set up the step and select the worksheet. The file itself can be deleted afterwards, but the reference needs to stay.

Of course, if the path is a constant as in your example, then the variable is not needed.

 

 

Link to post
Share on other sites

But how is the script directed to subsequent worksheets after processing the initial. There doesn't seem to be any differentiator in your response.

The script is stopping for each worksheet to ask which one.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By NickFaraday
      Hey everyone, 
      Just looking to see if anyone has come across a good solution to for this.  
      I have a set of records with multiple fields that I need to export to Excel, I'm exporting via script and everything works great with the exception of one of my fields "columns".   This field has a multi-line description.   When exporting, all carriage returns are stripped.   A quick google search turns up some solutions for exporting a single filed and preserving returns but not a group of fields with only one "column" needed the returns preserved.
      I'm on FM19 Server. 
      Is there a plugin that addresses this issue?
      Thx
    • By bgscott
      I have an application where some of the related files will reside on removable media.  When sorting, a message box appears telling the user that the related file (which is not on the server) can not be opened (Not Found) and asks the user to locate it.  The related file may or may not be available on the removable media and is not needed for the purposes of the sort.
      Is there anyway to prevent this message from appearing?  Error capture on does not solve the problem
    • By St3v1e
      New user
      I have a "Search" script setup in one of my layouts, which works well but I'd like to refine it a little to make searching quicker and with less mouse clicks.
      Currently, I have a field into which I enter a client name, and a "Search" button which activates a search throughout my database for any records containing this name. For the search to work I have to input the name (or a portion of it) into the "Search Name" field , and then click on the "Search" button.
      What I'd like to happen is to enter the name into the field and then hit the enter key to activate the same search. Is this possible?

      Thanks in advance.
    • By ChrisOC
      Has anyone integrated Smartsheet with FileMaker using Claris Connect? I can get the basics set up and am able to create a Flow that will add a new record in FM when a row is added in Smartsheet.  However, I can't seem to figure out how to pass any column data from that row into the new FM record.  It does not give me the tags for any of the columns from my sheet, only high level parent and body tags.  What am I missing?  
    • By paulkivett
      My first try using a Server Side Script. I'm importing csv data which is on "This PC / Documents/CompPBM_checks.csv". I have tried every possible path, but still get missing file error. Options tried include
       file:FileMakerServer/data/documents/CompPBM_checks.csv
      file:ThisPC/data/documents/CompPBM_checks.csv
      file:ThisPC/FileMaker Server/data/documents/CompPBM_checks.csv
      $Import.
      $Import equals: Get ( DocumentsPath )&"CompPBM_checks.cvs"
      FM Server is hosted on AWS
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.