Need help on writing data to csv file

tucs_123 · 12-20-2008, 12:12 PM

Hi,

I want to copy result of two diffrent sybase queries in adjacent cells of the csv file ,say

query 1:select fund_id from table1 where fund_code=123
result1:ABC

query 2:select end_date from table2 where fund_code=123
result2:31-Dec-2008

cell1 | cell2
______ |_____
result1 | result2
_______ |________

I am using following command to send data to csv file.

isql -Sserver -Ppassword -o result.csv

Also please suggest how to append data to csv file for more queries.

Thanks in advance.....

crabboy · 12-20-2008, 12:51 PM

Code:

select t1.fund_id || ',' || t2.end_date 
   from table1 t1, table2 t2 
  where t1.fund_code = t2.fund_code and 
        t1.fund_code = '123';

You'll probably want to change the fund_code = '123' to increase your result set.

I think this is what you are asking for.

sec-per · 12-20-2008, 08:18 PM

Probably

Code:

man join

can help

tucs_123 · 12-21-2008, 09:26 AM

Thanks crabboy for your quick reply...yaa..I think this query will work....But I had given just sample queries.

My actual queries are:

1)select min(start_time) from table1 where req_name='s_20080709' and end_date='6/30/2008'

2)select max(upd_time) from table1 where req_name='s_20080709' and end_date='6/30/2008'

3)select min(start_time) from table1 where req_name='s_20080709' and end_date='7/31/2008'

4)select max(upd_time) from table1 where req_name='s_20080709’ and end_date='7/31/2008'

As you can see table name is same for all the queries but end_date is different for first two and last two queries.

I can combine query1 and query2 to single query and pass result to csv file and can combine query3 and query4 to single query and pass result to csv file.

How can I handle the field end_date,so that I can combine the result of all four queries and pass it to csv file.so that it will look like in csv file as below.

server1: result1 |result2| result3| result4|

Please help on how to append data to csv file as I will be running these quries for different servers.

server1: result1 |result2| result3| result4|
server2: result1 |result2| result3| result4|

crabboy · 12-21-2008, 11:51 AM

You did not specify what database you are using, but for Oracle, something like this will work:

Code:

create or replace procedure test1( condition1 varchar2, condition2 varchar2, condition3 varchar2) as
lv_min varchar2(20);
lv_max varchar2(20);
lv_min2 varchar2(20);
lv_max2 varchar2(20);

BEGIN
   select min(start_time) into lv_min from aps_account where req_name=condition1 and end_date=condition2;
   select max(upd_time) into lv_max from aps_account  where req_name=condition1 and end_date=condition2;
   select min(start_time) into lv_min2 from aps_account where req_name=condition1 and end_date=condition3;
   select max(upd_time) into lv_max2 from aps_account where req_name=condition1 and end_date=condition3;
   
   dbms_output.put_line('Server x: ' || lv_min || '|' || lv_max || '|' || lv_min2 || '|' || lv_max2 );
END;

Execute it like this:

Code:

SQL> execute test1( 's_20080709’, '6/30/2008', '7/31/2008'  );

To execute it from a script:
test.sql

Code:

set serveroutput on;
execute test1( 's_20080709’, '6/30/2008', '7/31/2008'  );
exit

A test script, test.sh:

Code:

OUTFILE=test.out
sqlplus -S dbuser/dbpass@sid @test.sql | grep Results >> $OUTFILE

tucs_123 · 12-21-2008, 11:29 PM

Thanks a lot crabboy......

I am using Sybase as database as I used command "isql -Sserver -Ppassword -o result.csv" in my first post...As per your reply,got an idea how to proceed further...I will try to use that for Sybase.

tucs_123 · 12-23-2008, 12:34 PM

Hi crabboy.....could you please post the solution for Sybase as well in detail as you provided for the oracle like creating procedure,running it,passing parameters to it etc.as I tried to work on that but getting some errors with Sybase procedure....

tucs_123 · 12-24-2008, 08:08 AM

Hi,

I am executing Sybase procedure test3

create procedure test3( @condition1 varchar(20), @condition2 varchar(20), @condition3 varchar(20) ) as

DECLARE
@lv_min varchar(20),
@lv_max varchar(20),
@lv_min2 varchar(20),
@lv_max2 varchar(20)

begin
select @lv_min=(select min(start_time) from tabel1 where requestor_name= @condition1 and end_date= @condition2)
select @lv_max=(select max(upd_time) from tabel1 where requestor_name= @condition1 and end_date= @condition2)
select @lv_min2=(select min(start_time) from tabel1 where requestor_name= @condition1 and end_date= @condition3)
select @lv_max2=(select max(upd_time) from tabel1 where requestor_name= @condition1 and end_date= @condition3)
select @lv_min
select @lv_max
select @lv_min2
select @lv_max2
end

>exec test3 's_20080709’, '6/30/2008', '7/31/2008'

and the results are

------
result1

-------
result2

-------
result3

-------
result4

How to copy these results to csv file,so that it will look like..

cell1 cell2 cell3 cell4
result1|result2|result3|result4

tucs_123 · 12-25-2008, 05:35 AM

Hi,

Can someone help me in copying data from a text file to csv file using shell script.

Say my text file is hi.txt with following contents:

monday tuesday wedneday

I can use:

cat hi.txt > queue.csv

but the output is not copied properly to csv file.

it should look like:

cell1 | cell2 | cell3
monday | tuesday | wedneday

i.e.first text from text file should go in first cell of csv file,second text in second cell and so on...

Thanks in advance.......

pixellany · 12-25-2008, 06:28 AM

Please do not start multiple threads on the same subject. I'm merging the two recent ones into your original thread.

tucs_123 · 12-28-2008, 06:15 AM

Hi,

Can any one tell me how to copy data to a excel/csv file from text file to other columns,leaving first column unaffected i.e it should not overwrite data in first column using shell script.

Say my text file data is:

15-dec-2008 15-dec-2009
16-dec-2008 16-dec-2009

say my first excel column is:

column1 column2 column3
server1
server2

I want output as below:

column1 column2 column3
server1 15-dec-2008 15-dec-2009
server2 16-dec-2008 16-dec-2009

Thanks in advance........

sec-per · 12-28-2008, 11:19 PM

Hi,

Try this :

1- place the server list in a file (f1.txt)
2- convert the excel file into a space separated file (say f2.txt)

Then type

Code:

paste f1.txt f2.txt > resutl.txt

This will produce a space/tab separated file.

To get a cvs file, type

Code:

paste f1.txt f2.txt  | sed 's/[\t| ]/;/g' > resutl.cvs

Hope it helps.

tucs_123 · 12-29-2008, 03:42 AM

hey thanks buddy...it helped me a lot.....