Solved

Export FileMaker data to Oracle SQL db

Posted on 2011-02-26
9
1,041 Views
Last Modified: 2012-06-21
I have a large FM DB with text, number, date, and timestamp fields that I need to export and upload to an Oracle SQL db. I normally do a small subset upload by exporting a csv file from FM, replacing ',' separators with '|' (comma to pipe delimiter), then upload a pipe-delimited text file to Oracle. The larger complete FBDB has several fields that are composed of strings of five alphanumeric codes each preceded by a comma that must maintain their original format (similar to a concatenated string of 10 ZIP codes with a comma delimiter and the first term preceded also by a comma). Thus, doing a search and replace of all commas with pipes is not feasible.

On the Oracle side, I can either upload a text file or use SQL Developer to write an INSERT or APPEND query.

What is the most suitable FM output?

What is a sample SQL INSERT query for LastName (text/varchar2), Age (number), DOB (date), and ArrivalTime (timestamp) into a schema Oracle::Demographics?
0
Comment
Question by:MarkJulie
[X]
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
  • 5
  • 3
9 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 34989012
ODBC, XML - both are options.

Retrieve via ODBC and then you can do whatever.

XML - XSLT - do whatever with the exported data and then import into Oracle.

Ss
0
 

Author Comment

by:MarkJulie
ID: 34989442
The partial answer appears to be export FM to ODBC or XML. The meat of the question remains, i.e., please provide baby steps for the Oracle upload.

1) Describe the type of file your answer will create with the exported FM data.

2) Write a sample SQL INSERT query for LastName (text/varchar2), Age (number), DOB (date), and ArrivalTime (timestamp) into a schema Oracle::Demographics given the FM data file in 1) above.

3) If your answer chooses not to use a SQL INSERT for uploading to Oracle, please give detailed advice on data manipulation for upload with an open source tool.

Mark

0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34989902
INSERT INTO DEMOGRAPHICS ( LastName, Age, DOB, ArrivalTime )
VALUES (....,  ...., .....)

Is this what you are talking about?

I do a fair amount of Oracle work and I use a tool called TOAD by Quest Software, http://www.quest.com/toad-for-oracle/.  They even have a SQL Server and MySQL version.  I find it an invaluable tool.  I could see creating an ODBC connect to both FM and Oracle and then simple insert into one table from the other table.  Might work.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:MarkJulie
ID: 34989915
Correct on the INSERT except that I will want the VALUES to be read from a file of a few dozen records and 198 fields on a monthly basis.

I know about TOAD for powerusers. For me, SQL Developer from Oracle is adequate, but I am not fluent in SQL other than with select, where, and order by. Reading from an external file presents an added level of complexity beyond my skill set, but I can modify a simple template with all the data types.

I will try the ODBC angle in the future when I have more time.
0
 
LVL 12

Accepted Solution

by:
North2Alaska earned 500 total points
ID: 34991220
Also check out SQLLDR (SQL Loader).  It's an Oracle tool that will do the insert very quickly.  You provide it a "Template" of what your data file will contain and the table you want to insert into and it does all the work.  I did a quick Google search and found lots of information like: http://www.orafaq.com/wiki/SQL*Loader_FAQ

You may not even need to swap the commas for the pipe as SQL Loader can handle pretty much any separator.
0
 

Author Comment

by:MarkJulie
ID: 34994575
SQL Loader at a glance looks Windows friendly, but I am on Snow Leopard. The Unix side looks like I would have to learn a lot of command line. Is there another solution available without the steep learning curve?

How would you write your INSERT using an external data file in a text editor level SQL tool if the external file is in: /home/demographics.dat?
0
 
LVL 12

Expert Comment

by:North2Alaska
ID: 34997120
I don't think you can say "Insert into from file".  You will need something to open and interpret the file.  This is what a tool like SQL Loader will do.  But you could also use perl, PHP, Java, etc...  You could also use FileMaker to write the file as a SQL command file with the Insert statements already constructed, but with large number of rows it is terribly inefficient.  But you said you were doing a couple hundred a month, it might not be too bad.
0
 

Author Comment

by:MarkJulie
ID: 34997336
I haven't tried this approach yet, but if I have select, insert, and delete privileges in the Oracle staging schema and set up the relationship in FMPA to allow creation and deletion of records in the Oracle table, can I create a shadow table in FMPA, export the 200 records from the .fp7 file, then import to the shadow table in Oracle with my ODBC connection?
0
 

Author Closing Comment

by:MarkJulie
ID: 35057823
The explanation of the solution was easy to follow, but the tool has a steeper learning curve than I have time to implement. That has no reflection on the solution itself.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
calculate days away 11 61
T-SQL: Please describe what a page split is 5 58
Read CLOB data from Oracle using JAVA 3 43
Populating a table from inside a trigger 2 16
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

738 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