Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


FileMaker as an ETL Solution

Posted on 2012-04-05
Medium Priority
Last Modified: 2012-04-05
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
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
  • 2
  • 2
LVL 25

Expert Comment

by:Will Loving
ID: 37811667

1. Check for existence of files:

You can test for a files existence - assuming you know or can calculate the name of the file - using 1) some techniques described here: http://fmforums.com/forum/topic/66020-check-if-a-file-exists/  that use native FileMaker capabilities, of 2) a plugin such as the Troi File Plugin. Because of your second item, I think you'll want to consider a plugin.

2. Copy Files

To copy/move/rename files via a script in FM you will need a third-party FileMaker plugin such as the Troi File Plugin. Because file manipulation operations are so common and easy to implement in a Plugin, many other plugins include file move/copy/rename commands in addition to whatever else they do. There are free plugins that do this but my experience with the free ones is that they are sometimes unstable and are not well supported over time such that you have to eventually replace them with something else. I've had a developer license to Troi File for 15 years and while I don't use it as much as I used to because of FileMaker's improved Import and Export options, I still do use it when I need to move, copy or rename files. It may be that you don't really need the "staging" step at all, just import directly from the source directory and then delete or archive.

3. Importing files

File imported can be scripted to use a calculated or hard coded name. The example you gave before had the file name the same each time except for the date in the name. A calculation can determine the file name for a particular date. Alternately, if you know the source directory, you can get a list of all files in the directory and then import each one.

4. Emptying tables

A scripted function that goes to a layout for each table, performs a "Show All Records" and then "Delete All Records" should take care of this.

5. Import to staging tables

Again, a scripted import can import the contents of the files into the appropriate staging table. If you are working with flat files that contains both parent and child data, you can import different sets of information into different tables, it's just a matter of setting it up. FileMaker supports Match by Field name where it looks at the first row of the import data and attempts to match columns to field names.

5. Transform data

Using script commands and perhaps other tables beyond the staging tables you should be able to manipulate your data as required. In addition to the import fields, your staging tables might also have calculation or summary fields, including calculations that reference or compare staged data to production data, and  that produces values based on the imported data that you can then, query or do further manipulation on.

7. Moving data

Depending on what your goals is, you may simply be able to run looped scripts that directly updates related records in the production data, OR, you may want to Import with Matching into your production data. You might even have steps that delete irrelevant records in the staged data before Import into Production.

8. Archive Files

Not sure which files you are wanting to archive, but I'm assuming it's the original files that were imported. Just use the Plugins "Move File" function to put them wherever you want.

Author Comment

by:Auerelio Vasquez
ID: 37812084
This all sounds great, i'll check the plug in. Now, i guess there isn't a corresponding orchestration tool, so my last part of this, is how do i put it all together, in a "Job" sorta speak ? Does filemaker have the ability to execute outside executable file (for a lack of a better word, i'm really not familiar with how macs do things, in a windows/sql world, you could schedule a sql job, that can call the command line utility to accomplish alot of this, or create a .net program that can be executed from SQL). Thanks!
LVL 25

Accepted Solution

Will Loving earned 2000 total points
ID: 37812214
Just so you know, while FileMaker, Inc. IS a wholly owned subsidiary of Apple - a fact which many people don't know - FileMaker has also been available on Windows since 1993 and a majority of FileMaker users are on Windows.

The way you "put it all together" in FileMaker is to create a "script" - essentially a macro or series of steps/operations to execute - and then either trigger that script manually, set it to trigger under certain circumstances, or schedule it to run at regular intervals from FM Server. Almost anything you can do manually in FileMaker can be scripted and you can include conditional statements, temporary variables ($) and persistent variables ($$). If I were to go about what you are describing above I would create a script that would work through each part of the sequence and I would test it manually. The example file I included for your previous question contains a script in it with notes on what I did where and why.

If after you have the sequence fully working with a manually launch, you can then look at setting it to run automatically as a server-side script. When running scripts on the server, certain script steps such as "Save as PDF" are not supported and there are certain restrictions such as a limited number of location where files can be imported from, but assuming those are taken into account, you should be able to automate it.

Sounds like an interesting project and you're likely to post more questions and learn a lot about how FM works, but it sounds quite doable.

Author Closing Comment

by:Auerelio Vasquez
ID: 37812319
Well, thanks again for all your help with this project. You are definately sending me in the right direction! Cheers.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

596 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