BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rcflyboyjr
Calcite | Level 5

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 ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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.

Rcflyboyjr
Calcite | Level 5

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

 

Tom
Super User Tom
Super User

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;
Rcflyboyjr
Calcite | Level 5

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.

Jowa89
Calcite | Level 5

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 ';'

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2556 views
  • 1 like
  • 3 in conversation