T-SQL Export to CSV help

Question

Post reply

T-SQL Export to CSV help

nicolas.worth

Old Hand

Points: 311
More actions
January 21, 2014 at 5:21 am

#303134

Hi All,
I was wondering if anyone can help, I am trying to write a script that will export data from SQL into a CSV file. I have got most of it working however in the CSV file it keeps adding 'whitespace' in the column data. I know you can simply right click the results pane and save results as CSV (by doing this it save the results in the format I want without whitespace). What I want to do is just have a script to run with no manual input from users. Please see below for my example code, I am welcome to improvements if I have not coded something correctly.
USE AdventureWorks
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
CREATE TABLE dbo.Users
(
UserID uniqueidentifier,
UserName varchar (20),
DateCreated datetime
)
INSERT INTO dbo.Users
VALUES (NEWID(),'Temp123', GETDATE()-1)
INSERT INTO dbo.Users
VALUES (NEWID(),'User123', GETDATE())
GO
DECLARE @filepath varchar(250)
DECLARE @query varchar(max)
DECLARE @cmd varchar(8000)
SET @filepath = 'C:\test_output_file.csv'
SET @query = 'SET NOCOUNT ON; SELECT U.[UserId] ,U.[UserName] ,U.[DateCreated] FROM dbo.Users U'
SET @cmd = 'sqlcmd -S <SERVERNAME> -E -h -1 -d AdventureWorks -Q '+ char(34)+ @query + CHAR(34)+ ' -o ' + char(34) + @filepath + CHAR(34) + ' -s' + CHAR(34) + ',' +Char(34)
--SELECT @Cmd
EXEC master..xp_cmdshell @cmd
DROP TABLE dbo.Users
When you the open the csv file created it contains whitespace.
Any help will be appreciated.
Thanks

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

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

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 1

are you replacing <SERVERNAME> with your server name right?

Can you please check the syntax try to print the @cmd and check.

nicolas.worth Old Hand Points: 311 More actions · Answer 2

Hi, Sorry yes I am replacing <SERVERNAME>. Thanks

So the cmd looks like -

sqlcmd -S MyServerName -E -h -1 -d AdventureWorks -Q "SET NOCOUNT ON; SELECT U.[UserId] ,U.[UserName] ,U.[DateCreated] FROM dbo.Users U" -o "C:\test_output_file.csv" -s","

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 3

Prassad Dabbada V R

Ten Centuries

Points: 1251

January 21, 2014 at 8:34 am

#1681963

Remove "SET NOCOUNT ON" and try to execute you command

nicolas.worth Old Hand Points: 311 More actions · Answer 4

Hi,

Thanks for your reply. The reason why I added that was to remove the row count on the output of the CSV. However I removed the section as you suggested and the output was with whitespace. See below -

CB8ECBE0-EDEB-41CB-B8B1-B15DC8F1A70E,Temp123 ,2014-01-21 10:03:32.750

66D53C8E-E543-4B55-983C-F0BE97936655,User123 ,2014-01-22 10:03:32.753

(2 rows affected)

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

There's a -W parameter to SQLCMD that removes trailing spaces from fields--would that work for you?

nicolas.worth Old Hand Points: 311 More actions · Answer 6

paul.knibbs (1/22/2014)
There's a -W parameter to SQLCMD that removes trailing spaces from fields--would that work for you?

Thanks Paul, Passing the -W parameter did the trick.

Many Thanks

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

Instead of using SQLCMD for this, I'd recommend the use of BCP instead (with the understanding that it won't give you column headers).

--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)