Replace in a CSV file value of a column [closed]

Question

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 6 years ago.

Improve this 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

Answer 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

Answer 2

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.

Answer 3

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.

Answer 4

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

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

try the line above