SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Carriage return in txt file


Carriage return in txt file

Author
Message
jessica.stape
jessica.stape
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 38
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
jessica.stape
jessica.stape
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 38
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
cdkelly
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 16
Jessica,

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

Chris
GoofyGuy
GoofyGuy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1197 Visits: 971
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
Will1922
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1965 Visits: 481
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
jessica.stape
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 38
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
Will1922
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1965 Visits: 481
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
ButchH
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 88
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


Butch
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search