Reading csv and executing queries

Operating Systems Linux Red Hat Reading csv and executing queries

Prev Next

08-08-2013

Registered User

52, 0

Join Date: Mar 2013

Last Activity: 17 October 2013, 6:57 AM EDT

Posts: 52

Thanks Given: 7

Thanked 0 Times in 0 Posts

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

ramsavi

View Public Profile for ramsavi

Find all posts by ramsavi

Previous Thread | Next Thread

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

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

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

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

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

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

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`

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

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

LEARN ABOUT DEBIAN

class::csv

CSV(3pm)						User Contributed Perl Documentation						  CSV(3pm)

NAME

       Class::CSV - Class based CSV parser/writer

SYNOPSIS

	 use Class::CSV;

	 my $csv = Class::CSV->parse(
	   filename => 'test.csv',
	   fields   => [qw/item qty sub_total/]
	 );

	 foreach my $line (@{$csv->lines()}) {
	   $line->sub_total('$'. sprintf("%0.2f", $line->sub_total()));

	   print 'Item:     '. $line->item(). "
".
		 'Qty:	    '. $line->qty(). "
".
		 'SubTotal: '. $line->sub_total(). "
";
	 }

	 my $cvs_as_string = $csv->string();

	 $csv->print();

	 my $csv = Class::CSV->new(
	   fields	  => [qw/userid username/],
	   line_separator => "
";
	 );

	 $csv->add_line([2063, 'testuser']);
	 $csv->add_line({
	   userid   => 2064,
	   username => 'testuser2'
	 });

DESCRIPTION

       This module can be used to create objects from CSV files, or to create CSV files from objects. Text::CSV_XS is used for parsing and
       creating CSV file lines, so any limitations in Text::CSV_XS will of course be inherant in this module.

       EXPORT

       None by default.

METHOD

       CONSTRUCTOR

       parse
	   the parse constructor takes a hash as its paramater, the various options that can be in this hash are detailed below.

	   Required Options
	       o   fields - an array ref containing the list of field names to use for each row.  there are some reserved words that cannot be
		   used as field names, there is no checking done for this at the moment but it is something to be aware of. the reserved field
		   names are as follows: "string", "set", "get". also field names cannot contain whitespace or any characters that would not be
		   allowed in a method name.

	   Source Options (only one of these is needed)
	       o   filename - the path of the CSV file to be opened and parsed.

	       o   filehandle - the file handle of the CSV file to be parsed.

	       o   objects - an array ref of objects (e.g. Class::DBI objects). for this to work properly the field names provided in fields needs
		   to correspond to the field names of the objects in the array ref.

	       o   classdbi_objects - depreciated use objects instead - using classdbi_objects will still work but its advisable to update your
		   code.

	   Optional Options
	       o   line_separator - the line seperator to be included at the end of every line. defaulting to "
" (unix carriage return).

       new the new constructor takes a hash as its paramater, the same options detailed in parse apply to new however no Source Options can be
	   used. this constructor creates a blank CSV object of which lines can be added via add_line.

       ACCESSING

       lines
	   returns an array ref containing objects of each CSV line (made via Class::Accessor). the field names given upon construction are
	   available as accessors and can be set or get. for more information please see the notes below or the perldoc for Class::Accessor. the
	   lines accessor is also able to be updated/retrieved in the same way as individual lines fields (examples below).

	   Example
	       retrieving the lines:

		 my @lines = @{$csv->lines()};

	       removing the first line:

		 pop @lines;

		 $csv->lines(@lines);

	       sorting the lines:

		 @lines = sort { $a->userid() <=> $b->userid() } @lines:

		 $csv->lines(@lines);

	       sorting the lines (all-in-one way):

		 $csv->lines([ sort { $a->userid() <=> $b->userid() } @{$csv->lines()} ]);

	   Retrieving a fields value
	       there is two ways to retrieve a fields value (as documented in Class::Accessor). firstly you can call the field name on the object
	       and secondly you can call "get" on the object with the field name as the argument (multiple field names can be specified to
	       retrieve an array of values). examples are below.

		 my $value = $line->test();

	       OR

		 my $value = $line->get('test');

	       OR

		 my @values = $line->get(qw/test test2 test3/);

	   Setting a fields value
	       setting a fields value is simmilar to getting a fields value. there are two ways to set a fields value (as documented in
	       Class::Accessor).  firstly you can simply call the field name on the object with the value as the argument or secondly you can call
	       "set" on the object with a hash of fields and their values to set (this isn't standard in Class::Accessor, i have overloaded the
	       "set" method to allow this). examples are below.

		 $line->test('123');

	       OR

		 $line->set( test => '123' );

	       OR

		 $line->set(
		   test  => '123',
		   test2 => '456'
		 );

	   Retrieving a line as a string
	       to retrieve a line as a string simply call "string" on the object.

		 my $string = $line->string();

       new_line
	   returns a new line object, this can be useful for to "splice" a line into lines (see example below). you can pass the values of the
	   line as an ARRAY ref or a HASH ref.

	   Example
		 my $line = $csv->new_line({ userid => 123, domainname => 'splicey.com' });
		 my @lines = $csv->lines();
		 splice(@lines, 1, 0, $line);

	       OR

		 splice(@{$csv->lines()}, 1, 0, $csv->new_line({ userid => 123, domainname => 'splicey.com' }));

       add_line
	   adds a line to the lines stack. this is mainly useful when the new constructor is used but can of course be used with any constructor.
	   it will add a new line to the end of the lines stack. you can pass the values of the line as an ARRAY ref or a HASH ref. examples of
	   how to use this are below.

	   Example
		 $csv->add_line(['house', 100000, 4]);

		 $csv->add_line({
		   item     => 'house',
		   cost     => 100000,
		   bedrooms => 4
		 });

       OUTPUT

       string
	   returns the object as a string (CSV file format).

       print
	   calls "print" on string (prints the CSV to STDOUT).

SEE ALSO

       Text::CSV_XS, Class::Accessor

AUTHOR

       David Radunz, <david@boxen.net>

COPYRIGHT AND LICENSE

       Copyright 2004 by David Radunz

       This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.10.0							    2007-02-08								  CSV(3pm)

Red Hat