remove last line

  • Hi

    I ´ve this code on a ERP framework, the only problem is that on the .TXT file it creates a blank line

    in the end, because of the LR CR

    My question is how to create de .txt file without the last blank line ( char(13)+char(10) )

    where is the all code

    ----

    /********Guarda no ficheiro*************/

    declare @nmdos varchar (50),

    @obrano numeric (18,0),

    @ref varchar (18),

    @qtt numeric (5,0)

    declare @cursor cursor

    declare @STR varchar(max)

    declare @str1 varchar(max)

    declare @str2 varchar(max)

    declare @str3 varchar(max)

    set @STR=''

    set @cursor= cursor for

    select bo.nmdos,bo.obrano,ltrim(rtrim(bi.ref)),cast (bi.qtt as decimal (10,0))

    from valsteam2.dbo.bo left join valsteam2.dbo.bi

    on bo.bostamp=bi.bostamp left join st on bi.ref=st.ref where bo.ndos=23 and bo.obrano=8545 and bi.ref<>'' and st.local=112

    open @cursor

    fetch next from @cursor into @nmdos,@obrano,@ref,@qtt

    while @@FETCH_STATUS=0

    begin

    set @STR=@str+@nmdos+'|'+convert(varchar(5),@obrano,1)+'|'+convert(varchar (20),@ref,1)+'|'+convert(varchar (8),@qtt,1) +'|4'+char(13)+char(10)

    fetch next from @cursor into @nmdos,@obrano,@ref,@qtt

    end

    close @cursor

    deallocate @cursor

    print @STR;

    declare @nomef varchar(200)

    set @nomef=convert(varchar(8),@obrano,1)+'.txt'

    execute valsteam2.dbo.spWriteStringToFile @STR,'\tvalsteam\kardex\Movimentos\',@nomef

    /************/

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Out of curiosity, does this correct your issue?

    DECLARE @STR NVARCHAR(MAX);

    SELECT @STR = STUFF((SELECT CHAR(13) + CHAR(10) + csvFile

    FROM (SELECT bo.nmdos + '|' + CONVERT(VARCHAR(5),bo.obrano,1)+ '|' +

    CONVERT(VARCHAR(20),LTRIM(RTRIM(bi.ref)),1)+'|'+CONVERT(VARCHAR(8),CAST(bi.qtt AS DECIMAL(10, 0)),1) +'|4'

    FROM valsteam2.dbo.bo

    LEFT OUTER JOIN valsteam2.dbo.bi ON bo.bostamp = bi.bostamp

    LEFT OUTER JOIN st ON bi.ref = st.ref

    WHERE bo.ndos = 23 AND bo.obrano = 8545 AND bi.ref <> ''

    AND st.local = 112

    )a(csvFile)

    FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'),1,2,'');

    PRINT @STR;

    DECLARE @nomef VARCHAR(200);

    SELECT @nomef=CONVERT(VARCHAR(8),bo.obrano,1)+'.txt'

    FROM valsteam2.dbo.bo

    LEFT OUTER JOIN valsteam2.dbo.bi ON bo.bostamp = bi.bostamp

    LEFT OUTER JOIN st ON bi.ref = st.ref

    WHERE bo.ndos = 23 AND bo.obrano = 8545 AND bi.ref <> ''

    AND st.local = 112;

    EXECUTE valsteam2.dbo.spWriteStringToFile @STR,'\tvalsteam\kardex\Movimentos\',@nomef


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • ok thank you very much

    it worked just fine

  • just another question, is it possible to save the resulting .txt in ANSI mode instead of Unicode?

    thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

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