Results 1 to 8 of 8
  1. #1
    Join Date
    May 2014
    Posts
    3

    Unanswered: Remove Line Feed Characters from a Load file

    We have a file extract from a mainframe which we want to load into DB2 from a Unix scrip.
    The row delimiter is CRLF (Carriage Return)(Line Feed) but within some rows there is a LF (Line Feed) non printable character but the Load script sees this as a row delimiter and incorrectly tries to load a new row of data.
    How would we remove this character LF from the data or force the load to only use CRLF as the row delimiter.
    Attached is an example of the file where the 3rd row has the above mentioned error.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    You did not say the flavour of the unix, but many unix versions and all linux typically have the filters 'dos2unix' and 'unix2dos' for this purpose.
    If you lack these filters you can simulate them (google helps here).
    But usually it is better to arrange that the source system does not place CRLF into the line endings, and also to ensure that the mechanisms used for file copy from source-system to target-system either use a binary copy (when no codepage conversions are needed), or use a copy that does not change the line terminators.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    try this:

    tr -d '\n' < File.TXT | tr '\r' '\n' > File.TXT.u
    Regards,
    Mark.

  4. #4
    Join Date
    May 2014
    Posts
    3

    Almost right

    Hi Mark,

    Thanks this almost works the only thing is it removes a character so the error row ends up having 1026 instead of 1027 characters.

    I ideally want to replace the first /n with some other character or space.

    The way the file is generated is out of our control as we do not have access.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    tr '\n\r' ' \n' < File.TXT > File.TXT.u
    ?
    Regards,
    Mark.

  6. #6
    Join Date
    May 2014
    Posts
    3

    Still not working

    Unfortunately still not working still leaves the error row with one fewer character.

  7. #7
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    So is a linefeed character valid in the source-database column?
    Is a linefeed character also valid in the target-database column ?

    If the linefeed character is valid data in both the source-column and target-column then you have to preserve it.

    for import/load utilities: are you specifying file file ASC ? What options are you using: delprioritychar ? nochardel ?

    If you cant get a working combination then consider pre-processing the file (awk is useful)

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    try this:

    Code:
    awk 'BEGIN {RS="\r\n"} {gsub(/\n/," "); print $0}' File.TXT > File.TXT.u
    it treats CRLF as a row separator and then amends \n symbol to a space.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •