I want to remove an array which does not have a fixed size.For example
file.csv
001,"Geography",TRUE,"{1,0,1,1}",1,"CHARLIE"
002,"History",FALSE,"{0,0,1,1}",0,"DAVID"
003,"Mathematics",TRUE,"{1,1,1,1}",1,"SAM"
The following array should be converted into the below format
file_edited.csv
001,"Geography",TRUE,1,"CHARLIE"
002,"History",FALSE,0,"DAVID"
003,"Mathematics",TRUE,1,"SAM"
How to do this in the command line.
I'm sure there are tons of different ways to accomplish this, I prefer sed:
sed 's/"{.*}",//'
On GNU/Linux (GNU sed), use:
sed 's/"{.*}",//' csv
001,"Geography",TRUE,1,"CHARLIE"
002,"History",FALSE,0,"DAVID"
003,"Mathematics",TRUE,1,"SAM"
OS X (BSD sed, note the -E flag):
sed -E 's/"{.*}",//' csv
001,"Geography",TRUE,1,"CHARLIE"
002,"History",FALSE,0,"DAVID"
003,"Mathematics",TRUE,1,"SAM"
sed
-E is supported by both GNU and BSD sed
g at the end of your sed in case of multiple pattern occurences in the same line ?
Use Python and its CSV module, for example the following single command:
python -c 'import sys,csv
w = csv.writer(sys.stdout, quoting=csv.QUOTE_NONNUMERIC)
for row in csv.reader(sys.stdin):
del row[3]
w.writerow(row)' < file.csv > file_edited.csv
The magic happens on the second-to-last row in the command. In Python, arrays start at index 0, so row[3] is the fourth column; so, this one simply removes the fourth column on each record.
Alternatively, you can simply print columns 0, 1, 2, 4, and 5, using
python -c 'import sys,csv
w = csv.writer(sys.stdout, quoting=csv.QUOTE_NONNUMERIC)
for row in csv.reader(sys.stdin):
w.writerow([row[0], row[1], row[2], row[4], row[5]])' < file.csv > file_edited.csv
Using the CSV-aware Miller (mlr) tool to cut out (remove) the 4th field from the headerless CSV data:
$ mlr --csv -N cut -x -f 4 file
001,Geography,TRUE,1,CHARLIE
002,History,FALSE,0,DAVID
003,Mathematics,TRUE,1,SAM
Note that Miller will only ever quote fields that actually needs quoting.
