Solved

Using Filemaker pro 12 to Import Excel Files

Posted on 2012-04-04
7
1,034 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 25

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 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....
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 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