Using Filemaker pro 12 to Import Excel Files

Posted on 2012-04-04
Medium Priority
Last Modified: 2012-06-27
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 ?
Question by:Auerelio Vasquez
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 25

Accepted Solution

Will Loving earned 2000 total points
ID: 37809096
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 http://www.filemaker.com/11help/html/create_db.8.32.html )

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.
LVL 25

Expert Comment

by:Will Loving
ID: 37809098
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....

Author Closing Comment

by:Auerelio Vasquez
ID: 37809307
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 asp.net can access filemaker ? it doesn't seem as such. thanks for your help!

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 25

Expert Comment

by:Will Loving
ID: 37809347
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

Author Comment

by:Auerelio Vasquez
ID: 37809504
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.
LVL 25

Expert Comment

by:Will Loving
ID: 37810593
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.

Author Comment

by:Auerelio Vasquez
ID: 37810848
Ok will go. I'll title the question:

FileMaker Pro as an ETL Solution

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question