0

I have a number of files up to a million records coming from a vendor.

These files are originally from a Windows environment and when we get them they have erroneous splits in the records caused by Line Feeds in the middle of text fields.

I think this is caused by characters that get interpreted as Line Feeds during the transfer to Linux, but am not sure as we never see the original Windows files.

What I need is a routine that will count the number of delimiters on a line and if it is below a specified threshold to then delete the Line Feed at the end of the record. So for example we know that a record is 29 columns and should have 28 "pipe" delimiters ("|") when we get the data because of the erroneous Line Feed we get two records, one with, for example 10 fields and 9 delimiters and a second with 19 fields and 18 delimiters. Here is an example with the data changed for security:

9999999999|Duck Donald|87|||999999999|9999999999|XX999999|||Z99999|999 Planet Ln|||Trumpet
ville|ZZ|99999||||||ZZ|P|9999999999|F|||

Notice that the line splits on the word Trumpetville. This is because of the Line Feed character that was inserted or mistranslated. Again looking for a woutine to count the delimiters and remove the Line Feed if the number of delimiters falls below specified threshold.

  • “...characters that get interpreted as Line Feeds during the transfer to LINUX...” A line feed is a line feed in windows OR linux, but the EOL, end of line does change between them. The line feed could be an intended part of the data. Would it help if you “quoted” each field between the pipes. Are you sure it is a line feed? 0x0A. Could it be a form feed? 0x0C or another non-text character? If the data can be represented by the ASCII character set you could replace all characters in the data outside of that range with a space 0x20 or null 0x00... – jc__ Jan 10 '17 at 14:08
  • Thanks for the response jc__ but it is definitely a CR/LF and it is not intentional or normally a part of the data . As I said we don't know how they get there, but they have the effect of splitting records into two records where there should be one. – powerski Jan 10 '17 at 14:13
  • So that means that you have a CR/LF in the data, sometimes and unwanted, and at the end of the line every time... That would rule out a simple character swap... – jc__ Jan 10 '17 at 14:19
  • Does the 'real' line end have a single CRLF or a double CRLF CRLF? – jc__ Jan 10 '17 at 14:21
  • When I view this through a Windows editor in has a CR/LF at the end of every lin, including those that were split. In Linux i understand this would be converted to a single LF. Thanks, larry – powerski Jan 10 '17 at 14:25
1

Try:

sed -e :1 -e 's/|/|/28;t' -e 'N;s/\n//;t1' < your-file

Or:

awk -F'|' '{while (NF < 29 && (getline nextline) > 0)
   $0 = $0 nextline; print}' < your-file

In case the text has CRLF Microsoft line delimiters, you may want to first process the files with dos2unix.

  • Hi Stephane, Thanks for your input on my question . I first tested the SED routine and saw that it removed the line feeds on records with fewer than 28 delimiters. I then reran the data through dos2unix to remove the CR's. I haven't tested the AWK routine yet as the first one seems to have done it. – powerski Jan 11 '17 at 16:06
0

Assuming that the unwanted CRLF in the data does not occur at the beginning of the field you could:

Replace the intended CRLF at the end of the line with the linux EOL, the LF.

This should occur after a pipe character. Ether directly or separated by a space. So replace the string(s) “pipe space CRLF” and “pipe CRLF” with “pipe space LF” or “pipe LF”

0x7C 0x20 0x0D 0x0A and 0x7C 0x0D 0x0A

to

0x7C 0x20 0x0A and 0x7C 0x0A

Now the only CRLF that remains are in the data. Replace that string “CRLF” to a space or null.

0x0D 0x0A to 0x20

edit:

The intended EOL should be between 2 pipes.

|data | CRLF

| data |

0x7C 0x0D 0x0A 0x7C
  • Hi jc___, Thanks Again. The CR/LF always occurs at the end of the line. When we receive the files the split has already happened, that is why I am looking for a way to count the delimiters. There is no way to determine if the CR/LF is valid other than by what proceeds it, which is the number of delimiters. If it is less than the predetermined count then we know it is a bad split and the next line should be appended to the current record by removing the LF at the end. Larry – powerski Jan 10 '17 at 14:57
0

bash script

I read that the original answer is not really what you're looking for. Please see the following script. This only works when you are aware of the amount of elements of the input file beforehand:

#!/bin/bash

infile=/home/wokie/duck.txt
outfile=/home/wokie/duck2.txt
# Define the amount of elements/columns in a row
maxelem=28

# Read the file, strip all newline characters and create one big variable
inputOneline=$(cat $infile | dos2unix | tr -d '\n')

count=0
# Read through the variable and split elements
for element in ${inputOneline//|/ }
  do
  if [ $count -lt $maxelem ]
    then
      # Write element to outfile while suppresing newline (-n) 
      echo -n "$element|" >> $outfile
      count=$[$count +1]
    else
      # Write newline to outfile when maximum elements is reached
      echo >> $outfile
      count=0
  fi
done

The whole idea is to first strip all newlines, after this a new file is put together with the newlines in the right places.

dos2unix

The script makes use of a great tool called dos2unix. By running the dos2unix with just the filename as a parameter it will automagically convert the input (Windows) file to unix format.

If you start the tool with the -id parameter, the number of CRLF occurences is counted, e.g.:

[test@testsystem ~]$ dos2unix -id /home/wokie/test2.txt 5 /home/wokie/test2.txt

In the example file there are 5 occurences.

You can find the dos2unix tool over here: https://sourceforge.net/projects/dos2unix/. Most distros provide dos2unix as a standard, or provide the possibility of installation by using apt-get or dnf.

  • Thanks wokie, but the problem is not converting the CR/LF to LF in Linux, but rather it is eliminating the first CR/LF or LF (depending on environment) in a pair of records where the first CR/LF or LF Iin Linux) is erroneous. I need a routinne to count the number of pipe ("|") delimiters and remove the LF in Linux where the delimiter count is below a given threshold. – powerski Jan 10 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.