none
Programmatically Write Results to CSV RRS feed

  • Question

  • Good morning, all!

    Does anyone know of a way to write the results of a SELECT straight to a CSV file? I know there are ways to do it via options and r-clicking, but I'd like to do it as part of the query.

    Is this possible?

    Thanx!

    Friday, January 3, 2020 4:15 PM

Answers

  • TSQL does not write to the file system.  

    You need to use a "tool" to write to a file.  You can use SSIS, SSMS (right click and export), or bcp.exe.

    Rather than do this at all, I suggest you use something like Excel and use data access to write queries directly against your database and get it directly into Excel, where you likely want it.

    Friday, January 3, 2020 6:28 PM
    Moderator

All replies

  • You can use bcp command to export your query to a text file. Search to find sample code.
    Friday, January 3, 2020 5:06 PM
    Moderator
  • You can use bcp command to export your query to a text file. Search to find sample code.

    Thanx for your reply, Jingyang, but, BCP requires a download and that is not an option for me. I need something native to SSMS or T-SQL.

    Others may have to execute my queries, if I am not available, and may not have the utility or the permissions to download it in the first place.

    I thought I had seen a reference to an internal SQL function to write a CSV, but I can't find it now. I had come across the BCP solution, but something native to SSMS would be what I need.

    Thanx again for your reply!

    Friday, January 3, 2020 5:18 PM
  • If Powershell is an option, then you could use that. Another option would be to put your SELECT statement into a SQL Agent job, and have the job output to a file. HTH
    Friday, January 3, 2020 5:43 PM
  • TSQL does not write to the file system.  

    You need to use a "tool" to write to a file.  You can use SSIS, SSMS (right click and export), or bcp.exe.

    Rather than do this at all, I suggest you use something like Excel and use data access to write queries directly against your database and get it directly into Excel, where you likely want it.

    Friday, January 3, 2020 6:28 PM
    Moderator
  • If Powershell is an option, then you could use that. Another option would be to put your SELECT statement into a SQL Agent job, and have the job output to a file. HTH
    I'll have to look into the PowerShell path, though that may have the same restrictions as BCP. SQL Agent isn't a solution, worst luck, because I don't have permissions to create one and the IT people don't see this as a corporate need; their view is it's just something fluffy that I want.
    Friday, January 3, 2020 6:48 PM
  • TSQL does not write to the file system.  

    You need to use a "tool" to write to a file.  You can use SSIS, SSMS (right click and export), or bcp.exe.

    Rather than do this at all, I suggest you use something like Excel and use data access to write queries directly against your database and get it directly into Excel, where you likely want it.

    True, it is going to Excel.

    This is something I hadn't thought of, kind of a backhanded approach. I'll explore this idea, as the SQL -> CSV construct isn't turning out so well.

    Thanx, Tom!

    Friday, January 3, 2020 7:03 PM
  • Another option would be to use SQLCMD Mode - the file will be output as a tab-delimited file and can then be imported into Excel.  From within Excel you can open the file and it will recognize that it is a tab-delimited file and prompt you...

    :out C:\Temp\somfilename.txt

    SET NOCOUNT ON;

    SELECT ... FROM ... WHERE ...; GO

    To change to SQLCMD Mode - select Query from the menu and then select SQLCMD Mode.


    Jeff Williams

    Friday, January 3, 2020 8:11 PM
  • Another option would be to use SQLCMD Mode - the file will be output as a tab-delimited file and can then be imported into Excel.  From within Excel you can open the file and it will recognize that it is a tab-delimited file and prompt you...

    :out C:\Temp\somfilename.txt

    SET NOCOUNT ON;

    SELECT ... FROM ... WHERE ...; GO

    To change to SQLCMD Mode - select Query from the menu and then select SQLCMD Mode.


    Jeff Williams

    Interesting.

    The end goal is to fully automate an updating and reporting process and take fallible human hands out of the equation. Can this be done programmatically? Or, only manually?

    Friday, January 3, 2020 8:46 PM
  • The end goal is to fully automate an updating and reporting process and take fallible human hands out of the equation. Can this be done programmatically? Or, only manually?

    Yes, this can be done programmatically. But it just can't be done in T-SQL. Exporting data to file is being done every day all over this planet. Most commonly used are probably SSIS packages, but there are PowerShell scripts, custom-written .NET programs, VBA macros in Excel, you name it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 4, 2020 10:33 AM
  • If your end goal is to get it into Excel I suggest you abandon the export idea and use direct access from Excel.  Then the user can simply click "Data -> Refresh" to get new data.

    https://support.office.com/en-us/article/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e

    Monday, January 6, 2020 12:15 PM
    Moderator
  • If your end goal is to get it into Excel I suggest you abandon the export idea and use direct access from Excel.  Then the user can simply click "Data -> Refresh" to get new data.

    https://support.office.com/en-us/article/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e

    I'm not sure that is something that would solve my specific problem. The generated CSV is placed onto a server to be accessed by another automated process. I suppose, if that process could be so coded to do such a refresh across servers, that may work. I'll see what that team says.

    Thanx for your response, Tom!

    Monday, January 6, 2020 2:48 PM
  • The end goal is to fully automate an updating and reporting process and take fallible human hands out of the equation. Can this be done programmatically? Or, only manually?

    Yes, this can be done programmatically. But it just can't be done in T-SQL. Exporting data to file is being done every day all over this planet. Most commonly used are probably SSIS packages, but there are PowerShell scripts, custom-written .NET programs, VBA macros in Excel, you name it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Yes, I know it can be done in the other packages - SSIS, for example - but, my specific need is to do it in SQL. If that's not possible, then I'll have to try another path. So far, Tom's idea of writing the query in Excel seems best.
    Monday, January 6, 2020 2:54 PM