Replace in a CSV file value of a column

Question

I have a a CSV file with this structure:

31126000283424431;32285076389;t;text text;1;3;;1;1;0.9;0.81;0;0;1;1;1;2013-11-21;;NL
31126000279521531;32308233749;c;text text;1;2;;1;9;2.79;7.78;0;0;4;16;9;2013-11-21;;NL
31126000279406931;32291254349;c;text text;1;5;;1;3;0.98;0.96;0;0;3;9;0;2013-11-21;;NL
31126000272138431;32284912829;c;text text;1;3;;1;1;0;0;0;0;3;9;0;2013-11-21;;NL
31126000271468431;32304086789;t;text text;1;5;;1;1;0.2;0.04;0;0;2;4;1;2013-11-21;;NL
31126000269838731;29269530509;c;text text;1;1;;1;1;0.45;0.2;0;0;3;9;0;2013-11-21;;NL

and I need to replace the number after the sixth semicolon to 0.

So the output file would look like:

31126000283424431;32285076389;t;text text;1;0;;1;1;0.9;0.81;0;0;1;1;1;2013-11-21;;NL
31126000279521531;32308233749;c;text text;1;0;;1;9;2.79;7.78;0;0;4;16;9;2013-11-21;;NL
31126000279406931;32291254349;c;text text;1;0;;1;3;0.98;0.96;0;0;3;9;0;2013-11-21;;NL
31126000272138431;32284912829;c;text text;1;0;;1;1;0;0;0;0;3;9;0;2013-11-21;;NL
31126000271468431;32304086789;t;text text;1;0;;1;1;0.2;0.04;0;0;2;4;1;2013-11-21;;NL
31126000269838731;29269530509;c;text text;1;0;;1;1;0.45;0.2;0;0;3;9;0;2013-11-21;;NL

I have been trying awk, sed, and cut, but I can't get it to work.

thank you

@pfnuesel Valid point. However, it's only a matter when answers start to roll in. — devnull, Dec 06 '13 at 14:07
I tried things from this two post http://stackoverflow.com/questions/16487395/remove-spaces-from-a-single-column-using-bash http://stackoverflow.com/questions/14492590/using-bash-sed-awk-to-extract-rows-and-columns-in-csv-files — Ioannis, Dec 06 '13 at 14:09

score 7 · Accepted Answer · answered Dec 06 '13 at 14:09

7

your example shows the 6th col, but after the 5th semi.

awk -F';' -v OFS=';' '$6=0;7' file

try the line above

answered Dec 06 '13 at 14:09

Kent

189,393
32
233
301

score 1 · Answer 2 · answered Dec 06 '13 at 15:03

1

sed "s/;[^;]\{1,\}/;0/5" YourFile.csv

assume there is always something in colum

sed "s/;[^;]*/;0/5" YourFile.csv

change in every case even if there is no number is 6th column

answered Dec 06 '13 at 15:03

NeronLeVelu

9,908
1
23
43

score 0 · Answer 3 · answered Dec 06 '13 at 14:11

If you've got php on your machine you could use this very handy CSV Paser class. It will convert your CSV in to a 2D array, from which you can cycle through using a foreach loop and change the data.

$csvFile
foreach($csvFile as $value){
    foreach($value as $k => $v){
        if($k == 5){ $v = 0}
    }
}

This would automate it for a CSV file of any size of the same format.

score 0 · Answer 4 · answered Dec 06 '13 at 15:12

0

perl -MText::CSV_XS -e'$csv=Text::CSV_XS->new({sep_char=>";", eol=>"\n"}); while($row=$csv->getline(ARGV)) {$row->[5]=0;$csv->print(STDOUT, $row)}'

Use as filter or put input file as parameter. You should use proper CSV parser for any serious work.

answered Dec 06 '13 at 15:12

Hynek -Pichi- Vychodil

26,174
5
52
73

Replace in a CSV file value of a column

4 Answers4

Linked