Exporting FMPro 5.5 with ODBC

[jeepxo]

A bit of background first.

We have an existing FMPro application that is being replaced by a web app. The Web app is using MS SQL 2000 as the database. We've made significant changes to the database structure on the new app compared to the FMPro version.

I've been asked to try to import some of the data from the FMPro version. Normally that wouldn't be a problem going from one DB to another, however, the FMPro version is a stand alone app. We have roughly 1200 instances of the application that I have to import data from. There is a fair bit of validation that has to go on before I can import it.

My thought was to create a small VB application to the import for me. To complicate it, I can't trust that end users can export their data to a tab, or csv file, so I was planning on just getting their fp5 file, and getting the data I need through ODBC. Each fp5 file is named the same thing. Again to simplify it for the end user, I was going to instruct them to simply copy it to their personal drive on the network share and I'll grab it from there.

90% of the VB is a nobrainer for me.
Inserting the data into SQL is a nobrainer for me.
My problem is figuring out how to make an ODBC connection to the FMPro part to get the data. I've seen some examples of FMPro going to SQL to get data, but nothing the other way around.

How does one make a file dsn to an FMPro database to get data out?
Ideally I want to throw all the files into a directory structure, iterate through them, validate the data and stick it into SQL. Going from Access to SQL this way is as simple as spilling my coffee down the front of my shirt. I'm not an FMPro person so I'm having trouble getting my head around how FMPro does anything (except crash constantly).



"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[twiSSt]

Have you looked at the Execute SQL Script step in FMPro?

I currently insert and update into MySQL from filemaker via that script step. It required downloading the correct ODBC driver and configuring FM Pro for ODBC. The help tab in FM gives pretty good instructions how to get it set up. Look at ODBC - Configuring the ODBC Panel to share data.

We are currently in a similar postion migrating from FM to a web app. Some of the pieces are ready so we are transferring data to the web app in the above mentioned fashion.

Twist

===========================================
Everything will be OK in the end.
If it's not OK, then it's not the end

 

[jeepxo]

From what you are saying, I would have to open 1200 plus FMPro databases and put that script in. Don't like the sounds of that.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[JeanW]

1200 databases/tables in one application ???

Then there is something wrong with the structure...

Or am I missing something ?

 

[jeepxo]

We have roughly 10,200 computers on the network.
Approximately 1200 of those have a copy of the standalone FMPro application. A few years back 1 individual in our organization came up with a neat little application for his Mac. A couple people liked it because it meant they didn't have to do that job with a pencil and paper. Then the organization decided to adopt it as our "standard". Oh and did I mention that we migrated from Mac to PC 5 years ago.
Then last June someone decided that IT should finally get involved with it. A bean counter decided that since we have all this data we should do some analysis on it.

It was given to me. I gringed at the thought of trying to do any kind of meaningful analysis on 1200 standalone databases, hence our rewriting it to a web application.

Originally we were not required to import any existing data. We were going to start fresh.
Well...now that the new application is complete, guess what I have been tasked with doing.

So I have to take 1200 standalone databases from 1200 individual users and import their 150 or so records to give me a database with roughly 180,000 records.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[JeanW]

Give every 'user' a number.

Since you're on a network, tell every single user to save a copy of the file to a central place on the network, under a given number. (I suppose the files are flat, no relationships).

Now import the data into one FM file, which can be a clone/no records from the initial file.

You could make a vb script to loop through the files, because there will be a sequence, something like counter +1.

If you give a number to each import, in the final file you will know which data is coming from which file.

 

[lebisol]

in line with JeanW's thoughts....

you could consider adding a script that will run on exit of the file....
-flag each DB that it has been 'exported' or 'saved as...to network'
-then change/set user password so that they no longer have the access to files ones exported
- if u can not edit/add a script to each station perhaps consider distributing your own small 'export.fpx' that will do this...
All the best!

:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[jeepxo]

Your telling me basically what I said I was going to do in my first post. It's great to hear that others validate what I want to do, the problem that I see is I need to import a great deal of data into a REAL database and not this psuedo crap called Frustration Maker Pro.

What I am coming to the realization of is unless I am going to store recipe cards or phone numbers, FMPro is a useless database with no ability to be used as a data repository in an enterprise environment.

I think that what I am going to have to do is spend a couple of days exporting the data from each FMPro database to a comma delimited or tab delimited file so that I can then pull it into SQL validate the data and take what is useful.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[lebisol]

well...FM is for Workgroups NOT Enterprise levels and can be frustrating coming from SQL based DBs.... I know your pain

u don't have to do CSV or tab crap....
Assuming that all the files (DBs) are the same structure then u can:
-make a 'master.fpx' by picking one of the databses and 'Save a Clone/copy' without any records. This will give you the structure and field names etc.

-then import all single files into 1 "master.fpx". This process is very easy and fields will match

then from your MSSQL perspective use the ODBC to import directly into it.

Take a look at quick export sample...

http://mostarnet.com/tutorials/filemaker/AutoExport.zip

All the best!

:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[MattiasJ]

Hi,

A simple but effective solution to your problem is this:

1. Make sure that all users have a password that you know about.
2. Make a new simple database called Master with all the fields you need.
3. Make a new database with a script that connects to the copy and imports the data via script or import to the master database that should reside on a server preferably on a FileMaker Server. Make the script start when the file opens.
4. Email the script database to all users. And tell them to open the database.

regards
/Mattias

 

[MattiasJ]

Hi again,

When this is accomplished export the data to a csv file and use DTS to import the data.

Regards
/Mattias