Replace /" from the 14th column of the file in unix shell script ksh

Question

I have one file which has 10000+ records. There are some bad records which has /" in between the value like below. This is comma delimeter file. I cannot use -E GNU option.

"22-11-2020","ABCD",,"ABCD/4400","7644","NY","NY","LAX","ABC","16-09-2023","25-09-2023","02-10-2023","660802","993 14\" X 8\"","28-09-2023",4,"CA",2586,"AA",65.850000,6895.200000,"01-11-2023"

I need to remove/replace " from 14th column. I tried below command but its replacing all the double quotes, but not the \ .

sed 's/'\"'/''/g' File

Need the Output as below :

"22-11-2020","ABCD",,"ABCD/4400","7644","NY","NY","LAX","ABC","16-09-2023","25-09-2023","02-10-2023","660802","993 14 X 8","28-09-2023",4,"CA",2586,"AA",65.850000,6895.200000,"01-11-2023"

Answer 1

Assuming that all you want is to replace the string \" with the empty string, the following sed command should work:

sed 's/\\"//g' filename

If you only want to do this in the 14th column, you could use awk;

awk  'BEGIN {FS=","; OFS=",";} {gsub(/\\"/, "", $14); print}' filename

Answer 2

Use this Perl one-liner:

perl -F, -lane '$F[13] =~ s{\\"}{}g; print join ",", @F;' in_file > out_file

To change the file in-place:

perl -i.bak -F, -lane '$F[13] =~ s{\\"}{}g; print join ",", @F;' in_file > out_file

The Perl one-liner uses these command line flags:
-e : Tells Perl to look for code in-line, instead of in a file.
-n : Loop over the input one line at a time, assigning it to $_ by default.
-l : Strip the input line separator ("\n" on *NIX by default) before executing the code in-line, and append it when printing.
-a : Split $_ into array @F on whitespace or on the regex specified in -F option.
-F, : Split into @F on ,, rather than on whitespace.
-i.bak : Edit input files in-place (overwrite the input file). Before overwriting, save a backup copy of the original file by appending to its name the extension .bak. If you want to skip writing a backup file, just use -i and skip the extension.

The regex uses this modifier:
/g : Match the pattern repeatedly.

See also:

• perldoc perlrun: how to execute the Perl interpreter: command line switches
• perldoc perlre: Perl regular expressions (regexes)
• perldoc perlrequick: Perl regular expressions quick start

Answer 3

This might work for you:

sed ':a;s/^\(\([^,]*,\)\{13\}[^,]*\)\\"/\1/;ta' file

Iterate backwards through the 14th field removing each occurrence of \" and return the result.