×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

populate database table with the the contents of flat file.

populate database table with the the contents of flat file.

populate database table with the the contents of flat file.

(OP)
Hello All,

In a Korn Shell,

I am trying to populate database table with the the contents of flat file.
Here is the sample of my flat file and its contents: sample_file.txt

- source: "\"GRP\".\"GEC_CLAIM_EVENT_SRI_PROCESS\""
target: "\"GRP\".\"GEC_CLAIM_EVENT_SRI_PROCESS\""
rsid: c0a8a91c-26-0
flags: 0
- source: "\"GRP\".\"GPB_BONUS_PMT_SMMRY\""
target: "\"GRP\".\"GPB_BONUS_PMT_SMMRY\""
rsid: c0a8a91c-26-0
flags: 0
- source: "\"SIC\".\"CCP_ADP_DETAIL\""
target: "\"SIC\".\"CCP_ADP_DETAIL\""
rsid: c0a8a91c-26-0
flags: 0

Here is the intended structure of the population of the destination table. Note that the table already has the column headings:

Here is the attempt:
#!/bin/ksh

USER=splex
PASS=splexpassword
DATABASE=mydbname

sqlplus -s /nolog << EOF
conn ${USER}/${PASS}@${DATABASE}
for i in `cat file.txt`
do
col1=`cat $i | awk -F ',' '{print $1}'`
col2=`cat $i | awk -F ',' '{print $2}'`
col3=`cat $i | awk -F ',' '{print $3}'`
col4=`cat $i | awk -F ',' '{print $4}'`
col5=`cat $i | awk -F ',' '{print $5}'`
col6=`cat $i | awk -F ',' '{print $6}'`
col7=`cat $i | awk -F ',' '{print $7}'`

INSERT INTO TEST_TAB
(COLUMN1, COLUMN2.....................)
VALUES
($col1, $col2, $col3,.................)
commit;
done
--doing other operations in the database after the aoove
set pagesize 0 linesize 4000 feedback off trimspool on

spool rowcount.txt;
select source_name from splex.count_match_vw;
spool off;

EOF

However the above is not helping.
Please Help.

RE: populate database table with the the contents of flat file.

The best tool for that job is Oracle's SQL Loader. You'll need to learn it's commands and syntax, but it's by far the best way to load files into tables. In the long run it will be much easier to maintain than a custom shell script. Plus it's much faster when the files to load get bigger.

You might need to reformat your input (one line per record) and get rid of some of the noise (remove the quoted back-slashes), but loading flat files is a simple task for SQL Loader. It's what it was made for.

A very rough untested example...

Here's the data file...

CODE --> sample_file.txt

GRP.GEC_CLAIM_EVENT_SRI_PROCESS,GRP.GEC_CLAIM_EVENT_SRI_PROCESS,c0a8a91c-26-0,0
GRP.GPB_BONUS_PMT_SMMRY,GRP.GPB_BONUS_PMT_SMMRY,c0a8a91c-26-0,0
SIC.CCP_ADP_DETAIL,SIC.CCP_ADP_DETAIL,c0a8a91c-26-0,0 

Here's a parameter file for our login...

CODE --> parfile.dat

userid=splex/splexpassword 

Here's a VERY simple control file for SQL Loader...

CODE --> sqlldr-append.ctl

load data
 infile '/path/to/files/sample_file.txt'
 append
 into table test_tab
 fields terminated by ","
 ( source_name, target_name, routing_number, flag ) 

And a command to do the load...

CODE

sqlldr parfile=/path/to/files/parfile.dat control=/path/to/files/sqlldr-append.ctl 

You can even have it send bad records to a separate output file so you can handle them as exceptions. Just Google "Oracle SQL Loader examples" and you'll get all kinds of things. It's pretty powerful and a good tool to know how to use.

RE: populate database table with the the contents of flat file.

Agree with SamBones, SQL Loader is much better for loading files. Faster, too.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: populate database table with the the contents of flat file.

(OP)
Thanks fellas, I am familiar with sql loader and datapump as well.
I thought there is a one line awk command that i can use to load this data.

RE: populate database table with the the contents of flat file.

No awk one liners for this one. awk doesn't know how to talk to an Oracle database.

You can do it with a shell script. The example you give in your post needs work, but it's pointed in the right direction. I wouldn't personally spend time developing it though when you actually have a tool created for loading files into an Oracle database available.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close