Replace newline in quoted strings in huge files

Question

I have a few huge files with values seperated by a pipe (|) sign. The strings our quoted but sometimes there is a newline in between the quoted string.

I need to read these files with external table from oracle but on the newlines he will give me errors. So I need to replace them with a space.

I do some other perl commands on these files for other errors, so I would like to have a solution in a one line perl command.

I 've found some other similar questions on stackoverflow, but they don't quite do the same and I can't find a solution for my problem with the solution mentioned there.

The statement I tried but that isn't working:

perl -pi -e 's/"(^|)*\n(^|)*"/ /g' test.txt

Sample text:

4454|"test string"|20-05-1999|"test 2nd string"
4455|"test newline
in string"||"test another 2nd string"
4456|"another string"|19-03-2021|"here also a newline
"
4457|.....

Should become:

4454|"test string"|20-05-1999|"test 2nd string"
4455|"test newline in string"||"test another 2nd string"
4456|"another string"|19-03-2021|"here also a newline "
4457|.....

Answer 1

Sounds like you want a CSV parser like Text::CSV_XS (Install through your OS's package manager or favorite CPAN client):

$ perl -MText::CSV_XS -e '
my $csv = Text::CSV_XS->new({sep => "|", binary => 1});
while (my $row = $csv->getline(*ARGV)) {
  $csv->say(*STDOUT, [ map { tr/\n/ /r } @$row ]) 
}' test.txt
4454|"test string"|20-05-1999|"test 2nd string"
4455|"test newline in string"||"test another 2nd string"
4456|"another string"|19-03-2021|"here also a newline "

This one-liner reads each record using | as the field separator instead of the normal comma, and for each field, replaces newlines with spaces, and then prints out the transformed record.

Answer 2

With your shown samples, this could be simply done in awk program. Written and tested in GNU awk, should work in any awk. This should work fast even on huge files(better than slurping whole file into memory, having mentioned that OP may use it on huge files).

awk 'gsub(/"/,"&")%2!=0{if(val==""){val=$0} else{print val $0;val=""};next} 1' Input_file

Explanation: Adding detailed explanation for above.

awk '                                ##Starting awk program from here.
gsub(/"/,"&")%2!=0{                  ##Checking condition if number of " are EVEN or not, because if they are NOT even then it means they are NOT closed properly.
  if(val==""){ val=$0            }   ##Checking condition if val is NULL then set val to current line.
  else       {print val $0;val=""}   ##Else(if val NOT NULL) then print val current line and nullify val here.
  next                               ##next will skip further statements from here.
}
1                                    ##In case number of " are EVEN in any line it will skip above condition(gusb one) and simply print the line.
' Input_file                         ##Mentioning Input_file name here.