topic Text File Output Node generating extra line at end of file in SAS Data Management

Text File Output Node generating extra line at end of file

Rcflyboyjr — Fri, 31 Aug 2018 21:00:52 GMT

Using Dataflux - Data Management Studio 2.7

I have a Data job that has the following flow .

/ -- Validation Node \

Fixed Len Input file > data validation (removes bad rows) > Branch > > No Key Data Join > Text File Output

\ -- Expression /

All of the files being exported end up with an extra line at the end of the file.

I am assuming that's because the writer is writing a line and adds the Cr/LF to prepare for the next line then

when eof is reached it just closes the file.

My database load utility errors when that final blank line is reached. is there a way to disable or remove the last line during the process without having to reconnect with a file reader and loop through the entire file again to remove the last line ?

Re: Text File Output Node generating extra line at end of file

Tom — Sat, 01 Sep 2018 23:11:10 GMT

Can you clarify what you mean?
A normal text file WILL have the end of line characters as the last bytes in the file.

Is your process expecting that to be missing? If so WHY? What software are you using to read the output file?

I think the confusion might be caused by some processes (or their programmers) thinking of the END OF LINE characters as if they were RECORD SEPARATOR characters instead. But that is just not how files are normally made by most software.

Re: Text File Output Node generating extra line at end of file

Rcflyboyjr — Tue, 18 Sep 2018 16:06:50 GMT

The Fastload utility for Teradata errors when it reaches the last line with no data.
It expects the last data line to NOT have a CR/LF indicating it is the last line to be read.

Since my Dataflux environment is disconnected from the Database server environment
Dataflux is my standardization / ETL tool . I upload the data files to another server
and load into the database. I would just like to avoid passing it through another program
to strip the last record..

Re: Text File Output Node generating extra line at end of file

Tom — Tue, 18 Sep 2018 16:34:33 GMT

Your experience with FASTLOAD seems to be the reverse of this users experience.

https://community.teradata.com/t5/Database/Question-regarding-Fastload/td-p/20830

Even so perhaps you should adopt your process to use TPT instead of FASTLOAD. I don't think Teradata is continuing development on FASTLOAD.

It is possible to generate a file without the final end of line (I am sure I have answered that question on this list), but I am not sure it is possible in DI without adding custom code.

So code like this should convert the file. This is using Unix end of line, if you are generating Windows/DOS formatted files then write CR+LF ('0D0A'x) between the lines instead.

data _null_;
   infile 'file with final EOL'  end=eof;
   file 'file without final EOL' recfm=n;
   input;
   put _infile_ @ ;
   if not eof then put '0A'x @;
run;

Re: Text File Output Node generating extra line at end of file

Rcflyboyjr — Tue, 18 Sep 2018 16:45:33 GMT

That is correct for fast loads using input of TEXT .

I should have mentioned our records are Pipe delimited.

So that may be why our fastloads fail in that instance.

I would love to use TPT but alas our hands are tied in the
environment we are in.... I will have to look into how to
execute the samples you provided. I tried putting in a SAS Code node but
get an error No SAS Workspace server available.

Thanks for the insights I will continue to hammer away until
our training is finally approved.

Re: Text File Output Node generating extra line at end of file

Jowa89 — Tue, 19 Nov 2019 14:46:05 GMT

Thank you for your collaboration! Helped me a lot.
Just to complement: It is possible to adapt the above code in files with extension .TXT delimited by ';'