Spool CSV file using BASH Script | Errors with Output in generated CSV

Question

I need to spool the output of tables from db to CSV file. I checked and referred to How do I spool to a CSV formatted file using SQLPLUS?

But it's not working successfully. What I am getting is only the first 3 columns being separated by , and not the remaining. Others are getting separated by newline.

EDIT : Schema Details of Table being Spooled.

WWID               VARCHAR2 (5 Byte) 
TIMELOG_DATE       DATE 
TOTAL_HOURS        NUMBER (10,2) 
ACTIVITY           VARCHAR2 (100 Byte) 
SUBACTIVITY        VARCHAR2 (100 Byte) 
REF_PROJECT_ID     VARCHAR2 (30 Byte) 
REF_PROJECT_DESC   VARCHAR2 (250 Byte)
WORK_REQUEST_ID    VARCHAR2 (30 Byte)
EMP_COMMENTS       VARCHAR2 (100 Byte)
APPROVER_COMMENTS  VARCHAR2 (100 Byte)

Script:

echo "\n>>> ******Data Processing Started at `date '+%d-%m-%Y %T %Z'`******" >>${LOGFILE}

sqlplus $fims_user/$fims_pwd << EOF
set serveroutput on;
set colsep ,     ;-- separate columns with a comma
set pagesize 0   ;-- No header rows
set trimspool on ;-- remove trailing blanks
set headsep off  ;-- this may be useful...depends on your headings.

spool /home/fimsctl/datafiles/outbound/timelog/timelog_file_`date +%y%m%d`.csv

select * from FIMS_OWNER.TIMELOG_EXTRACT_OUTBOUND_T;

commit;
spool off
exit
EOF

echo "\n>>> ******Data Load Completed at `date '+%d-%m-%Y %T %Z'`******" >>${LOGFILE}

echo "End of the script">> ${LOGFILE}

And Output in CSV i am getting is:

SQL> select * from FIMS_OWNER.TIMELOG_EXTRACT_OUTBOUND_T;
iv315,29-DEC-14,          8
DUMMY

REF01
New Ref Project of type CPRM
66
NA


iv315,30-DEC-14,          8
DUMMY

REF01
New Ref Project of type CPRM
66
NA


iv315,31-DEC-14,          8
DUMMY

REF01
New Ref Project of type CPRM
66
NA

That is values are then separated by newline(when seen in wordpad)

Thanks

You may just need to set the linesize much higher, and/or set wrap off; but what is the table structure - what size are the columns that are being wrapped? And do you really want the whitespace padding the string values? — Alex Poole
– Alex Poole, Commented Jan 9, 2015 at 12:24
Ok, i am editing the description with table structure. For here, the part where , delimiter is changed to newline is a column declared as NUMBER(10,2) (which is showing value 8 in output as show in description) — Sachin
– Sachin, Commented Jan 9, 2015 at 12:27

Sachin · Accepted Answer · 2015-01-09 12:48:56Z

5

The defaut linesize is 80 characters, so by default columns will wrap onto new lines when that length will be exceeded. Your 100-byte columns will cause that behaviour. You can add SQL*Plus commands to change that:

-- any number at least as large as the longest possible output
set linesize 1024
set wrap off

select * is generally frowned up as the output can change unexpectedly if the table definition changes - i.e. a column is added - or if the table has columns in different orders in different environments. (Which arguably shouldn't happen with source control, and generally doesn't matter as long as you don't use *. If you list the columns you want explicitly, it isn't much extra work to concatenate them with manually-added separators, e.g:

select wwid
  ||','|| to_char(timelog_date, 'DD-MON-YY') -- or another format
  ||','|| total_hours
  ||','|| activity
  ||','|| subactivity
  ... -- etc
from FIMS_OWNER.TIMELOG_EXTRACT_OUTBOUND_T;

That will remove extra whitespace that is currently going to pad all the CSV rows to the same length, and reduce the output file size. The output is then a single column, so the colsep setting isn't relevant any more.

edited Jan 9, 2015 at 12:48

Sachin

1,00312 silver badges31 bronze badges

answered Jan 9, 2015 at 12:37

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sachin Over a year ago

It worked. Thanks. And about using select *, it was for debugging just. Thanks a lot :-)

Alex Poole Over a year ago

@Sachin - thanks; RR is a valid date model, but I guess YY is just as good for output, if you really want a two-digit year.

Sachin Over a year ago

Yes actually. I changed it coz, for 50-99 are stored as 1950-1999, and dates ending in 00-49 are stored as 2000-2049. And I am developing an enterprise application for much older company existing since 1919 and thus have a lot of data to be migrated. Just in that case ;-) Thanks

Sachin Over a year ago

may I disturb you for one more thing. Actually my column outputs are listing with so many spaces. e.g. iv315,31-DEC-14,<spaces>8,DUMMY<so many spaces>,REF01<spaces>,New Ref Project of type CPRM<spaces>,66<spaces>,NA<spaces> ..... The same number spaces are being adding as per remaining characters according to column definition... Any solution other than select trim(col1),trim(col2),trim(col3) from tablename... As so many times call for trim() function is decreasing the performance a bit.

Alex Poole Over a year ago

@Sachin - trim won't help if you're relying on colsep; that's why I prefer concatenation.

|

Collectives™ on Stack Overflow

Spool CSV file using BASH Script | Errors with Output in generated CSV

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related