.CSV Output With Headers Using SQL

Harry_S690 · #1 (**permalink**) 06-05-11, 20:38

Hi,

I have a sql which generates .csv compatible output, but im not getting headers in it. My sql is like this
Select
'"'||accountno||'",'||'"'||accountname||'",'||'"'| |email||'"'
from account_details

Please tell me how i can get column headers in this sql or provide alternate sql to use. I need to output this to a .csv file with column headers. My output looks like this
"12","test_12","test12@gmail.com"
"13","test_13","test13@gmail.com"

* If there is another way to put SQL Query output to .csv(Excel) file please suggest how it can be done, we were going to put the above sql in shell script but dont know of a proper method. Kindly give reply in detail. Appreciate your help.

Thank you.

db2girl · #2 (**permalink**) 06-05-11, 20:56

Maybe this is what you're looking for:
How to fetch Column names using a select query?

Harry_S690 · #3 (**permalink**) 06-05-11, 21:16

Hi i saw the link you posted if you see my query it is exactly as below.
Select
'"'||accountno||'",'||'"'||accountname||'",'||'"'| |email||'"'
from account_details

So how can i have another query with just column names in it and how can one do union all if the original query is as above with " qualifier for column headers. Also can one have a .csv compatible output wihtout using " " for column headings??

tonkuma · #4 (**permalink**) 06-05-11, 21:58

VALUES '"accountno","accountname","email"'
UNION ALL
...

Harry_S690 · #5 (**permalink**) 06-05-11, 22:18

Quote:

Originally Posted by tonkuma

VALUES '"accountno","accountname","email"'
UNION ALL
...

Hi,

Thanks for your response I am assuming this is using the Command Line Export command?? and the Union All follows with the Select Query..

But, how would one tackle this situation only by joining two queries one the actual select and the other with column headers only?

Thanks

tonkuma · #6 (**permalink**) 06-05-11, 22:49

Quote:

But, how would one tackle this situation only by joining two queries one the actual select and the other with column headers only?

I couldn't understand the statement, perhaps by my poor English capabilities.

Anyhow, what problem do you have with my example?

Here is another export sample delimitted by double quotation marks.

Code:

------------------------------ Commands Entered ------------------------------
EXPORT TO emp_with_header.csv OF DEL
MODIFIED BY nochardel
VALUES ' "empno","fullname","workdept","edlevel"'
UNION ALL
SELECT '"' || empno ||'","'
       || firstnme || RTRIM(' ' || midinit) || ' ' || lastname
       || '","' || workdept || '","'
       || VARCHAR(edlevel) || '"'
 FROM  employee
 ORDER BY
       1
;
------------------------------------------------------------------------------
SQL3104N  The Export utility is beginning to export data to file 
"emp_with_header.csv".

SQL3105N  The Export utility has finished exporting "33" rows.


Number of rows exported: 33

Contents of "emp_with_header.csv" was like...

Code:

 "empno","fullname","workdept","edlevel"
"000010","CHRISTINE I HAAS","A00","18"
"000020","MICHAEL L THOMPSON","B01","18"
"000030","SALLY A KWAN","C01","20"
"000050","JOHN B GEYER","E01","16"
"000060","IRVING F STERN","D11","16"
"000070","EVA D PULASKI","D21","16"
"000090","EILEEN W HENDERSON","E11","16"
"000100","THEODORE Q SPENSER","E21","14"
"000110","VINCENZO G LUCCHESSI","A00","19"
"000120","SEAN O'CONNELL","A00","14"
"000130","DELORES M QUINTANA","C01","16"
"000140","HEATHER A NICHOLLS","C01","18"
"000150","BRUCE ADAMSON","D11","16"
"000160","ELIZABETH R PIANKA","D11","17"
"000170","MASATOSHI J YOSHIMURA","D11","16"
"000180","MARILYN S SCOUTTEN","D11","17"
"000190","JAMES H WALKER","D11","16"
"000200","DAVID BROWN","D11","16"
"000210","WILLIAM T JONES","D11","17"
"000220","JENNIFER K LUTZ","D11","18"
"000230","JAMES J JEFFERSON","D21","14"
"000240","SALVATORE M MARINO","D21","17"
"000250","DANIEL S SMITH","D21","15"
"000260","SYBIL P JOHNSON","D21","16"
"000270","MARIA L PEREZ","D21","15"
"000280","ETHEL R SCHNEIDER","E11","17"
"000290","JOHN R PARKER","E11","12"
"000300","PHILIP X SMITH","E11","14"
"000310","MAUDE F SETRIGHT","E11","12"
"000320","RAMLAL V MEHTA","E21","16"
"000330","WING LEE","E21","14"
"000340","JASON R GOUNOT","E21","16"

Harry_S690 · #7 (**permalink**) 06-06-11, 00:07

Hi,

What i was looking for is not using the EXPORT utility but something within the SQL query itself which would modify it to include headers, for example your query below can we modify it to have headers to make it union(with another query) all somehow with headers?
SELECT '"' || empno ||'","'
|| firstnme || RTRIM(' ' || midinit) || ' ' || lastname
|| '","' || workdept || '","'
|| VARCHAR(edlevel) || '"'
FROM employee
ORDER BY
1
;

tonkuma · #8 (**permalink**) 06-06-11, 00:23

I think that I already showed two examples.

For example:

Code:

VALUES ' "empno","fullname","workdept","edlevel"'
UNION ALL
SELECT '"' || empno ||'","'
...

What was the problem in it?

ashish_sapkal · #9 (**permalink**) 04-26-12, 11:25

I am trying to export data which is similar to the posts above.
But the result of this export gives nothing. I just get the column headers but there is no data(for the second select)
I think the Select statement cannot handle strings more than 255 characters.
Is there any workaround for this? typecasting, or something else?

EXPORT TO ABCDUMP.csv OF del
modified by nochardel
MESSAGES ABCMSG.txt

VALUES '"F1"; "F2"; "F3";"F4";"F5";"F6";"F7";"F8";"F9";"F10";"F11";"F1 2";"F13";"F14";"F15";"F16";"F17";"F18";"F19";"F20" '

UNION ALL

SELECT
'"'||TRIM(CHAR(FIELD1))||'";"'||
TRIM(CHAR(FIELD2))||'";"'||
Trim(FIELD3)||'";"'||
Trim(FIELD4)||'";"'||
Trim(FIELD5)||'";"'||
Trim(CHAR(FIELD6))||'";"'||
Trim(CHAR(FIELD7))||'";"'||
trim(FIELD8)||'";"'||
Trim(CHAR((int(FIELD9))))||','||Trim(Char((Abs(Int (((FIELD9 - int(FIELD9)) * 100))))))||'";"'||
Trim(CHAR((int(FIELD10))))||','||Trim(Char((Abs(In t(((FIELD10 - int(FIELD10)) * 100))))))||'";"'||
Trim(CHAR((int(FIELD11))))||','||Trim(Char((Abs(In t(((FIELD11 - int(FIELD11)) * 100))))))||'";"'||
Trim(CHAR(FIELD12))||'";"'||
Trim(CHAR(FIELD13))||'";"'||
Trim(FIELD14)||'";"'||
Trim(FIELD15)||'";"'||
Trim(FIELD16)||'";"'||
Trim(FIELD17)||'";"'||
Trim(CHAR(FIELD18))||'";"'||
Trim(CHAR((int(FIELD19))))||','||Trim(Char((Abs(In t(((FIELD19 - int(FIELD19)) * 100))))))||'";"'||
Trim(CHAR(FIELD20))||'"'
FROM
EMP.TABLE1

PLease help!