Visit Our UNIX and Linux User Community


Reading csv and executing queries

 
Thread Tools Search this Thread
Operating Systems Linux Red Hat Reading csv and executing queries
# 1  
Old 08-08-2013
Reading csv and executing queries

hi all,

i have a csv file in which some queries are there and through a shell script
i am reading and trying to excute them but it is not working properly

my csv file is like this

Code:
Tid,table,query,values
1,PRD_FRG_FILE_JRNL,SELECT SWI_ID,FT_SWI
2,PRD_FRG_FILE_JRNL,SELECT COUNT(1),1


and my script is like this

Code:
#!/bin/bash -xv
hdr=1
while IFS=',' read -r f1 f2 f3 f4
do      if [ "$hdr" ]
        then    # Skip the 1st line from the input file.
                hdr=""
                continue
        fi

        Tid=$f
        Table=$f2
        query=$f3
        value=$f4
done < excel.txt
sqlplus $IB_RTE_CONN_STR << EOF
$query from $table;
EXIT
EOF

can any one please help
# 2  
Old 08-08-2013
You had "Table" for a variable, instead of "table":
Code:
#!/bin/bash -xv
IB_RTE_CONN_STR=scott/tiger
hdr=1
while IFS=',' read -r f1 f2 f3 f4
do      if [ "$hdr" ]
        then    # Skip the 1st line from the input file.
                hdr=""
                continue
        fi

        Tid=$f
        table=$f2
        query=$f3
        value=$f4
sqlplus -s /nolog << EOF
connect $IB_RTE_CONN_STR
$query from $table;
EXIT
EOF
done < file

Code:
$ cat myScript
#!/bin/bash
#-xv
IB_RTE_CONN_STR=scott/tiger
hdr=1
while IFS=',' read -r f1 f2 f3 f4
do      if [ "$hdr" ]
        then    # Skip the 1st line from the input file.
                hdr=""
                continue
        fi

        Tid=$f
        table=$f2
        query=$f3
        value=$f4
sqlplus -s /nolog << EOF
connect $IB_RTE_CONN_STR
$query from $table;
EXIT
EOF
done < file

$ cat file
Tid,table,query,values
1,EMP,SELECT MGR,FT_SWI
2,DEPT,SELECT DNAME,1

$ ./myScript

       MGR
----------
      7902
      7698
      7698
      7839
      7698
      7839
      7839
      7566

      7698
      7788

       MGR
----------
      7698
      7566
      7782

14 rows selected.


DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

This User Gave Thanks to Scott For This Post:
# 3  
Old 08-13-2013
Hi,
i used the previous script to compare to the values returned by the query with the values in the csv file.like this
Code:
!/bin/bash
hdr=1
while IFS=',' read -r f1 f2 f3 f4
do      if [ "$hdr" ]
        then    # Skip the 1st line from the input file.
                hdr=""
                continue
        fi

        Tid=$f
        table=$f2
        query=$f3
        value=$f4
val_1=$( sqlplus -s rte/rtet2@rel76t2 << EOF
set heading off
set feedback off
$query from $table;
EXIT
EOF
)
val_4=`echo $val_1 | tr '\n' ' '`

echo "val_5 : $val_4 "
if [ $val_4 == $value ]
then
   echo "test pass"
else
   echo "test fail"
fi

done < excel.txt


it gave the result like this

Code:
val_5 : EXINZTE
test pass
val_5 : 1
test pass
val_5 : SP2-0042: unknown command "from " - rest of line ignored.
./read.sh: line 24: [: too many arguments
test fail
val_5 : SP2-0042: unknown command "from " - rest of line ignored.
./read.sh: line 24: [: too many arguments
test fail

but i only want the result like this

Code:
val_5 : EXINZTE
test pass
val_5 : 1
test pass

but why two more values are compared , how to resolve it

---------- Post updated at 02:04 AM ---------- Previous update was at 02:02 AM ----------

the csv file used is like this

Code:
Tid,table,query,values
1,PRD_FRG_FILE_JRNL,SELECT SWI_ID,EXINZTE
2,PRD_FRG_FILE_JRNL,SELECT CONVERTED_RECS,1


Previous Thread | Next Thread
Test Your Knowledge in Computers #119
Difficulty: Easy
The Single UNIX Specification (SUS) is a standard for Unix systems to qualify and have permission to use the UNIX certification mark.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Reading CSV file

Hi experts, Im having csv file with few columns which should contain data as shown below. Want to check if column 3 contain row with duplicate value(9876,9876) then corresponding to this in col2 should contain text "tax" and should not contain text "non". Word "non" can come but if in column3... (2 Replies)
Discussion started by: as7951
2 Replies

2. Shell Programming and Scripting

Issue on executing db2 queries through shell script

hi i am trying to execute db2 queries through shell script. it's working fine but for few queries is not working ( those queries are taking time so the script is not waiting to get the complete the execution of that query ) could you please any one help me on this is there any wait... (1 Reply)
Discussion started by: bhaskar v
1 Replies

3. Shell Programming and Scripting

Creating a Continuous File Reading-Executing Shell Script

I need to write something that will read and execute all the files(Mainly executable scripts) inside one or more folders; in other words, a continuous chain with a break when finished. I'm new to shell and need syntax help. I'm on Ubuntu 12.10-Gnome btw. Here are some main highlights I think... (2 Replies)
Discussion started by: linuxlololol
2 Replies

4. Shell Programming and Scripting

Executing Multiple Queries in parallel in Shell

I have n number of SQL queries needs to executed in Shell. Result of this query need to assign in a variable. Once all the queries are executed script needs to exit. Sample Query: SQL 1: Select Count(*) from TABLE GROUP BY COL1,COL2 SQL 2: Select Count(*) from TABLE GROUP BY COL1,COL2 ... (2 Replies)
Discussion started by: Niranjancse
2 Replies

5. Shell Programming and Scripting

Executing set of sql queries from shell script

Hi All, I tried executing set of queries from shell script but not able to capture the input query in the log file. The code looks something similar to below sqlplus user/pwd@dbname << EOF > output.log $(<inputfile.txt) EOF The above code is capturing the output of queries into... (9 Replies)
Discussion started by: loggedin.ksh
9 Replies

6. Shell Programming and Scripting

Reading from a CSV and writing in same CSV file

Hi, I am tryng to read from a csv file and based on some grep command output I will modify one of the column in the same csv. Example:- Input CSV:- 20120829001415,noneAA,google.com 20120829001415,dfsafds,google.com 20120829001415,noneAA,google.com Intermediate Step:- If 2nd column of... (3 Replies)
Discussion started by: kmajumder
3 Replies

7. Shell Programming and Scripting

Executing informix queries using isqlrf

Hi, I have my informix queries in files named sql1.sql and sql2.sql. I am executing these queries in a remote informix db server.The data got as a result of executing sql1.sql and sql2.sql is got in file1.dat and file2.dat respectively. cmd= ssh "$USER_NAME"@"$HOST_NAME" "export... (0 Replies)
Discussion started by: Shri123
0 Replies

8. Shell Programming and Scripting

executing scripts by reading names from a file

file.txt contains ------------------ sat1 1300 sat2 2400 sat3 sat4 500 I need to write a shell script that will output like the below #output sat1.ksh 1300 sat2.ksh 2400 sat3.ksh sat4.ksh 500 my try ------- #!/bin/ksh for i in `cat file.txt` (3 Replies)
Discussion started by: konark
3 Replies

9. UNIX for Dummies Questions & Answers

Sh Shell Script executing remote SQL queries

Hi there folks, I am trying to execute remote sql queries on an Oracle server. I would like to save the result of the executed sql queries on a text file, and send that text file as an attachment to an email address. Could anyone give me an idea on how the above could be achieved? Any help... (2 Replies)
Discussion started by: Javed
2 Replies

10. Shell Programming and Scripting

bash: executing commands and reading exit vals

I have a function that returns a bunch of exit codes, say func1, and in my code I'm trying to execute that function in an if statement. This is the closest I could get. f1call=`func1 $arg1 $arg2` if ]; then ... fi When I run the script the function never gets called. What's the right way... (7 Replies)
Discussion started by: eur0dad
7 Replies

Featured Tech Videos