Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: .CSV Output With Headers Using SQL

    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.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Maybe this is what you're looking for:
    http://www.dbforums.com/db2/1665936-...ect-query.html

  3. #3
    Join Date
    Jun 2011
    Posts
    4

    .CSV Output with headers

    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??

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    VALUES '"accountno","accountname","email"'
    UNION ALL
    ...

  5. #5
    Join Date
    Jun 2011
    Posts
    4

    .CSV Output with headers

    Quote Originally Posted by tonkuma View Post
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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"

  7. #7
    Join Date
    Jun 2011
    Posts
    4

    .CSV Output with headers

    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
    ;

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?
    Last edited by tonkuma; 06-06-11 at 01:29.

  9. #9
    Join Date
    Feb 2009
    Posts
    7

    Help required

    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!

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please start a new thread.

    Does EMP.TABLE1 have any data? Are any of the columns null?

    Andy

  11. #11
    Join Date
    Feb 2009
    Posts
    7
    Thanks for your reply.
    Made a new thread.


    Regards,
    Ashish

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •