I am trying to set a variable equal to a string and then output that into a file that must be formatted exactly line by line.
I have tried this:
and the result is this: '\x0aline1\x0aline2\x0aline3'
After some research I also tried this:
and the result is the same: '\x0aline1 \x0aline2 \x0aline3'
After Calvin's comment I tried this:
and the result is: '\x0a\x0dline1\x0a\x0dline2\x0a\x0dline3'
How do I make a real new line appear in a txt file?
Also, how would I remove the single quotes that surround the results in the text file?
Martin and Volker's answer both work. This is the full code for Martin's answer:
I don't know whether there is a better way by particular options of UNLOAD SELECT (I tried ESCAPES ON/OFF, QUOTES ON/OFF and the like).
EDIT: According to John's comments on Martin's answer, the following seems to be the correct platfrom-independant solution.
QUOTES OFF will remove the disturbing quotes around the output.
But the easiest thing may be to give UNLOAD SELECT a usually result set, i.e. the different lines treated as different rows. That can easily be done with the help of the sa_split_list function (here used with the select from procedure-syntax):
returns a resultset as following
Therefore just selecting the row_value column will give you the needed text file:
Use \x0d\x0a for the carriage return inside your string (Calvin just mixed the order) and use
By the way a lot of software is already recognizing the \x0a as a line feed e.g. Microsoft Wordpad...
answered 01 Oct '10, 07:18
Use whatever combination of '\x0D' and/or '\x0A' works with whatever client software and operating system you are using to display the text.
Personally, I work in the world of Windoze, and I find that \x0D\x0A is pretty reliable... works with both Wordpad and Notepad ( what, there are other text editors? :)
Note that life can be very different in the Unix world. Utility programs exist to convert text files when moving between environments... and religious wars exist, even within Sybase, on which newline is The One True NewLine... a Great Schism formed when folks stopped using mechanical teletypewriters, and the spittle has flown ever since.
Even Notepad differs from Wordpad in the way they handle non-\x0D\x0A line breaks... sometimes you can view text in one program, but it appears all on one line in the other, depending on what combination of one, two or three '\x0A' and '\x0D' character(s) is/are being used.
FWIW I absolutely detest all forms of logical escape sequences like '\n' since you NEVER REALLY KNOW what you are getting, I stick to the physical escape sequences like '\x0A' and '\x0D' ...but that's just me.
For more than you ever wanted to know on the subject, start here and follow the links: http://en.wikipedia.org/wiki/Control_character
answered 02 Oct '10, 12:41