none
automate a query and export the result to a CSV file RRS feed

  • Question

  • I need to automate a query on a daily basis to a folder as a CSV file. What is the best way of doing this? Thanks
    Tuesday, July 16, 2019 7:07 PM

All replies

  • Create a Powershell script and use a scheduler.

    $AttachmentPath = "CSV File location"
    $QueryFmt= "Query"
    
    Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query $QueryFmt | Export-CSV $AttachmentPath


    william xifaras


    Tuesday, July 16, 2019 7:17 PM
  • Hi, thanks for the quick response. Beside powershell, any other suggestions? Thanks
    Tuesday, July 16, 2019 7:39 PM
  • You can create an SSIS package.

    https://solutioncenter.apexsql.com/how-to-export-sql-server-data-to-a-csv-file/

    Here is a pretty succinct list.

    https://www.sqlservercentral.com/articles/8-ways-to-export-sql-results-to-a-text-file

    I prefer Powershell and SSIS. If you prefer another option, use SQLCMD and create a schedule job.


    william xifaras

    Tuesday, July 16, 2019 9:28 PM
  • Hi my3cents,

     

    >>I need to automate a query on a daily basis to a folder as a CSV file. What is the best way of doing this? 

     

    I suggest you create a scheduled job and use xp_cmdshell to export data to a csv file:

     

    EXEC  master..xp_cmdshell 'SQLCMD -S servre\instancename -E -Q "query" -b -o D:\myoutput.csv', no_output

     

     

     

    About creating scheduled jobs, please refer to https://logicalread.com/scheduling-sql-server-jobs-with-sql-agent-mo01/#.XS7rrOgzaUk

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 17, 2019 9:44 AM
  • sorry i forgot to mention it's not just a query but a script with temp tables and queries.
    Wednesday, July 17, 2019 2:16 PM
  • Would you please try to use the following statement:EXEC  master..xp_cmdshell 'sqlcmd -S servre\instancename -i  C:\Users\xxx\Documents\SQL Server Management Studio\SQLQuery1.sql -o c:\CSV_Output.csv -s', no_output?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 18, 2019 8:52 AM
  • I got an missing argument error for "-s", so i added a comma like ".....csv_output.csv -s ,', no_output" and it seems to work but how can i get rid of all unecessary output. I just want the headers followed by the data? Thanks.

    I'm getting this

    (192 rows affected)

    last                 first            dob
    ---------------   ----------     ----------------
    smith              john            11-12-1988

    but want this only

    last                 first            dob
    smith              john            11-12-1988

    Friday, July 19, 2019 2:34 PM