Post reply

easy way to extract data to csv?

  • June 21, 2012 at 4:15 am

    #262752

    Hi Guys,

    just a quick one, I'm extracting data to a CSV for an accounts reconciliation and really would like the extract part of it to be coded in the stored proc instead of using SSIS as I think SSIS will add more complexity than is required.

    I know there is bulk insert for importing data, is there a similarly nice and simple way of outputting data to csv?

    Cheers,

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • June 21, 2012 at 4:18 am

    #1503853

    BCP would be an option

    http://msdn.microsoft.com/en-us/library/ms162802.aspx

  • June 21, 2012 at 4:25 am

    #1503860

    awesome thanks that looks good.

    should be able to run that through xp_cmdshell with a trusted connection.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • June 21, 2012 at 6:15 am

    #1503882

    another option is this suite of CLR export functions I created and put on codePlex as a proof-of-concept:

    https://sqlclrexport.codeplex.com/

    here's the example code of exporting a table to a CSV report:

    EXECUTE CLR_ExportTableToCSV @TableName = 'Products',

    @FilePath = 'C:\Data',

    @FileName = 'Products_export.csv',

    @IncludeHeaders = 1

    or a query:

    EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'SELECT * FROM Products',

    @FilePath = 'C:\Data',

    @FileName = '\Products_export.csv',

    @IncludeHeaders = 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 21, 2012 at 6:18 am

    #1503885

    Thanks Lowell, one to add to the tool repository, will take a look at them.

  • June 22, 2012 at 1:21 pm

    #1504672

    BenWard (6/21/2012)

    Hi Guys,

    just a quick one, I'm extracting data to a CSV for an accounts reconciliation and really would like the extract part of it to be coded in the stored proc instead of using SSIS as I think SSIS will add more complexity than is required.

    I know there is bulk insert for importing data, is there a similarly nice and simple way of outputting data to csv?

    Cheers,

    Maybe a new BULK EXPORT command to go along with the existing BULK INSERT command. Wouldn't that be nice?

    SQL Engine - BULK EXPORT command

    Note the resolution status 😀

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 22, 2012 at 5:14 pm

    #1504706

    opc.three (6/22/2012)

    BenWard (6/21/2012)

    Hi Guys,

    just a quick one, I'm extracting data to a CSV for an accounts reconciliation and really would like the extract part of it to be coded in the stored proc instead of using SSIS as I think SSIS will add more complexity than is required.

    I know there is bulk insert for importing data, is there a similarly nice and simple way of outputting data to csv?

    Cheers,

    Maybe a new BULK EXPORT command to go along with the existing BULK INSERT command. Wouldn't that be nice?

    SQL Engine - BULK EXPORT command

    Note the resolution status 😀

    You must be doing research for an xp_CmdShell article. 🙂

    I'm amazed at Microsoft. They take the time to give us "improvements" like PIVOT but no time to actually do something that people really want. That CONNECT items has a lot of +'s on it.

    --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 7 posts - 1 through 7 (of 7 total)

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