Solved

Using Filemaker pro 12 to Import Excel Files

Posted on 2012-04-04
7
1,028 Views
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 ?
0
Comment
Question by:basile
  • 4
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
Will Loving earned 500 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:

file:file_01022012.xlsx

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.
Import-Demo.zip
0
 
LVL 24

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....
0
 
LVL 1

Author Closing Comment

by:basile
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!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

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
0
 
LVL 1

Author Comment

by:basile
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.
0
 
LVL 24

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.
0
 
LVL 1

Author Comment

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

FileMaker Pro as an ETL Solution
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now