• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 886
  • Last Modified:

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
0
jsuanque
Asked:
jsuanque
  • 3
  • 2
2 Solutions
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now