asked on

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 ?

ASKER CERTIFIED SOLUTION

Will Loving

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Will Loving

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 Vasquez

ASKER

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!

Will Loving

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 Vasquez

ASKER

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 Loving

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 Vasquez

ASKER

Ok will go. I'll title the question:

FileMaker Pro as an ETL Solution