Download resul set in .csv

Question

Post reply

Download resul set in .csv

ISuleymanovH

SSChasing Mays

Points: 639
More actions
November 25, 2013 at 4:45 am

#395889

Hello.
Could you help me? please.
I have a task, download resul set in .csv
declare @cmd varchar(8000)
set @cmd= 'bcp "[Devel].TestDB.dbo.ia_ReportUser" out "C:\TEMP1\2255212.csv" -c -t"," -T -S '+@@servername
exec master..xp_cmdshell @cmd ,no_output
But when i start this scrip, i have a message
A valid table name is required for in, out, or format options.
Please help me sove this task.
Thank you very much.

Viewing 9 posts - 1 through 8 (of 8 total)

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

ISuleymanovH SSChasing Mays Points: 639 More actions · Answer 1

This is stored procedure

[Devel].TestDB.dbo.ia_ReportUser

ISuleymanovH SSChasing Mays Points: 639 More actions · Answer 2

I do this script and it run succeseful

declare @cmd varchar(8000)

set @cmd= 'bcp "TestDB.dbo.ia_ReportUser" queryout "C:\TEMP1\2255212.csv" -c -t"," -T -S '+@@servername

exec master..xp_cmdshell @cmd

When I run TestDB.dbo.ia_ReportUser in QA

I have a result set(4 column)

a b c d

Then i do it

declare @cmd varchar(8000)

set @cmd= 'bcp "TestDB.dbo.ia_ReportUser" queryout "C:\TEMP1\2255212.csv" -c -t"," -T -S '+@@servername

exec master..xp_cmdshell @cmd

The file created, but when i open this file in exel I see that the data in one column, how can I share it

Sorry for my English((

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Are the double quotes around the field terminator necessary?

t"," ---. t,

How are you checking the end result? I hope not with Excel...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ISuleymanovH SSChasing Mays Points: 639 More actions · Answer 4

I don't know, why i did double quotes, I find this example in internet, but this script work well and it download resul set in csv file

declare @cmd varchar(8000)

set @cmd= 'bcp "exec TestDB.dbo.ReportYaerd" queryout "C:\TEMP1\2255212.csv" -c -t";" -T -S '+@@servername

exec master..xp_cmdshell @cmd

But title of resul set misses in csv file/

For example

exec TestDB.dbo.ReportYaerd

result set

Id Date UserID

1 20010101 125

When I download this in exel wity top scritp/ the result next

1 20010101 125

But I want to download capture

Id Date UserID

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

ISuleymanovH (11/25/2013)
I don't know, why i did double quotes, I find this example in internet, but this script work well and it download resul set in csv file
declare @cmd varchar(8000)
set @cmd= 'bcp "exec TestDB.dbo.ReportYaerd" queryout "C:\TEMP1\2255212.csv" -c -t";" -T -S '+@@servername
exec master..xp_cmdshell @cmd

Right, you changed the delimiter from , to ;

The regional settings of Excel determine which delimiter .csv uses. That's why it is a bad idea to use Excel to check the output of a .csv extract.

(there are many other reasons)

ISuleymanovH (11/25/2013)
But title of resul set misses in csv file/
For example
exec TestDB.dbo.ReportYaerd
result set
Id Date UserID
1 20010101 125
When I download this in exel wity top scritp/ the result next
1 20010101 125
But I want to download capture
Id Date UserID

bcp doesn't have headers by default. You can either do a UNION ALL query to append the header as the first row to your result set, or you can do the export with PowerShell:

Exporting from SQL Server to CSV file using Powershell

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ISuleymanovH SSChasing Mays Points: 639 More actions · Answer 6

WOW!!!

Many Many thanks for your answers

For a quick fix I use UNION ALL

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

ISuleymanovH (11/26/2013)
WOW!!!
Many Many thanks for your answers

No problem, glad to help.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqldriver SSChampion Points: 10440 More actions · Answer 8

Can you use sqlcmd? This article was recently in my RSS feed.

SQL SERVER – Export Data AS CSV from Database Using SQLCMD[/url]