PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 04/01/2016 11:52 AM by  jaherb
Extract File Issues
 14 Replies
Sort:
You are not authorized to post a reply.
Author Messages
jaherb
Independent
Independent
Veteran Member
(472 points)
Veteran Member
Posts:164


Send Message:

--
03/09/2016 3:32 PM

    I am not sure why I am not seeing the issue with a simple extract program... but I am at a loss at this point.   

    We have an extract file being produced as a 250 byte fixed length file.   There are 3 record types... a typical Header, Detail and Trailer record.  Each record must be delimited by  CR/LF characters.

    The logic writes the records to an intermediate sequential file and then at the end of the job it is reformatted by a cnvexp -f  filename  250  command to append the CR/LF characters at the end of each line.   This is all working fine and the issue comes when the bank complains about the file having a blank line after the trailer record.  

    In looking at the extract record, the only place this occurs at is following the trailer record.   You can see this on the screen image below.   I would expect to see the "0D0A" as the last character and nothing following that.   Whatever that character is, it is position 251 of that record, so it appears this one position is being carried over to the next line, which should not exist.   The work def has all files set at 250 bytes.

    Anyone have any ideas?

     

     

    Here is another view of the extract file using the Unix hd command

     

     

    JimY
    Private
    Private
    Veteran Member
    (1194 points)
    Veteran Member
    Posts:428


    Send Message:

    --
    03/14/2016 1:20 PM
    Our bank also complained to me about that. I had to remove the CRLF at the end of the last line. What type of program are you using to generate the file. You will probably have to run it through a script to remove the the CRLF from the last line.
    pbelsky
    Sr Analyst Project Manager
    Weyerhaeuser
    Veteran Member
    (215 points)
    Veteran Member
    Posts:75


    Send Message:

    --
    03/14/2016 1:42 PM

    i have run into this with several vendors over the years. We also had to run the file through a script to remove that last line.

    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    03/15/2016 11:19 AM
    Thanks for the reply. I am waiting on the vendor to forward me detailed extract specifications including the addition or deletion of CR/LF characters. This is where I think it may be off at. The odd part is that they say the file is good sometimes and bad at other times
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    03/15/2016 4:59 PM
    pbelskky.....

    Can you paste the script that you are using to remove the last line? I have been trying all types of sed commands, but cannot find the correct combination.

    Thank you..

    Jim
    pbelsky
    Sr Analyst Project Manager
    Weyerhaeuser
    Veteran Member
    (215 points)
    Veteran Member
    Posts:75


    Send Message:

    --
    03/15/2016 5:54 PM
    Hi Jim, rather than try to strip off the extra last line, we use grep to get the records we do want. for example, I have a vendor file with a header, detail, and trailer. This vendor's layout includes a constant for record type: a for header, b for detail, c for trailer. So we do this:

    grep a cntrb1 > cntrb1a
    grep b cntrb2 > cntrb2a
    grep c cntrb3 > cntrb3a

    cat cntrb1a cntrb2a cntrb3a > final_output_file.txt

    The constant doesn't have to be in the same position in every record. For example, if you know that all the records in your file will have a zero in them someplace, you can do a grep 0 > output.txt and it will write all the records except the extra last line to output.txt.

    If your file doesn't have a value in it that can be used as a constant, let me know and I'll try to play around with some other things.
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    03/16/2016 3:55 PM
    Thanks pbelsky.... the file has 3 record types... FH, FR & FT. when I do the grep command on the FR records, that blank record still appears.

    0001bd20: 20 0a 46 52 37 30 32 36 37 31 31 35 33 31 20 20 .FR7026711531
    0001bd30: 20 20 20 20 30 30 30 30 30 32 33 37 37 39 20 20 0000023779

    0001bd40: 20 XX XX XX XX XX XX XX XX  30 20 20 20 20 20 SXXXXXXXXX

    0001bd50: 20 20 20 20 20 20 55 53 4f 4e 47 4f 20 20 20 20 USONGO
    0001bd60: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    0001bd70: 20 20 20 20 30 30 30 30 30 30 30 30 43 30 30 30 00000000C000
    0001bd80: 30 30 30 30 30 30 30 30 32 35 30 30 30 30 30 38 0000000025000008
    0001bd90: 32 30 31 35 20 4e 4e 20 20 20 20 20 20 20 20 20 2015 NN
    0001bda0: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    *
    0001be10: 20 20 20 20 20 20 20 20 20 20 20 20 0a .
    0001be1d:


    I took a look at other vendor files we send out as well... even csv files and they all seem to have this empty record on them. Guess that is just a unix / lawson thing. I webex'd with a good friend of mine at Lawson who have been using Unix for 30+ years... he is stumped as well. Of all the files we send out, this vendor is the only one complaining about it.
    pbelsky
    Sr Analyst Project Manager
    Weyerhaeuser
    Veteran Member
    (215 points)
    Veteran Member
    Posts:75


    Send Message:

    --
    03/16/2016 4:52 PM
    The extra blank line is on the bottom of all of our output files as well, it is a Lawson thing for sure. Like you, we never noticied it until a vendor complained (oddly, also a bank).

    We are not on Unix but on Windows - I wonder if this is why grep works differently for us. We are using the grep.exe from the LUU tools for Windows.

    Have you tried using head? Try head -5 yourfile.txt > output.txt to return the first 5 lines of the file. Does the output file still have the extra blank line? if that works, you might be able to pass a record count from the program that creates the file to a head command and get rid of the extra blank that way.
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    03/16/2016 5:05 PM
    Thanks again.... just tried the head command.... did a hd on the output file ... and yep... it's still there
    JimY
    Private
    Private
    Veteran Member
    (1194 points)
    Veteran Member
    Posts:428


    Send Message:

    --
    03/17/2016 7:10 AM
    If you are on windows here is a Perl one liner that works.

    
    perl -i.bak -ape "chomp if eof" inputfile
    
    JimY
    Private
    Private
    Veteran Member
    (1194 points)
    Veteran Member
    Posts:428


    Send Message:

    --
    03/17/2016 8:31 AM
    I found this powershell script that works also.
    
    $stream = [IO.File]::Open('input_file.txt', [IO.FileMode]::Open)
    $stream.Position = $stream.Length - 2
    $bytes = 0..1 | %{ $stream.ReadByte() }
    $compareBytes = 13,10 # CR,LF
    if ("$bytes" -eq "$compareBytes") {
        $stream.SetLength($stream.Length - 2)
    }
    $stream.Close()
    $stream.Dispose()
    
    pbelsky
    Sr Analyst Project Manager
    Weyerhaeuser
    Veteran Member
    (215 points)
    Veteran Member
    Posts:75


    Send Message:

    --
    03/17/2016 11:02 AM
    I hope one of these work for you! Also, one more thought ~ what OS is your ftp server running on? It sounds like your lawson environment is on some flavor of Unix. If by chance the server you are sending the file to the vendor from is windows, you should be able to set up a job to execute a batch file with any of these suggestions, and it should work.
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    03/17/2016 11:10 AM
    We are a Unix shop here... Talking to my ftp guy is next on my agenda. Just need to wait for him to return to the office. Thanks for everyone's input...
    JimY
    Private
    Private
    Veteran Member
    (1194 points)
    Veteran Member
    Posts:428


    Send Message:

    --
    03/17/2016 12:13 PM
    The Perl one liner will work in Unix also, but the syntax is a little different. Windows requires you to make a back up copy whereas Unix doesn't. You could try the following.

    
    perl -pi -e 'chomp if eof' filename
    
    jaherb
    Independent
    Independent
    Veteran Member
    (472 points)
    Veteran Member
    Posts:164


    Send Message:

    --
    04/01/2016 11:52 AM
    After discussions with the vendor, they finally stated that they could take the file if we removed the line feed character after the trailer record.... Wish they would have told me that a long time ago... anyway... making use of the perl script filechomp, we have resolved the issue. Thanks for all of the input and thoughts on this one.....
    You are not authorized to post a reply.