Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

To Read a File and Insert a part of the lines into the database

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting To Read a File and Insert a part of the lines into the database
# 1  
Old 11-13-2009
Question To Read a File and Insert a part of the lines into the database
Hi Guys

I need to have a shell script which reads a log file and insert a part of each line into the database. Some sample lines in the file are as below.

Code:
20091112091359 MED_RQACK     : user_data=60173054304,100232120,20091112091359,;ask_status=0;ask_reason=OK;msg_id=20091112091319
20091112091422 MED_RQACK     : user_data=60193360676,90436000,20091112091422,20091112091422;ask_status=0;ask_reason=OK;msg_id=20091112091360
20091112091441 MED_RQACK     : user_data=60192883529,77542478,20091112091441,20091112091441;ask_status=0;ask_reason=OK;msg_id=20091112091423
20091112091450 MED_RQACK     : user_data=60192818748,95312410,20091112091450,20091112091450;ask_status=0;ask_reason=OK;msg_id=20091112091442
20091112091451 MED_RQACK     : user_data=60133650310,100166770,20091112091451,;ask_status=0;ask_reason=OK;msg_id=20091112091451

What I have to do is, pick the values from (say in the first line)
60173054304,100232120,20091112091359, till the character ';'
and insert them into the database (a table).

The advantage (I guess) is that the values always start from the 42nd character of each line. I have to pick the line till I confront the character ';'. Then I have to divide that string into different fields delimited by a comma (','). Can somebody help me out on this. Smilie
Last edited by Franklin52; 11-13-2009 at 06:27 AM.. Reason: Please use code tags!
# 2  
Old 11-13-2009
To select the part from the file you can do something like:

Code:
awk -F"[=;]" '{print $2}' file

If you're using an Oracle database you can use the SQL loader, check this link:

SQL*Loader FAQ - Oracle FAQ
# 3  
Old 11-13-2009
Thanks a lot Smilie
Will get back if I have any queries. Thanks again
# 4  
Old 11-16-2009
In continuation to this...

I require a code to list all the files that have the extension "log" in a folder and then I have to process each of them, reading each line in the file and insert the contents of the file into the database. I tried searching for a similar query in the forum but cudn't find any. Kindly help me on this. One more condition is that the code has to pick the files that got accumulated today (only today) and then process them for the contents. The file name will have the time stamp appended. Smilie Thanks in advance.

Say "ImE-20091112-091359.log"
# 5  
Old 11-16-2009
Code:
DATE=`date +%Y%m%d`

for i in `find /PATH -name "*$DATE*.log"`
do
  # what you want to do
done

# 6  
Old 11-16-2009
Now I am trying to read the file line by lline and assign the fields to variables using awk.

say
Code:
v=echo `awk -F"," '{print $4}' tmpline.log`

tmpline.log is the temporary line from the file

I have to use these variables to be passed onto an sql procedure. The problem here is, if one of the fileds in the line is a null value then the variable has to be assigned the value 'NULL'.

How to do this?

---------- Post updated at 04:34 PM ---------- Previous update was at 04:00 PM ----------

Hey

I got it

Code:
echo `awk -F"," ' {if ( $4 ~ /^ *$/ ) printf("NULL")} ' tmpline.log`

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to make a loop to read the input from a file part by part?

Hi All, We've a VDI infrastructure in AWS (AWS workspaces) and we're planning to automate the process of provisioning workspaces. Instead of going to GUI console, and launching workspaces by selecting individual users is little time consuming. Thus, I want to create them in bunches from AWS CLI... (6 Replies)
Discussion started by: arun_adm
6 Replies

2. Shell Programming and Scripting

Read latest log files and perform database insert

Hi Experts, I have a situation where I need to write a shell script to continuously monitor a log directory with multiple log files and perform following: 1. Read the latest log file continuously and grep "Success" OR "Failure" 2. As it capture either Success or Failure, it has to perform a... (1 Reply)
Discussion started by: rish_max
1 Replies

3. Shell Programming and Scripting

Parse log file to insert into database

I have a log file that's created daily by this command: sar -u 300 288 >> /var/log/usage/$(date "+%Y-%m-%d")_$(hostname)_cpu.log It that contains data like this: Linux 3.16.0-4-amd64 (myhostname) 08/15/2015 _x86_64_ (1 CPU) 11:34:17 PM CPU %user %nice ... (12 Replies)
Discussion started by: unplugme71
12 Replies

4. Shell Programming and Scripting

How to read a text file line by line and insert into a database table?

I have a test file that I want to read and insert only certain lines into the the table based on a filter. 1. Rread the log file 12 Hours back Getdate() -12 Hours 2. Extract the following information on for lines that say "DUMP is complete" A. Date B. Database Name C.... (2 Replies)
Discussion started by: JolietJake
2 Replies

5. Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database. I need to write the korn shell program on Red Hat Enterprise Linux server. Oracle database is 10g. (15 Replies)
Discussion started by: shellguy
15 Replies

6. Shell Programming and Scripting

read database tablename from another file

i hava a file which contains database like emp dept loc now i want to create a script that read this file line by line and create a seperate query that ll be run in database like.. DELETE FROM TRS_ONE_ATDE WHERE DATA_DT = <from file> how can this be achieved. pls help. (5 Replies)
Discussion started by: mukeshguliao
5 Replies

7. Web Development

INSERT data to a Database Table from a text file

If you have a text file and if you want to Insert data to your Database Table, You can do it with these queries LOAD DATA LOCAL INFILE '/path/yourTextFile.txt' INTO TABLE yourTableName FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (0 Replies)
Discussion started by: sitex
0 Replies

8. Shell Programming and Scripting

how to insert data into database by reading it from a text file??

Hi....can you guys help me out in this script?? Below is a text file and it contains these: GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL005675 000001... (4 Replies)
Discussion started by: pallavishetty
4 Replies

9. Shell Programming and Scripting

How to insert data into MYSql database from a text file

Hi, Need to get help from you guys about this issue. I need to insert data into MySql database from a text file which is located in other server. The text file is something look like below: Date | SubscriberNo | Call Duration 20/7/07 | 123456788 | 20 20/7/07 | 123412344 | 30 The... (4 Replies)
Discussion started by: shirleyeow
4 Replies

10. Shell Programming and Scripting

how to insert data in database based on text file?

Hi....can you guys help me out in this script?? Below is a text file script....called Bukom.txt and it contains these: BUKOM 20060101 2.5 2.6 2.7 2.8 2.9 2.3 2.1 BUKOM 20060102 2.4 2.5 2.6 2.7 2.7 2.6 2.4 BUKOM 20060103 2.1 ... (9 Replies)
Discussion started by: forevercalz
9 Replies
Login or Register to Ask a Question