Not able to write SQL query output in to .csv file with shell script.

UNIX for Beginners Q & A
1

I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line.

Main Code shell script:

this is my code:

#!/bin/bash
file="db_detail.txt"
. $file
rm /batch/corpplan/bin/dan.csv
SPOOL_FILE="/batch/corpplan/bin/dan.csv"
SQL="/batch/corpplan/bin/myquery.sql"
sqlplus -s  UserName/password@database << EOF
set linesize 60
spool on
set head on
set colsep ',';
SPOOL $SPOOL_FILE
@$SQL
SPOOL OFF
EOF

myquery.sql

SELECT DISTINCT ID_1,USER_NAME,TIME_POSTED FROM Shchemaname.tablename WHERE TIME_POSTED  >  to_date('05/31/2019', 'MM/DD/YYYY') and USER_NAME = 'PL26218';

My Current output is:

ID_1
------------------------------------------------------------
USER_NAME
------------------------------------------------------------
TIME_POSTED
---------------
1 - Choose COA Seed Options
PL26218
05-JUN-19

Budget Seed On_Off
PL26218
04-JUN-19

ID_1
------------------------------------------------------------
USER_NAME
------------------------------------------------------------
TIME_POSTED
---------------

1 - Choose COA Seed Options
PL26218
04-JUN-19

Desired output is :

ID_1, USER_NAME, TIME_POSTED
1 - Choose COA Seed Options,PL26218,05-JUN-19
Budget Seed On_Off,PL26218,04-JUN-19
1 - Choose COA Seed Options,PL26218,04-JUN-19
2

You should attempt to write the code to process (parse) your text.

Your shell script had no basic code to process the text in your output.

3

Those header "underlines" indicate that the first two colums are 60 characters wide and thus won't fit into linesize 60.You can

  • trim / chop the columns to sensible lengths
  • increase line size so all three fit
  • select "one column" only by concatenating the three trimmed column values.
4

Thanks Neo and RudiC for your inputs.

RudiC - i will increase the the line size and try.

Neo - Could you please give me some hint about the part i missed, I am new to unix.

5

Hi Guys,

I am able to get desired output after changing my shell script as mentioned below.

#!/bin/bash
file="db_detail.txt"
. $file
rm /batch/corpplan/bin/dan.csv
SPOOL_FILE="/batch/corpplan/bin/dan.csv"
SQL="/batch/corpplan/bin/myquery.sql"
sqlplus -s username/password@database << EOF
COLUMN ID_1 HEADING 'FORM_NAME' FORMAT A50
COLUMN USER_NAME FORMAT A50
COLUMN TIME_POSTED FORMAT A50
set linesize 200
spool on
set head on
set colsep '|';
SPOOL $SPOOL_FILE
@$SQL
SPOOL OFF
EOF
1 Like
6

Thanks for sharing your final code. Glad you found a solution. Pls be aware that this is NOT coming close to the desired output in post #1.

7

Hi RudiC,

Yes i changes column names and used '|' as delimiter.