Hi, SeattleSquirrel: It sounds like what you're currently doing is manually 'opening' the Excel file in FileMaker, thus 'importing' the data into a newly-created database file.
If I understand correctly, you want to import data from numerous Excel files directly into an existing FileMaker database table, adding to the data in that table, and to automate that process.
You can set-up a script to import an excel file into a table. This works best when the files being imported are always the same format/structure). It can be a little tricky, but is very common, normal practice.
Look for the Import Records script step. Here you can specify a file by path and specify the field mapping. I would start by setting the path to and field mapping for an exemplar file. Later, you can remove the path so the user selects the file each time OR you can keep the path, so long as each time you import a file, it has the same name and location. You'll probably want to perform the import without a dialog, UNLESS you need to manually tweak the field mapping for different files each time (but that would reduce the effectiveness of automation).
Well, I have that part set up. The files are already importing, and the Import function was nice enough to create scripts to enable import updates with the click of a button. But Sussie's timesheet imports into one table, while Mike's into another. In the end I'd like to end up with one auto-generated merged table that contains all employees data. Not sure if that makes sense, let me know if not!
Thank you so much for taking the time to answer my question - much, much appreciated!
If the scripts are created, can you just edit them to point to the same table, then call them in succession (with a "controller" script, perhaps)?
Hm, that's interesting! If I did that, and then tried to update it daily, what would happen? Would it save over data?
Are you "updating" existing data or importing new records each time? I would think this would be new "daily" records. Why would you need to remove/delete yesterday? or would you?
I used the FM option in the Import dialogue box "Set up as automatic recurring import" - that then created a table layout with a button that I click to import. Not sure if that answers your question?
You are right, I don't want to delete anything from yesterday. The timesheets are a running list (excel spreadsheet) of all time entries over time so I basically just want to make sure FM has included all the latest entries too.
This part all seems to work fine on individual tables, question is just how I merge / consolidate all the employees entries into one table. I can't even do this in excel (mac) without some labor intensive workarounds or manual copy and paste.
Maybe make a backup and try to move one of the "imports" to add to another import (and change the table to be the correct one).
Can you post any of the script(s) for feedback?
Once you merge the scripts they work.
I may be in over my head here - not sure how to do this:
move one of the "imports" to add to another import (and change the table to be the correct one).
I've attached the script that FM creates automatically when I set up the "automatic recurring imports"
So basically I have one script each for all the different timesheets now.
OK, so lines 4 &5:
Show All Records
Delete All Records
These clean out the previous record EACH time, so you want to disable them.
Go to Layout ...
This going to be different for each import (for now), because the layout is tied to the table/table occurrence
Line 6 is the key feature and would need to be revised so that the table points to the same one each time. I would need to be changed (along with line 2 - Layout) to be one layout one table for each of the different imports.
First, can you verify that you have the SAME fields in each of the new tables? Because that is very import to allow a single table for the imports.
Hello, Just trying to follow the thread, Excel imports.
Do it every day. We have set up a series of imports from Excel spreadsheets, by creating a recurring import.
First, give you some simple quick solutions that work.
Take your Excel spreadsheet worksheet and open it as a FileMaker file and set first line to field names.
(Just works well) You will take the Excel spreadsheet and convert it to an coma delimited .csv file for import later...
Import the FileMaker pro converted spreadsheet making sure your change the FileMaker Pro Table and File Name of your converted file. Open and change the names so your layouts and table are your preferred names.
Import and create an recurring import, it will creates a ER diagram table, Layout and Script.
So, now you have the table, layout and a script, with not data yet , no problem.
Go back to your excel spreadsheet and save is as a coma delimited .csv file. You will have to save all the spreadsheets as a .csv to it import, change the script location and type to .csv...
Now go to your newly created layout and import the excel.csv file...
You will find a script on the layout.
Remember to turn off the delete script step - unless you are importing fresh data each import, added each spreadsheet data into the table and record.
The script will need to be modified both for map data, (script debugger is a friend) Script step.
FileMaker 14 server will run this script if you set in up in the backups. Place excel spreadsheet.csv in the location \\servername\Program Files\FileMaker Server\Data\Documents\Folder (this location is deep in the filemaker folders)
I have about 15 imports running automatically at 4:00 a.m. every day...
Hope this helps, I have some friends with MacTutor in Clearwater, who help me.
yes, coorect! All spreadsheets are identical. I'll try this tomorrow. If I do delete all - what's the difference in end the result?
thank you! The spreadsheets have functions such as linking to an external sheet so the employee will be able to solve a project code based on a predefined list. I don't think I can do that w csv files? Would this work with regular excel format?
The predefined list must be imported into FileMaker (once) into a new table for "lookup" with a relationship between the 'time sheets' and the project code list. Yes, you can use .csv as import into FileMaker!
Anyone have an issue when doing the first initial import for the recurring file?
I have my spreadsheet saved with 20 files just to test out the functionality of this feature, and every time I do the import, it will take empty cells from the spreadsheet and import 40 records...20 with data and 20 blank.
I thought maybe it was how I was saving my excel sheet, but I tried every possible way and on top of that you can't actually delete empty cells on the spreadsheets, every cell you delete it creates a new one anyway...
Any ideas, suggestions, tips would be awesome!