How to format output text file from T-SQL using sqlcmd

Hello Experts,
Is it possible to format the output text file from SQL Server? If yes, any suggestion or avenues to look into would do.
Anyway here is what i've got when i execute it on a command line
(C:\sqlcmd -S MyServer\MyInstance -i C:\MySQL.txt -o C:\MyOutput.txt

Changed database context to 'MyDB'.
CustOnlinelID                                       CustRecID    CustIdentifier      
-------------------------------------------------- ----------- -------------
6000000                                                      1 Q007012402004
6000001                                                      3 V007012404001
6000002                                                      4 J007012404002
6000003                                                      5 J006000405299
6000004                                                      6 M007012404004
6000005                                                      7 B007012404005
6000006                                                      8 D006009602006
6000007                                                      9 E004001088001

My desirec output should look like this (comma delimited)...

Changed database context to 'MyDB'.
CustOnlinelID                                       CustRecID    CustIdentifier      
-------------------------------------------------- ----------- -------------
6000000,1,007012402004
6000001,3,V007012404001
6000002,4,J007012404002
6000003,5,J006000405299
6000004,6,M007012404004
6000005,7,B007012404005
6000006,8,D006009602006
6000007,9,E004001088001
6000008,10,K007004404445
6000009,11,U007012404008
jsuanqueAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Hi.

You can use the -s option to specify the column separator for export, i.e., -s ","
http://msdn.microsoft.com/en-us/library/ms162773.aspx

C:\sqlcmd -S MyServer\MyInstance -i C:\MySQL.txt -o C:\MyOutput.txt -s ","
0
 
jsuanqueAuthor Commented:
Unfortunately just find a workaround...
Instead of doing this...
SELECT colA, ColB, ColC FROM MyTable on MySQL.txt

I decided to modify it...

SELECT ColA+','+ColB+','+ColC FROM MyTable on MySQL.txt
This did gave me the result i wanted
0
 
jsuanqueAuthor Commented:
Hello mwvisa1,
That almost did it but unfortunately since this output file will be processed by another process extra spaces would create problems. Here's the output of your suggestion...
6000000                                           ,          1,Q007012402004
6000001                                           ,          3,V007012404001
6000002                                           ,          4,J007012404002
6000003                                           ,          5,J006000405299


While modifying the T-SQL itself.. SELECT ColA+','+ColB+','+ColC as DATA FROM MyTable on MySQL.txt ...to this, resulted on what i wanted...

DATA                              
-----------------------------------
6000000,1,Q007012402004            
6000001,3,V007012404001            
6000002,4,J007012404002            
6000003,5,J006000405299            
6000004,6,M007012404004            
6000005,7,B007012404005    
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Kevin CrossChief Technology OfficerCommented:
Try to see if this helps, but glad you found a workaround.
C:\sqlcmd -S MyServer\MyInstance -i C:\MySQL.txt -o C:\MyOutput.txt -s "," -h -1
0
 
dj_alikCommented:
you can use OPENROWSET

LIKE this also to CSV FILE:

 insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
0
 
jsuanqueAuthor Commented:
Hello MWVisa,
Your solution solved my other problem. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.