Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Carriage return in txt file Expand / Collapse
Author
Message
Posted Friday, February 1, 2008 8:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 1, 2008 7:30 PM
Points: 6, 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





Post #450843
Posted Saturday, February 2, 2008 9:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 1, 2008 7:30 PM
Points: 6, 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
Post #450908
Posted Monday, February 4, 2008 8:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2008 5:38 AM
Points: 2, Visits: 15
Jessica,

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

Chris
Post #451423
Posted Tuesday, February 5, 2008 7:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:31 AM
Points: 210, Visits: 600
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
Post #451648
Posted Wednesday, February 6, 2008 2:17 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:53 AM
Points: 679, Visits: 456
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
Post #452449
Posted Thursday, February 7, 2008 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 1, 2008 7:30 PM
Points: 6, 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.
Post #452697
Posted Thursday, February 7, 2008 7:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:53 AM
Points: 679, Visits: 456
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
Post #452722
Posted Friday, February 8, 2008 10:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, February 9, 2014 4:17 PM
Points: 55, Visits: 78
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
Post #453559
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse