Jump to content

  •  

Photo

Import .xls using variables in script

import variables scripts

  • Please log in to reply
2 replies to this topic

#1 Chris1961  member

Chris1961
  • Members
  • 9 posts
  • FM Application:11 Advance
  • Platform:Windows 7
  • Skill Level:Intermediate
  • Time Online: 11h 36m 5s

Posted 12 January 2012 - 06:51 AM

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
  • 0

#2 dansmith65  veteran

dansmith65
  • Members
  • 857 posts
  • LocationB.C. Canada
  • Certification:8, 11, 12, 13
  • Membership:TechNet
  • Time Online: 14d 22h 36m 38s

Posted 13 January 2012 - 02:43 PM

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

  • 0

#3 Chris1961  member

Chris1961
  • Members
  • 9 posts
  • FM Application:11 Advance
  • Platform:Windows 7
  • Skill Level:Intermediate
  • Time Online: 11h 36m 5s

Posted 14 January 2012 - 03:05 AM

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
  • 0





FMForum Advertisers