There might be a requirement very often to dump the query results straightaway into CSV file format or in other words into a .CSV file. Here is what we need to do to achieve the same.
1) In case we are using OSQL through JCL to call the stored proc, then we need to add a new parameter (-s) as "," or ";" to indicate the character separator. E.g. SET PARM5= -s %";"%
The other parameter (-o) needs to be modified to specify (.CSV) as output file format instead of (.TXT).
2) This is not all. The stored proc needs to be modified as well to accommodate the changes. Please refer to these different approaches taken.
SELECTED APPROACH:
a) SELECT 'col_1' + ',' + 'col_2' + ',' + ' '
UNION ALL
SELECT CONVERT (varchar, col_1) + ','
+ CONVERT (varchar, col_2) + ',' + ' '
+ CONVERT (varchar, col_2) + ',' + ' '
ALTERNATIVE APPROACH:
a) SELECT 'col_1', ',' , 'col_2'
UNION ALL
SELECT CONVERT (varchar, col_1), ','
, CONVERT (varchar, col_2).................cont.
, CONVERT (varchar, col_2).................cont.
b) SELECT CONVERT( varchar,col_1) AS [col_1,]
,CONVERT( varchar,col_2) AS [col_2,]........cont.
,CONVERT( varchar,col_2) AS [col_2,]........cont.
NOTE:
Conversion to varchar is required to append the character separator.
ISSUES WITH ALTERNATIVE APPROACH:
1) Alignment problem with alphanumeric columns.
2) Space issues which could not be handled through generic sql trim functions.
3) Unoptimized file size.
4) Truncation of data in some cases if varchar size is not carefully specified.
5) Last column of unusual width.
UNSOLVED ISSUES:
1) The column header being present in the final CSV output file.
2) The date field losing its formatibility once converted to varchar and exported to excel.
Unresolved Issue # 1 can be resolved by the following approaches.
The SQL output contains 2 records, the Header name in the first line and the Line (-) in the second line. This can be removed by
1. We can create DTS package to export the data into Text file and call that DTS from the JCL script. This removes the header lines.
2. These lines can be skipped directly from the JCL script itself with the following code
We just need to provide the record length like [LRECL=357]
//SORTCPY EXEC PGM=SORT
//SORTIN DD DSN=&&UCMDFILE, DISP=OLD
//SORTOF01 DD DSN=&&UCMDSORT,
// DISP= (, PASS, DELETE),
// DCB= (RECFM=FB, LRECL=357),
// SPACE= (2048, (500,500), RLSE)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FILES=01, STARTREC=2
