mjhjmtu

I have a file like below,
cat Test.csv

"pav",12345,"ABCD,EF;xyz23;15rtg",,
"xyz",,"C4DEF;x23yu;rtg",,

After modification :

cat Test.csv

"pav",12345,"AB;xy;15",,
"xyz",,"C4;x2;rt",,

The 3rd field containing substrings delimited with ";" has to be replaced with their substrings

The following is using csvkit, because parsing CSV data that contains commas in quoted fields with awk directly is error prone.

This will get column three on the correct format:

csvcut -c 3 file.csv |
sed -r 's/^"|"$//g' |
awk -F';' -vOFS=';' ' for (i=1; i<=NF; ++i) $i = substr($i, 0, 2) printf(""%s"n", $0) ' >tmp-3rd

For the given input, this produces

"AB;xy;15"
"C4;x2;rt"

• 
  csvcut will cut out the third column.


• 
  sed will remove any double quotes from the data, if they appear first or last on the line.


• The awk program will go through the ;-delimited fields and cut them down to a length of two characters per field. It prints out the data with double quotes around it.


• The output is written to the file tmp-3rd.

Then it's just a matter of reassembling this with the original data (this is assuming bash or any other shell that can do process substitutions with <(...)):

paste -d, <( csvcut -c 1,2 file.csv ) tmp-3rd <( csvcut -c 4,5 file.csv ) | csvformat

• 
  paste will put the columns together with commas in-between.


• The first process substitution produces the first two columns from the original file, and the second produces the last two columns. In the middle, we provide the modified third column.


• As an optional step, we pass the data through csvformat which will quote or unquote fields as needed.

The output will be

pav,12345,AB;xy;15,,
xyz,,C4;x2;rt,,

Bypassing the need for the temporary file:

paste -d, 
 <( csvcut -c 1,2 file.csv ) 
 <( csvcut -c 3 file.csv | sed -r 's/^"|"$//g' |
 awk -F';' -vOFS=';' ' for (i=1; i<=NF; ++i) $i = substr($i, 0, 2) printf(""%s"n", $0) ' ) 
 <( csvcut -c 4,5 file.csv ) | csvformat

With perl

Assuming ; is only in third field

$ perl -pe 's/"K[^;"]*;[^"]*(?=")/$&=~s|([^;]2)[^;]+|$1|gr/e' ip.txt
"pav",12345,"AB;xy;15",,
"xyz",,"C4;x2;rt",,

• 
  "K to match " before string of interest and (?=") to match " after string of interest. But the " themselves not part of captured string as these are lookarounds


• 
  [^;"]*;[^"]* match any non ; or " characters followed by ; followed by non " characters


• 
  $&=~s|([^;]2)[^;]+|$1|gr to perform another substitution on the matched string


• 
  e modifier allows to use Perl code in substitution section

To restrict only for 3rd field

$ cat ip.txt 
"pav",12345,"ABCD,EF;xyz23;15rtg",,
"xyz",,"C4DEF;x23yu;rtg",,
"foo;12,23;good",124,253
12,5232,"xyz","ijk;5545;62"

$ perl -pe 's/^("[^"]*",|[^,]*,)2"K[^;"]*;[^"]*(?=")/$&=~s|([^;]2)[^;]+|$1|gr/e' ip.txt
"pav",12345,"AB;xy;15",,
"xyz",,"C4;x2;rt",,
"foo;12,23;good",124,253
12,5232,"xyz","ijk;5545;62"

Accurate and robust Python 3.x solution (based on csv.reader object):

parse_csv.py script:

import csv, sys
with open(sys.argv[1]) as f:
 reader = csv.reader(f)
 for l in reader:
 l = [s if ';' not in s else ';'.join(_[:2] for _ in s.split(';')) for s in l]
 print(','.join(i if not i or i.isnumeric() else '""'.format(i) for i in l))

Usage:

python3 parse_csv.py Test.csv

The output:

"pav",12345,"AB;xy;15",,
"xyz",,"C4;x2;rt",,

Python's csv module provides robust and flexible support for csv data.

Complex GNU AWK solution (parsing csv data):

awk -v FPAT='"[^"]+"|[^",]+|,,' ' 
 for (i=1;i<=NF;i++) 
 if ($i~/^".*;./) 
 len=split($i,a,";"); v=substr(a[1],1,3); 
 for (j=2;j<=len;j++) v= v";"substr(a[j],1,2);
 v=v"42"
 printf "%s%s",(v? v: ($i~/^,,/? (i==NF? ",":""):$i )),
 (i==NF? ORS:OFS); v="" 
 
 ' OFS=',' Test.csv

• FPAT='"[^"]+"|[^",]+|,,' - complex regex pattern defining field value




• if ($i~/^".*;./) ... - if the current field $i contains ; character(s)




• len=split($i,a,";") - split the field value $i into array a by separator ;. len is assigned with number of elements/chunks created




• v=substr(a[1],1,3); - capturing the first chunk of the needed length including leading " char, for ex. "AB will be extracted from "ABCD,EF




• for (j=2;j<=len;j++) ... - iterating through remaining chunks/items




• v=v"42" - add trailing double quote " to the processed sequence v. 43 is ASCII octal code representing the double quote char ".




• ($i~/^,,/? (i==NF? ",":""):$i ) - each empty field ,, is recreated with single comma , and common delimiter (also ,). This is to avoid redundant comma cluttering like "pav",,,




• (i==NF? ORS:OFS) - on encountering the last field i==NF - print output record separator ORS, otherwise - print output filed separator OFS

The output:

"pav",12345,"AB;xy;15",,
"xyz",,"C4;x2;rt",,