Post reply

Export CSV and Column Headers

  • March 13, 2008 at 10:33 am

    #188032

    Hello all, I'm new to the forum and looking to maybe get a direction with a problem I'm having. I'm looking to create a SQL statement that selects certain columns from a table and outputs them to a CSV text file. The CSV text file isn't the problem. Our client wants headers at the first line of the text file. I've pasted a code snippet to see what's going on. The statement runs fine however it drops the first part of the SELECT statement to the end of the result set. If I put a space in after the first single quote it places these headers at the top of the result set but with a space, "client no like." 🙁

    SELECT 'client_num,client_name,matter_num,matter_name'

    FROM dbo.master_trans

    UNION

    SELECT client_num+','+client_name+','+matter_num+','+matter_name

    FROM dbo.master_trans

    WHERE export_id IS NULL

    AND job_type_id IN (1,4)

    AND end_date < dbo.DATE_FORMAT(GETDATE(), 'YYYY-MM-DD 00:00:00')

    this statement places the first SELECT at the bottom of the result set, arrrghhh!

    SELECT ' client_num,client_name,matter_num,matter_name'

    FROM dbo.master_trans

    UNION

    SELECT client_num+','+client_name+','+matter_num+','+matter_name

    FROM dbo.master_trans

    WHERE export_id IS NULL

    AND job_type_id IN (1,4)

    AND end_date < dbo.DATE_FORMAT(GETDATE(), 'YYYY-MM-DD 00:00:00')

    this statement places the first SELECT at the top but with the space. Any ideas on how I correct this?

    Thanks all,

    Z

  • March 13, 2008 at 10:42 am

    #789200

    Try this...

    SELECT d.Csv

    FROM (

    SELECT 'client_num,client_name,matter_num,matter_name' AS Csv,1 AS SortOrder

    FROM dbo.master_trans

    UNION

    SELECT client_num+','+client_name+','+matter_num+','+matter_name AS Csv,2 AS SortOrder

    FROM dbo.master_trans

    WHERE export_id IS NULL

    AND job_type_id IN (1,4)

    AND end_date < dbo.DATE_FORMAT(GETDATE(), 'YYYY-MM-DD 00:00:00')

    )d

    ORDER BY d.SortOrder

    Change the smiley face to a right parenthesis...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 13, 2008 at 10:50 am

    #789207

    Thanks Jeff, appreciate the help.

    Sincerely,

    Z

  • March 13, 2008 at 1:25 pm

    #789279

    create a template csv file in a location, use that template for exporting purpose and then archive or change the name of the csv file as per need.

  • March 13, 2008 at 7:49 pm

    #789397

    cziebert (3/13/2008)

    Thanks Jeff, appreciate the help.

    Sincerely,

    Z

    And I appreciate the feedback. Thanks, Z.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply