0

I have a CSV file with 150+ columns, with file separator symbol as a field separator. The problem lies in one of the columns getting new line characters. For this, I want to remove those.

Input data

enter image description here

Output data

enter image description here

3
  • 1
    Put in code blocks. Your question is unintelligible without them. Do you want to remove every other newline starting with the first? Or all newlines except those right before a number? Or something else?
    – frabjous
    Jun 20 at 1:57
  • Yes I need to remove all new line. Each row should contain 5 fields.
    – user530495
    Jun 20 at 1:59
  • No, you don't need to remove all new lines, or else there will be only one row in the result. Please think harder about my question.
    – frabjous
    Jun 20 at 2:04

3 Answers 3

0

Using hd to show the FS characters (hex 1c) in the output:

$ perl -0777 -pe 's/^(\d{3}.*)\n/$1/mg' input.txt | hd
00000000  30 30 31 1c 42 61 6b 65  72 20 53 74 2e 4c 6f 6e  |001.Baker St.Lon|
00000010  64 6f 6e 1c 33 1c 34 1c  37 0a 30 30 32 1c 50 65  |don.3.4.7.002.Pe|
00000020  6e 6e 79 20 4c 61 6e 65  4c 69 76 65 72 70 6f 6f  |nny LaneLiverpoo|
00000030  6c 1c 38 38 1c 35 1c 37  0a                       |l.88.5.7.|
00000039

Without the hd, the output looks like this (FS characters are invisible but still present, so they will be in the output if it is redirected to another file, or if the -i "in-place edit" option is used):

$ perl -0777 -pe 's/^(\d{3}.*)\n/$1/mg' input.txt   
001Baker St.London347
002Penny LaneLiverpool8857

In both cases, this perl script slurps the entire file at once, (-0777), and captures each "line" (a sequence of characters beginning with three digits, up to but not including the next newline) and replaces it with the captured text (without the newline). In short, it strips the newline character from any "line" beginning with three digits.

Add a space after the $1 in the RHS if you want it to replace the unwanted newlines with a space, rather than just delete them. Or \x1c if you want it to change the newline to an FS character.

The s/// search-and-replace operation uses the m ("multi-line string") and g ("global") regex modifiers. g is common to several regex-using tools (including sed) and causes the regex to make "global" repeated matches, but m is specific to perl:

From man perlre (search for the "Modifiers" section):

m Treat the string being matched against as multiple lines. That is, change ^ and $ from matching the start of the string's first line and the end of its last line to matching the start and end of each line within the string.

NOTE 1: this script doesn't care what the "field" separators are. It doesn't look for or use fields at all. It would work just as well if the field separators were spaces, tabs, colons, or anything else (except newlines, of course).

NOTE 2: this won't work if any of the fields following an unwanted newline begin with three digits - e.g. 123 London. Handling that would require a more complicated script, one capable of parsing and counting the fields in the input.

0

You can use awk:

awk '{
  while (NF < 5  && getline cmp) { $0=$0"<br>"cmp }
  if (NF > 5) {
    print "#ERROR"
    count++
  }
  print
}
END{
  if (count) {
    print "FAILED "count" lines" > "/dev/stderr"
    exit 8
  }
}' FS=$'\x1c'
  • awk is very good at parsing fields with specified separators
  • NF tells the number of fields in the current row
  • you expect the newlines to occur always in 2nd field, then you know when the row has to be completed; just read next line and glue; maybe better to mark the glue with <br> than space so you don't lose information.
  • it becomes more difficult if the newline can occur in last field of the row, but your sample is safe; anyway this script catch the error

Yasta ?

New contributor
Thibault LE PAUL is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

Using Raku (formerly known as Perl_6)

raku -e 'slurp.split("\x1C").join("\t").put;'  

Sample Input (tab-separated):

~$ cat FS_test.txt
001 Baker St.
London  3   4   7
002 Penny Lane
Liverpool   88  5   7

~$ cat FS_test.txt | xxd
00000000: 3030 3109 4261 6b65 7220 5374 2e0a 4c6f  001.Baker St..Lo
00000010: 6e64 6f6e 0933 0934 0937 0a30 3032 0950  ndon.3.4.7.002.P
00000020: 656e 6e79 204c 616e 650a 4c69 7665 7270  enny Lane.Liverp
00000030: 6f6f 6c09 3838 0935 0937 0a              ool.88.5.7.

Convert tabs to FS (view hex using MacOS's xxd):

~$ raku -e 'slurp.split("\t").join("\x1C").put;' baker_st_FS_test.txt | xxd
00000000: 3030 311c 4261 6b65 7220 5374 2e0a 4c6f  001.Baker St..Lo
00000010: 6e64 6f6e 1c33 1c34 1c37 0a30 3032 1c50  ndon.3.4.7.002.P
00000020: 656e 6e79 204c 616e 650a 4c69 7665 7270  enny Lane.Liverp
00000030: 6f6f 6c1c 3838 1c35 1c37 0a0a            ool.88.5.7..

Convert tabs-to-FS and back again (FS-to-tabs):

~$ cat FS_test.txt | raku -e 'slurp.split("\t").join("\x1C").put;' | raku -e 'slurp.split("\x1C").join("\t").put;'
001 Baker St.
London  3   4   7
002 Penny Lane
Liverpool   88  5   7


~$ cat FS_test.txt | raku -e 'slurp.split("\t").join("\x1C").put;' | raku -e 'slurp.split("\x1C").join("\t").put;' | xxd
00000000: 3030 3109 4261 6b65 7220 5374 2e0a 4c6f  001.Baker St..Lo
00000010: 6e64 6f6e 0933 0934 0937 0a30 3032 0950  ndon.3.4.7.002.P
00000020: 656e 6e79 204c 616e 650a 4c69 7665 7270  enny Lane.Liverp
00000030: 6f6f 6c09 3838 0935 0937 0a0a 0a         ool.88.5.7...

Note, an extra blank line is added at the end of the file for each split/join roundtrip. Get rid of these by interposing a routine call to .subst(/\n/, :nth(*)) just before the final .put. Or get rid of all trailing-whitespace by running .trim-trailing on a slurped file. (Also, thanks to @cas for hex viewer idea).


ADDENDUM: Raku's translate routine also works:

raku -e 'slurp.trans("\x1C" => "\t").put;'

https://raku.org

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.