txt file to oracle database

Shell Programming and Scripting
1

hiya,

i have a query: i want to read a file which contains:

2005/02/21 16:56:54.301: 111 PS (200, 10) sent <log instrument="FXA.ROSS" name="FXA.COSS.PAH.URR"><gid_id>1434025</gid_id><submit_id>22</submit_id><taker_name>shannop2</taker_name><taker_group_name>SupportClients/SupportUsers</taker_group_name><taker_account>Demo

2005/02/21 16:56:55.596: 111 PS (200, 10) sent <log instrument="FXA.ROSS" name="FXA.ROSS.PUR.URR"><gid_id>1534025</gid_id><submit_id>22</submit_id><taker_name>shannop2</taker_name><taker_group_name>SupportClients/SupportUsers</taker_group_name><taker_account>Demo

Now i want to grep just the timestamp (ie 16:56:54.301) & the gid_id (ie 1434025) in the first line & so on for all lines. Then insert this into a oracle database.

what i have done is i use the cut command to first cut the timestamp on each line & write to a file , then do the same with the gid_id. Finally paste the two files. It looks like this.

file1 : jo.txt

16:56:54.301
16:56:55.596

file2: jo1.txt

1434025
1534025

After paste, file3: jo3.txt

16:56:54.301 1434025
16:56:55.596 1534025

Question 1: is there an easeier way to do this?
Question 2: i want to read file3 (ie jo3.txt) & insert the contents into an oracle table

To do this, should i count the no. of lines in file3 (ie jo3.txt) using wc -l & then using a for loop connect to db using the sql -s user/passwd@server<<++>/dev/null & use the insert command?

would be great if i could get suggestions on this...plz

cheers,
Jo

2 
bash-2.05$ cat filename
2005/02/21 16:56:54.301: 111 PS (200, 10) sent <log instrument="FXA.ROSS" name="FXA.COSS.PAH.URR"><gid_id>1434025</gid_id><submit_id>22</submit_id><taker_name>shannop2</taker_name><taker_group_name>SupportClients/SupportUsers</taker_group_name><taker_account>Demo 

2005/02/21 16:56:55.596: 111 PS (200, 10) sent <log instrument="FXA.ROSS" name="FXA.ROSS.PUR.URR"><gid_id>1534025</gid_id><submit_id>22</submit_id><taker_name>shannop2</taker_name><taker_group_name>SupportClients/SupportUsers</taker_group_name><taker_account>Demo

bash-2.05$ awk '/^20/{a=index($0,"gid_id>");b=index($0,"</gid_id");print substr($2,0,length($2)-1)"\t"substr($0, a+7, b-a-7);}' filename
16:56:54.301    1434025
16:56:55.596    1534025
bash-2.05$
3

hey thanks for that peice of code but does anyone know how to import the contents of that file to an oracle table?

it wud be great if someone could help plzzzzzzz ...i really need to do this soon.

thanks,
jo

4

depends on your Oracle schema....
a good place to start is 'man sqlldr'

5

I've never seen a man page for sqlldr but here is a link where you can find out how to use it.

Thomas

6

thanks guys,

the oracle table has just 2 colunms ie Gid:varchar(38) & Time varchar(12)

table name:pir_ud
column:Gid:varchar(38)
column:Time varchar(12)

Hey Thomas, i tried that link but its asks for username/password

cheers,
jo

7

you can always register.....

another link might be FAQ sqlldr

8

Sorry about that. Register for a free account at Oracle's Technet.

Here is a simple control file to use:

# pir.ctl
LOAD DATA 
INFILE 'pir.dat' 
BADFILE 'pir.bad'
DISCARDFILE 'pir.dsc'

INTO TABLE "PIR_UD"
INSERT
(gid,time)

Put your data in the ".dat" file as comman delimited.

GID1,Value1
GID2,Value2

Invoke sqlldr as follows:

sqlldr control=pir.ctl data=pir.dat

Oracle should prompt you for your username and password.

Don't let this fool you though. If you increase your data's complexity, you are going to increase the complexity of the control file as well.

Good luck,

Thomas

9

Hi , I tried that piece of code it gave me the following error:

awk: record `2005/02/21 16:56:45....' too long
record number 1

cheers,
Jo

10

Hi guys, i'm being a bt dumn here but will be great if you can help!

I'm writing this script(named as Jo.sh), now i'm wondering if i need to include the ctl file within the unix script or should the ctl file be called from within the script file. Here are the details:

Should the script look like this:

#jo.sh (this is the script file on my unix machine)
nice grep sent pac.txt |nice grep 'log ins'|nice grep spot_markup|cut -c12-23>jo.txt
nice grep sent pac.txt |nice grep 'log ins'|nice grep spot_markup|cut -c107-113>jo1.txt

paste jo.txt jo1.txt>pir.dat

# pir.ctl

sqlplus -s asasas/asasa@asas.com << ++ > /dev/null

LOAD DATA
INFILE 'pir.dat'
BADFILE 'pir.bad'
DISCARDFILE 'pir.dsc'

INTO TABLE "PIRSUP_GID_ORDERS"
INSERT
(GID_ID,DEAL_TIME)

cheers,
Jo