Visit Our UNIX and Linux User Community


txt file to oracle database


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting txt file to oracle database
# 1  
Old 03-05-2005
txt file to oracle database

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  
Old 03-07-2005
Code:
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  
Old 03-07-2005
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  
Old 03-07-2005
Quote:
Originally Posted by jorhul
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
depends on your Oracle schema....
a good place to start is 'man sqlldr'
# 5  
Old 03-07-2005
Quote:
a good place to start is 'man sqlldr'
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  
Old 03-07-2005
thanks guys,

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

table nameSmilieir_ud
column:Gid:varchar(38)
column:Time varchar(12)

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

cheers,
jo
# 7  
Old 03-07-2005
Quote:
Originally Posted by jorhul
thanks guys,

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

table nameSmilieir_ud
column:Gid:varchar(38)
column:Time varchar(12)

Hey Thomas, i tried that link but its asks for username/password
you can always register.....

another link might be FAQ sqlldr

Previous Thread | Next Thread
Test Your Knowledge in Computers #248
Difficulty: Easy
ARPANET was designed to reliably transmit 911 messages, and to inform the host computer when it loses a message.
True or False?

6 More Discussions You Might Find Interesting

1. UNIX and Linux Applications

Identify a specific environment Oracle variable to connect a remote Oracle database ?

Good evening I nned your help pls, In an unix server i want to connect to a remote oracle databse server by sqlplus. I tried to find out the user/passwd and service name by env variable and all Ive got is this: ORACLE_SID_REPCOL=SCL_REPCOL ORACLE_SID=xmeta ORACLE_SID_TOL=SCL_PROTOLCOL... (2 Replies)
Discussion started by: alexcol
2 Replies

2. Shell Programming and Scripting

shellscript to read data from txt file and import to oracle db

Hi all, Help needed urgently. I am currently writing a shellscript to read data/record from a flat file (.txt) file, and import/upload the data to oracle database. The script is working fine, but it takes too long time (for 18000 records, it takes around 90 mins). I guess it takes so long... (1 Reply)
Discussion started by: robot_mas
1 Replies

3. Shell Programming and Scripting

Using csh / awk / sed to compare database sizes in a txt file

Hello, I have an output file showing database sizes across the 3 environments that I use (LIVE, TEST & DEVELOPMENT). I am trying to write a script that lets me know if the size of a db on one environment is different to its corresponding db on the other environments. Here is an example... (4 Replies)
Discussion started by: stevie_g
4 Replies

4. Solaris

Can't create database after Oracle Database installation

I installed Oracle 10 software on Solaris 11 Express, everything was fine execpt I can't create database using dbca.rsp file. I populated file with following options. OPERATION_TYPE = "createDatabase" GDBNAME = "solaris_user.domain.com" SID = "solaris_user" TEMPLATENAME = "General... (0 Replies)
Discussion started by: solaris_user
0 Replies

5. Shell Programming and Scripting

Find similar entry in a .txt file acting as a database.

May i know how do i go along finding similar entry in a .txt file, which is used a as a "database" and post and error saying the entry existed when we key in the entry. ---------- Post updated at 05:18 PM ---------- Previous update was at 05:16 PM ---------- i mean post an error saying the... (5 Replies)
Discussion started by: santonio
5 Replies

6. Shell Programming and Scripting

how to extract the data from database (oracle) and send the output as an .xls file?

Hi, How to extract the data from Oracle database and sent the output data to mails using mailx command with .xls attachement? Here i know how to connect the database using unix shell script and how to use the mailx command in UNIX script But i don't know how to use the .xls format file (i... (1 Reply)
Discussion started by: psiva_arul
1 Replies

Featured Tech Videos