How can I remove new line char in csv? csv has file separator symbol(\u001C) as a delimiter

Question

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

Output data

Answer 1

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.

Answer 2

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 ?

Answer 3

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