How to populate csv file from SQL Server stored procedure?

Question

How to populate a .csv file from a SQL Server stored procedure?

We don't have Office on the Server. The .CSV file has to be populated from a stored procedure result set.

How to export to a .CSV without using SSIS package?

End result, I will have to generate email alert by attaching this CSV file as report.

I will have to use bulk copy program utility (BCP), I am looking on samples for BCP to generate csv file

Assuming you are wanting something automated. If you aren't adverse to PowerShell it would be pretty easy to do. — SteveB, Jan 16 '19 at 21:41
not understaing the quation, you want to create a csv file and save it to the disk using storeprecedure or you want to only convert the table data to csv string then using C# or what ever to create the file — Alen.Toma, Jan 16 '19 at 21:45
ok maybe this could help https://stackoverflow.com/questions/14275493/how-to-create-text-file-using-sql-script-with-text — Alen.Toma, Jan 16 '19 at 21:47
I will have to use BCP, I am looking on samples for BCP to generate csv file — goofyui, Jan 16 '19 at 21:48
Take a peek at https://dba.stackexchange.com/questions/23566/writing-select-result-to-a-csv-file — John Cappelletti, Jan 16 '19 at 21:51

score 1 · Answer 1 · answered Jan 16 '19 at 23:06

There are two methods to achieve that:

(1) Using OPENROWSET

Try implementing a similar logic:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
EXEC Sp_TEST

(2) Using bcp

From the third link in References section:

The queryout method allows you to BCP from the result of a stored procedure, which opens up a lot of possibilities and offers a lot of control over the file format. For anything other than a simple table extract I would tend to use this method rather than a view. I would also format each line within the stored procedure. This means that the formatting can be tested independently from the file creation

declare @sql varchar(8000)
select @sql = 'bcp "exec sp_Test"
   queryout c:\bcp\sysobjects.csv -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql

References

score 0 · Answer 2 · answered Jan 16 '19 at 22:37

Look at the code below. A stored procedure is created and then used as the source in the BCP command. Then a text file is generated. Delimiters are discussed in the code sample in the link.

Code sample taken from Creating CSV Files Using BCP and Stored Procedures

use tempdb
go
create proc s_bcpMasterSysobjects
as
   select   '"' + name + '"'
            + ',' + '"' + convert(varchar(8), crdate, 112) + '"'
            + ',' + '"' + convert(varchar(8), crdate, 108) + '"'
   from master..sysobjects
   order by crdate desc
go
declare @sql varchar(8000)
select @sql = 'bcp "exec tempdb..s_bcpMasterSysobjects"
       queryout c:\bcp\sysobjects.txt -c -t, -T -S'
+ @@servername
exec master..xp_cmdshell @sql

score 0 · Answer 3 · edited Jan 17 '19 at 08:37

0

I would use BCP

see example

declare @sql varchar(2000)
select @sql = 'bcp "SQL QUERY HERE" queryout '+@fileLocation+@fileName+'.csv -c -t, -T -S' + @@servername
    exec master..xp_cmdshell @sql, NO_OUTPUT

if you need to see any errors or whats it's doing, just remove the ", NO_OUTPUT"

edited Jan 17 '19 at 08:37

Kaushik Makwana

1,329
2
14
24

answered Jan 17 '19 at 08:06

Jucat

1

Thank you. I am using this syntax to generate the direct output .. exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on; select top 10 * from testtable;" -s"," -W -o"c:\MyFolder\output.csv"' Now, when i tried to pass the Stored Procedure with paramater on it such as ( exec sp_GetReport 'Inboundfile.txt' ) instead of Select query .. i am getting the syntax error – goofyui Jan 17 '19 at 16:36

score -1 · Answer 4 · answered Jan 16 '19 at 21:45

-1

Create a little script, the csv is a simple format : csv = comma separate value. Separate your values width comma.

answered Jan 16 '19 at 21:45

Jérémy Gachon

252
2
6
27

This really doesn't address the question. OP is asking how to output to a csv from a stored procedure, not what a csv is. – Error_2646 Jan 16 '19 at 22:04
@Error_2646 I think this actually addresses the OPs question. OP doesn't seem to know what CSV is and is trying to make a correlation with Office. – Cetin Basoz Jan 16 '19 at 22:43
@JohnCappelletti, thinking of CSV, can you tell me why would one need Microsoft.ACE.OLEDB on a server at all? – Cetin Basoz Jan 17 '19 at 01:08
@JohnCappelletti, I am not sure you know what you are talking about. Why would I need Microsoft.ACE.OLEDB on a server just to utilize CLR? Probably instead of saying "for example" you should show some solid reason. – Cetin Basoz Jan 17 '19 at 01:56
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186824/discussion-between-cetin-basoz-and-john-cappelletti). – Cetin Basoz Jan 17 '19 at 02:04
@JohnCappelletti, "peg"? Hope you are not saying something bad to me. It was you all along who claims one needs ACE.OLEDB for CSV. – Cetin Basoz Jan 17 '19 at 02:11
@CetinBasoz That doesn't really improve the answer. "Create a little script" is not going to be helpful for someone who doesn't even know what a .csv is. – Error_2646 Jan 17 '19 at 15:12
@Error_2646, that is the point, OP doesn't seem to know what CSV is and seeking harder solutions. – Cetin Basoz Jan 17 '19 at 19:07
@Cetinbasoz Maybe, based on his profile it seems very farfetched that he wouldn't know and wouldn't be able to figure out what a .csv is, but it's possible. – Error_2646 Jan 18 '19 at 00:21

How to populate csv file from SQL Server stored procedure?

4 Answers4

(1) Using OPENROWSET

(2) Using bcp

References

Linked