Importing data on a regular basis
If you regularly have a report coming to you in an excel spreadsheet (or csv file) that needs to be imported weekly (possibly even daily) into a database, is there a way to set it up to be fully automated?
At the moment, the user receives the report via email, saves it to their hard drive and then runs a script in the database which:
- goes to the right table
- shows all records
- opens the import records dialog box at which point the user selects the file they saved to their hard drive
- imports the records (probably by using the 'last order' setting, so it should be relatively easy)
Ideally however, I'd like it if the report could come in via email, be placed in a shared folder somewhere and then for the import script to run automatically at a certain time each week.
Does anyone have any advice re this process? Are there naming conventions I would need to use? Filepath variables? Ways to avoid errors if the person responsible for putting the file in the shared folder hasn't done so on time?
Filemaker Pro 10 Advanced, Mac OS X