Sign in to follow this  
Followers 0
Chris1961

Import .xls using variables in script

3 posts in this topic

Hi

Sorry, new here so please forgive me if I am in the wrong area!

I have a file which relies totally on externally provided data. I cannot link directly to the data and have to download the individual .xls files manually from the internet and I store them in a child-folder called "Lincs" in the My Documents folder using a direct path name.

Problem is, this will be used by other people in other countries and the route to their My Documents folder is differnet to mine.

To get round this problem I used the following in my script:

Set Error Capture [ On ]

Set Variable [ $file; Value:"filewin:" & Get ( DocumentsPath ) & "namefolder" & settings::globaltextnamefilewithextension ]

Import Records [ Source:“$file”; Method:Update existing; Character Set:“Windows ANSI”] [ No dialog ]I

f [ Get ( LastError ) = 100 ]

Show Custom Dialog [ Title: "Error"; Message: "File Does Not Exist"; Buttons: “OK” ]

End If

This seems to work OK.

Query:

When I set the filepath manually within the script (ie [file:../../../../../Temp/BostonRD.xls]) I click OK, then select 'Sheet 1' of the x.ls file, and then select 'data' as the 'First Row Option'. Then, when I click on 'Specify Import Order', the source fields are displayed which I can match up. I can also select 'Don't Import First Record (contains field names)", and then click OK again. Then click on 'Import Options' and select 'Import'. All works great.

However, when I use a variable to import the file, I am not given the 'Sheet No' option and when I try to specify the import order no source options are displayed. Also I cannot choose to omit the first record containing the field names.

It works in principle, but imports the 'title fields' or a blank record from the .xls file. Also, how does it know which fields to match up?

As I say, it works in principle but I don't understand why and how can I stop it importing the 'title fields' from the .xls file?

Any help would be greatly appreciated.

Chris

Share this post


Link to post
Share on other sites

Using a variable for a path to import records can be a little tricky since FileMaker cant find the file via your variable while you are editing your script. So, while editing the script, set the filepath manually so you can specify the import options, then you can change the path back to the variable path, and it should work.

You can also just specify multiple paths, and leave it like that. To do this, enter this text in the "Specify File" window:


$file

file:../../../../../Temp/BostonRD.xls

Share this post


Link to post
Share on other sites

Hi Dan

Thanks for that. Yes, makes sense when you think about it. As I said, it seemed to work OK, I just couldn't see why!

Chris

Share this post


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
Sign in to follow this  
Followers 0

  • Similar Content

    • Scripts drop down menu is greyed out
      By MutantST
      Hi All,
      Does anyone know why my scripts drop down menu might be greyed out? I am unable access these functions.
      Cheers, Simon
    • HELP creating homework planner app
      By Hamidfs
      Hi,
      Im in deep trouble
      I need help using scripts
      I am making a homework managing app for a project and i have created a few fields; date due, subject, task, and task type
      I need help on how to process this information and show computational thinking 
      I need help in creating POP UPS!!
      I am looking for something, when clicked on, pops up a screen showing the outstanding work when the user clicks on button 1 which has a date which has past . AND I'm also trying to make a button 2 which shows entries that are close to the next few days 
      please help as soon as possible and suggest any features i can add to the homework manager and how to write the script for the buttons
       
    • Moving scripts to folders in Script Workplace?
      By fmwannabe
      I'm using 14 Advanced, and looking at the Script Workplace. The left pane has my scripts. Some are in folders, some not.
      How does one move an existing script into an existing folder? I tried dragging but that won't work. Must be a different way? Or perhaps they are moved someplace else in FileMaker Pro?
    • Stopping a database form selecting a field
      By Stickybeak
      I have a script that runs on open.
      It insists on selecting a non-modifiable field even when I end the script with a go to object command.
      How do you get a file to open in a neutral state - ie with no active field?
    • Script Running Way Too Long
      By Lawrencex
      Hello FM Pros
      Would appreciate some tips for the following script problem:
       I have a situation here where my scripts in FM 13 Pro Adv takes more than 5 hrs to complete a calculation.

      OS: This is Win 7 Pro. with 16 GB RAM (i5 Processor.)


      I have Field "A" which is a 7 digit code numbers FIELD, which most often are repeating numbers, from 200 different types.
      (And I have upto 80 million records, in this field.) Code examples: 1234567, 2345678, 5678012,...

      Fields "B" to "Y" (are global Fields) provides a reference number for each type of the 7 digit codes from Field "A". For
      example the code 1234567, as above, the reference is, eg. HH/2015/Jul/132, Code 2345678 the reference is, HH/2015/Sep/314,...etc

      Field "C" list the reference numbers from Field "B" in the order of the records from Field "A"

      I use the following scripts in Field "C"

      If [Field A="1234567"]
      Perform Script [Replace Field Contents Field C;Field B]
      Else If [Field A="2345678"]
      Perform Script [Replace Field Contents Field C:Field B]

      Else If (...until 200 lines of the references from Field)

      End If

      The script runs well. However, it takes more than 5 hrs to complete the entire 80 million records.)

      Would appreciate your suggestion to speed run the scripts.

      Many thanks in advance.

      Lawrencex