Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2007-11-27 : 05:24:14
|
| Is there an example anywhere of how to output selected fields in a sql table to a text file with fixed length fields. ie pad data out to required length. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-27 : 05:52:29
|
| Use either a pump task or bcp with a format file. Not sure on examples of these, but google it and you'll find something. |
 |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-28 : 11:03:48
|
| bcp, dts or ssis are the best tools, but for a purely academic exercise here is a tsql solution...--create schema file in directory stating file and fixed width formatexec master.dbo.xp_cmdshell 'echo [output.txt]>>c:\Temp\Schema.ini'exec master.dbo.xp_cmdshell 'echo Format=FixedLength>>c:\Temp\Schema.ini'exec master.dbo.xp_cmdshell 'echo>>c:\Temp\Schema.ini'--create base file with headerexec master.dbo.xp_cmdshell 'echo Col1=column20char text width 20>>c:\Temp\Schema.ini'exec master.dbo.xp_cmdshell 'echo Col2=column15char text width 15>>c:\Temp\Schema.ini'exec master.dbo.xp_cmdshell 'echo column20char column15char >>c:\Temp\output.txt'--populate text fileINSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=YES;FMT=FixedLength;Database=c:\Temp', output#txt)select '12345' as column20char, 'abcde' as column15charunion all select '123451234512345' as column20char, 'abcdeabcde' as column15char |
|
|
|
|
|
|
Output sql table to text file