Carriage return in txt file

  • 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

  • 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

  • Jessica,

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

    Chris

  • 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

  • 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

  • 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.

  • 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

  • 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

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

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