remove last line

Question

Post reply

remove last line

carlos cachulo-318532

Say Hey Kid

Points: 697
More actions
August 13, 2012 at 5:08 pm

#260690

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
/************/
August 14, 2012 at 1:42 am

This was removed by the editor as SPAM
August 14, 2012 at 1:47 am

This was removed by the editor as SPAM

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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/

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 2

carlos cachulo-318532

Say Hey Kid

Points: 697

August 14, 2012 at 5:02 am

#1525427

ok thank you very much

it worked just fine

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 3

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

thanks in advance