0

Second double quotes is missing in csv column. Looking for any ideas/ shell script for the below

  1. Find a specific column which is missing a ending double quotes.
  2. Add the missing double quotes at the end of the column.

Sample data:

79,A138270382563593,QMGXA1752256,Open Up,"Barry Devorzon;
3,A263163706496582,QMGXA1727673,A Different Time (Full),"BruceChianese, VTAM;
αғsнιη
  • 41,407
Bharathi
  • 11
  • 1
  • 1
    Please read How do I ask a good question? -- show some of the affected CSV rows so we can test. – glenn jackman Mar 02 '22 at 16:25
  • 1
    It is not clear what your desired output would be. for example, should "BruceChianese, VTAM; be formatted as "BruceChianese", VTAM; or "BruceChianese, VTAM"; or "BruceChianese, VTAM;" – fuzzydrawrings Mar 02 '22 at 19:02
  • Expected result: "BruceChianese", VTAM; – Bharathi Mar 02 '22 at 21:07
  • 1
    Pleas [edit] your question to show the expected output, don't add information in comments where it can't be formatted and could be missed. We still don't know if the first line should end in "Barry Devorzon;" or "Barry Devorzon"; or "Barry Devorzon",; or something else. – Ed Morton Mar 03 '22 at 13:29

2 Answers2

1

Assuming each line will have at most one " and that the closing quote must be inserted before the next , or ; (and also assuming each line will always end with ;):

sed 's/"[^,;]*/&"/ file.csv

Output:

79,A138270382563593,QMGXA1752256,Open Up,"Barry Devorzon";
3,A263163706496582,QMGXA1727673,A Different Time (Full),"BruceChianese", VTAM;
fuzzydrawrings
  • 1,656
  • 5
  • 12
0

Add a " to any column ( comma delimited) which is not already ending with an " :

with sed :

sed 's/\(,"[^",;]*\)\([,;]\)/\1"\2/' file.csv

With awk :

awk -F, -v OFS=, '{
    for ( i = 1; i <= NF; i++ ) 
        if ($i ~ /^"/ && $i !~ /"$/) 
            $i = $i ~ /;$/ ? substr($i, 0, length($i)-1) "\";" : $i "\""
}1' file.csv
DanieleGrassini
  • 2,824