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

Shell Programming and Scripting
1

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.

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. :smiley:

2

To select the part from the file you can do something like:

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

Thanks a lot :smiley:
Will get back if I have any queries. Thanks again

4

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. :slight_smile: Thanks in advance.

Say "ImE-20091112-091359.log"

5 
DATE=`date +%Y%m%d`

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

Now I am trying to read the file line by lline and assign the fields to variables using awk.

say

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

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