Script for parsing a text file and inserting into DB

towhidgrep · 06-20-2011, 01:56 AM

I have a csv file like following

data1, data2, data3, data4
rdata1, rdata2, rdata3, rdata4
....
...
...
..
.
sdata1, sdata2, sdata3, sdata4

Now i have to parse data3 and data4 of every line and send as variable to a sql select command which will connect to a DB and find out two information and lastly create a new file like following

data1, data2, data3, data4, data5, data6
...
..
.

I have done the parsing portion like this
#!/bin/bash
# Set the field seperator to a newline
IFS="
"
file=$1
cat $file | while read fileline
do
col8=$(echo $fileline | cut -d',' -f8)
col9=$(echo $fileline | cut -d',' -f9)
echo $col8 $col9
done

Now i need to do the rest of the part ..can any one help me.

Thanks in advance

grail · 06-20-2011, 02:56 AM

So are you saying you do not know how to write the sql query?

Also i am not sure how the code (which you should place in [code][/code] tags to make readable)
is doing what you want?? You say you want the third and fourth columns but then use cut to get fields eight and nine??

Also, setting IFS and using cat are not required at all as the while loop can already handle both:

Code:

while read -r fileline
do
    <blah>
done<"$file"

towhidgrep · 06-20-2011, 04:30 AM

Sorry Bro it should be -f3 and -f4. I was just working with another script and i copied it from there. I know the query but i dont know how to integrate the query with scripting and how to generate another csv file with some portion from first csv file and another portion that query. And I am not setting IFS also.

Thanks

Ramurd · 06-20-2011, 05:14 AM

so you get something like this:

Code:

while read -r fileline
do
  col3=`echo ${fileline} | cut -d',' -f3`
  col4=`echo ${fileline} | cut -d'.' -f4`
  sql=`/path/to/sql_command_line_tool -parameters "SELECT field1,field2 FROM table1 WHERE condition"`
  col_appd1=`echo ${sql} | cut -d'separator' -f1`
  col_appd2=`echo ${sql} | cut -d'separator' -f2`
  echo "${col3},${col4},${col_appd1},${col_appd2}" >> newfile
done < $file

I'm not sure what you really want... your description of the problem is a bit vague.

catkin · 06-20-2011, 08:20 AM

No need for the cut either, a combination of IFS and read can do the parsing like this (with added error trapping and a simulated SQL script):

Code:

#!/bin/bash

# Configure script environment
set -o nounset
unalias -a

# Gopher it
IFS=', '
i=0
while read -r col1 col2 col3 col4 rest
do
    (( i++ ))
    if [[ $rest != '' ]]; then
        echo "ERROR: unexpected extra data on line $i; skipping" >&2
        continue
    fi
    SQL_data=$( echo $col3.XX $col4.XX )
    echo $col1, $col2, $col3, $col4, ${SQL_data% *}, ${SQL_data#* }
done < 'LQ-887263.txt'