Using Filemaker pro 12 to Import Excel Files

What i'm trying to accomplish is this:

Import Files
Move the data from files into staging tables
then move that data into final tables after doing some scrubbing
Basically i'm trying to mimic an ETL enviornment. Here's the issue, i've used ssis/informatic to accomplish these tasks before, including moving the files to archive paths once the process is complete.

Additionally, the file names will change, so in ssis you can assign variables to the filenames using expressions.

Is there an equivellant tool that works with filemaker pro, on macs ?

Can someone show me a sample script to import an excel file if the file were named /users/excel/file_01012012.xlsx then the next day the file would be file_01022012.xlsx i basically would want to import any .xlsx file in the folder (if that explanation helps) that's the biggest challeng i'm having, if i could just get that part to work, i could research a tool that can do this sort of ETL. Is there a free or open source tool that will do this on a mac ?
Auerelio VasquezETL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
Hi basile - I've attached an example file that shows how to setup a scripted import for a calculated file name such as "file_01022012.xlsx". The basics are that you create a variable called "$FilePath" (or whatever) and then you use a calculation formula to calculate and concatenate the file path using FileMaker's very specific file path syntax (for detailed info on specify various types of file paths in FileMaker for Import, Export, Save As, Create PDF, etc., see )

If the file to be imported is in the same folder as the FileMaker file, the path can be specified as:


To calculate that with the current date the calculation would look like:

"file:file_" & right( "00" & Month( Get ( CurrentDate ) ) ; 2 )
& right( "00" & Day( Get ( CurrentDate ) ) ; 2 )
& Year( Get ( CurrentDate ) )
& ".xlsx"

If you look at the script in the example file you will see this and a variant using a global field to specify the date instead of using Get(CurrentDate). Take a look at the script and try importing the files by changing the data. if the date is blank or a date is entered for which there is no file found to import, I've set it to give you an error dialog, though you could just as easily have it log the error somewhere else or even notify you by email.

If you want to update existing records rather than just import new records, you'll need to go a step further and setup a key field to match on, but I want to make sure you understand basic scripted imports first.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Will LovingPresident, Dedication Technologies, Inc.Commented:
And by the way, if you are actually using FileMaker 12, you're pretty fast out of the starting block since it was just released this morning. My example file was created with FileMaker 11, so if you open it with 12 it will likely tell you it's going to convert it to the FMP 12 format, but it should still work just the same. Just be sure to save the converted version to same folder as the original so it can find the Excel files properly. I need to go download my copy of FMPA 12 so I can be creating example files in that version when people need it....
Auerelio VasquezETL DeveloperAuthor Commented:
Thank you for your response. Yes, this was filemaker 12, i got a license today for that. This is for a client, and that's what they want their db in. However, is there an orchestration tool, that will also do the above questions? like get the file, import, extract, transform, load, and move the files to another folder? or is is possible to accomplish those tasks in filemaker? i'm really new to this, i've come from a RDBMS world of SQL and Oracle, and have used orchestration tools such as Informatica and SSIS. So, it would be really cool if there was an open source tool that can do this with filemaker on a mac. Or, on the converse, is there an api that c# or can access filemaker ? it doesn't seem as such. thanks for your help!
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Will LovingPresident, Dedication Technologies, Inc.Commented:
Filemaker does not have a specific orchestration tool that I'm aware of though someone may have built one in FileMaker. FileMaker does have tremendous capabilities for manipulating data and then exporting it back out through scripting, which can be manually triggered or set to run on a schedule from FM Server. I use FM a great deal to do data integration between very different systems, what I refer to as 'massaging' the data from one structure to another. A fair amount of how I do that simply comes out of experience with doing it lots of times with different needs, but if you have specific questions and post them separately I will try to respond.

As far as FileMaker's interaction with other systems, here are the main ones:
FileMaker can connect to an SQL source and even act as a 'dataless' front end for an external SQL source, including creating your own local joins in FM and piggy backing FM calculation fields on top of the SQL fields in each table accessed. It supports mySQL, SQL Server and Oracle.
For web interaction, FileMaker has a PHP API
FileMaker itself can be a source for other systems and accessed via ODBC or JDBC
Auerelio VasquezETL DeveloperAuthor Commented:
Specifically what i'm looking to do:

1. Check for the existence of a file(s)
2. if the files exist copy them to a directory (staging)
3. Import the files (they might be input by date, or may always have the same name, i haven't been given that directive specifically (still working on the requirements)
4. As in any ETL (or massage process, as you stated) i want to first to Empty all of the staging tables. With SSIS/Informatica i can simply issue a truncate table statement within the orchestration.
5. Import the files into the staging tables
6. Transform (massage) the data based on conditions say, 1. if the row exists update or delete the record. or 2. insert a new row
5. Move the data from those staging tables to production tables
6. move the files into an archive directory

That's a simplified process i'm trying to accomplish, being that this is the first time i've ever looked at filemaker pro, and i don't know how to manipulate files on the mac via programming/scripting language.
Will LovingPresident, Dedication Technologies, Inc.Commented:
I think all this is probably quite doable in FileMaker and as I say is quite similar to what I do with data integration projects where I manipulate data from one structure into another. Since you've already accepted my answer on this question, my suggestion would be that you post the above as a new question and I will start with some short responses or comments on each part of your list and we can go from there. The details of what you are trying to do, for example import new vs. update existing - or both at the same time, will make a difference in how you proceed, but I think I can give you guidance.
Auerelio VasquezETL DeveloperAuthor Commented:
Ok will go. I'll title the question:

FileMaker Pro as an ETL Solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.