Post reply

Export SQL Data to CSV using Batch Scripting

  • January 15, 2015 at 3:40 pm

    #313753

    Hi,

    I have thought about doing this for a long time, let me know if it feasible or if there is a better approach.

    Execute a pre written SQL on double clicking a batch file which will then export the results to a csv. Anyone has a pre written script I could use ?

    Thanks,

    V

  • January 15, 2015 at 8:53 pm

    #1770627

    venkyzrocks (1/15/2015)

    Hi,

    I have thought about doing this for a long time, let me know if it feasible or if there is a better approach.

    Execute a pre written SQL on double clicking a batch file which will then export the results to a csv. Anyone has a pre written script I could use ?

    Thanks,

    V

    Have it execute BCP in the "query out" mode and a comma for the delimiter using a trusted connection.

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

  • January 16, 2015 at 1:25 pm

    #1770889

    Hi,

    Here's the issue I'm having.

    1. the bcp output doesn't include headers

    2. the sql is too long to be included in one line. How could I call a .sql file from a bcp to execute.

    3. I want to pass .bat to the end user and have them execute it themselves.

    4. How can I have menu based selections for 1. execute this . 2 execute this ...

    Does it make more sense to have a .bat call an SSIS package and output the file to a common network path ?

  • January 16, 2015 at 1:34 pm

    #1770891

    venkyzrocks (1/16/2015)

    Hi,

    Here's the issue I'm having.

    1. the bcp output doesn't include headers

    So, make some using a separate SELECT UNION ALL

    2. the sql is too long to be included in one line. How could I call a .sql file from a bcp to execute.

    Write a stored procedure and call it from BCP

    3. I want to pass .bat to the end user and have them execute it themselves.

    There's nothing preventing that unless the user doesn't have the necessary privs.

    4. How can I have menu based selections for 1. execute this . 2 execute this ...

    Popup message using VB script or similar.

    Does it make more sense to have a .bat call an SSIS package and output the file to a common network path ?

    No. It makes much more sense to have schedule jobs do this and email the users with the locations as embedded links.

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

  • January 16, 2015 at 1:41 pm

    #1770895

    Thanks Jeff. i'll work on it and let you know. Thanks again for all your help.

  • January 21, 2015 at 10:08 am

    #1771913

    You could do this in powershell and use Export-Csv.

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

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