Import Excel file from URL link

[mdmanalytics]

We have a field in a DB hosted on FM Server 11 that contains the URL to an Excel file in a folder on a remote server, and want to import the Excel file into the hosted database to create new records with field matching  - via a script.  We aren't sure of the best approach. Suggestions would be appreciated.

Thanks.

[ggt667]

Note FileMaker is a bit tricky at times when it comes to choosing files to import from, yet this might be to your advantage.

1) First make the script to import the file using a static reference
---
Set variable $tmp = URL
import Records using some static reference to file; such as "http://server/file.xlsx"
---
2) Make the mapping of the cells to fieldnames
3) Run the script once to make sure you have the right piece of data in the right spot.
4) Edit the script step 2
---
Set variable $tmp = URL
import Records using $tmp
---
5) Run the script again to check that it works for a couple of references.

You should now be done.

This will work fine if the script is always executed by a user, if you want the server to do this you do want to add some error catching of some sort.

[comment]

We have a field in a DB hosted on FM Server 11 that contains the URL to an Excel file in a folder on a remote server, and want to import the Excel file into the hosted database to create new records

 

The only type of file that you can import from using an HTTP request is XML.  All other types must exist on your network. Either download the file first, using an external process (e.g. Applescript or a plugin), or insert it into a container and export it to a local location before importing.

[ggt667]

I use my suggestion a couple of thousand times pr day for XML files, XLSX files are a flavour of XML files should work fine; But I never tried using XSLX.

[comment]

XLSX files are a flavour of XML files

 

No, they are not. An .xlsx file is a ZIP archive containing an assortment of XML files in several folders. It cannot be imported into Filemaker using the Import Records > XML Data Source...  route.