Carriage return in txt file

Question

Carriage return in txt file

jessica.stape

Mr or Mrs. 500

Points: 544
More actions
February 1, 2008 at 8:29 pm

#93888

I am attempting to send data to a txt file using a T-SQL in a stored procedure. The file needs to contain six strings separated by carriage returns NOT newlines. I have done the following, but when I open up the file I do not see any indication that the carriage returns are actually there. I just see one long string of data.
I have been using the statement below:
select @cmdtxt = "echo "+ @string1_to_write + CHAR(13) + @string2_to_write + " >> c:\temp\myfile.txt"
Will carriage returns in the text file be visible? Am I going about this the wrong way?
Thanks,
Jessica

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

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

jessica.stape Mr or Mrs. 500 Points: 544 More actions · Answer 1

Figured this out myself by opening up the file in MS Word and selecting "Reveal Formatting". The code above does add a carriage return effectively.

-Jessica

cdkelly SSC Journeyman Points: 88 More actions · Answer 2

Jessica,

Just as a matter of course, you should have a (text) editor that (at least) displays an opened file in hex.

Chris

GoofyGuy SSCertifiable Points: 6109 More actions · Answer 3

Jessica,

I'll second the suggestion about using a text editor which reveals hex codes. There are many available - I like using the Boxer text editor (www.boxersoftware.com) because it includes this feature and many others I've found useful as an application developer.

Kind regards.

Craig

Will1922 SSCertifiable Points: 7189 More actions · Answer 4

I do stuff like what you are talking about all the time. I probably have a better way to write to a txt file other than a cmd_shell echo statement. Can you post a bit more of the code and I can check it out?

Live to Throw
Throw to Live
Will Summers

jessica.stape Mr or Mrs. 500 Points: 544 More actions · Answer 5

Hi Will,

I am creating a stored procedure that will query a database for some information and then generate a txt file with that information. This is for a medical application, so I have to make sure to follow HL7 guidelines. One of the guidelines specifies that each line must be separated by carriage return, not newline.

In the end, I need six lines of data separated by carriage returns. So I am saving each line to a variable and then using the echo command at the end of the procedure like below:

@string1_to_write VARCHAR(255),

@string2_to_write VARCHAR(255),

...

@string6_to_write VARCHAR(255)

...

select @cmdtxt = "echo "+ @string1_to_write + CHAR(13) + @string2_to_write + CHAR(13) + ...+@string6_to_write " >> c:\temp\myfile.txt"

exec master..xp_cmdshell @cmdtxt

I thought this was working right, but actually the carriage returns are not being generated. I am not an expert at by any means at T-SQL, but I used this method because it was simple. If you know of a better way, please let me know.

I found one site that said you can't use CHAR(13) with select statements, but I have seen a lot of code posted to the contrary.

Will1922 SSCertifiable Points: 7189 More actions · Answer 6

I got a much nicer way to do this.

1. Setup the following table:

CREATE TABLE TXTTOFILE (

LINE varchar (2000) NULL ,

IDCOL int IDENTITY (1, 1) NOT NULL

)

2. Then put a clustered index on IDCOL.

3. Now insert each line that you want to go into the file into this table without the carriage returns

INSERT INTO TXTTOFILE SELECT @string1_to_write

INSERT INTO TXTTOFILE SELECT @string2_to_write

. . .

4. Now with a single cmdshell you can output the results

exec xp_cmdshell 'BCP TXTTOFILE OUT D:\TXTFILE.TXT /Uuser /Ppassword /c'

Now if you did it right, you should have a nice file with carriage returns and all! The key to this is the clustered index on IDCOL which keeps the file in the order which it was inserted. The BCP will just do the equilivant to a SELECT * FROM on the table, which will be in the order you inserted it.

If you are still having problems doing it this way, post your code and I can go through it.

Live to Throw
Throw to Live
Will Summers

ButchH Ten Centuries Points: 1073 More actions · Answer 7

The way I work this is:

Declare

@CrLf char (2)

SET @CrLf = char(13) + char(10)

select @cmdtxt = "echo "+ @string1_to_write + @CrLf + @string2_to_write + @CrLf" >> c:\temp\myfile.txt"

I had a rough time with this at first because I tried to reverse the CRLF sequence which does NOT work. When you open your file in a "pure" text editor like NotePad, you'll have what you expect to find.

Butch