Batch script to remove rows in a CSV that are empty for a certain column

Question

Lets say Im having the following entries:

*ARTNR;*BESTAND;*HERSTARTNR;*WG;*WG2;*LWG;*LWG2
1000;2758;;PAS;;Passive Bauelemente;
5800;0;;PAS;MIC;Passive Bauelemente;Microchip Technology
5801;0;;AKT;AMP;Aktive Bauelemente;TE connectivity
5802;0;;PAS;;Passive Bauelemente;
5803;0;"PDZ 4,3B  7""SOD323";AKT;;Aktive Bauelemente;
5804;0;;MEC;HON;Elektromechanik;Honeywell
5805;0;;MEC;;Elektromechanik;
5806;0;;MEC;;Elektromechanik;
5807;0;;MEC;HON;Elektromechanik;Honeywell
5809;0;;AKT;ORS;Aktive Bauelemente;Osram Opto Semiconductors
5810;0;61240090;MEC;;Elektromechanik;
5811;0;AT89C55WD-24PU;AKT;ATM;Aktive Bauelemente;Atmel
5815;0;;MEC;;Elektromechanik;
5816;0;;MEC;;Elektromechanik;

I now want to remove all rows that are empty on the colum, *HERSTARTNR like the first row. Is this possible with a batch script?

Thank you

it's very easy with python

Ishikawa Yoshi
– Ishikawa Yoshi

2013-03-06 13:43:30 +00:00
Commented Mar 6, 2013 at 13:43 — Ishikawa Yoshi
– Ishikawa Yoshi, Commented Mar 6, 2013 at 13:43

rojo · Accepted Answer · 2013-03-06 14:04:33Z

3

To preserve the header row, test for non-semicolons in the first two columns.

In Windows:

findstr /v "^[^;]*;[^;]*;;" old.csv >new.csv

In *nix:

grep -P -v '^[^;]*;[^;]*;;' old.csv >new.csv

Linux example:

rojo@pico:~$ grep -P -v '^[^;]*;[^;]*;;' old.csv > new.csv
rojo@pico:~$ cat new.csv
*ARTNR;*BESTAND;*HERSTARTNR;*WG;*WG2;*LWG;*LWG2
5803;0;"PDZ 4,3B  7""SOD323";AKT;;Aktive Bauelemente;
5810;0;61240090;MEC;;Elektromechanik;
5811;0;AT89C55WD-24PU;AKT;ATM;Aktive Bauelemente;Atmel

Windows example:

C:\Users\me\Desktop>findstr /v "^[^;]*;[^;]*;;" old.csv > new.csv
C:\Users\me\Desktop>type new.csv
*ARTNR;*BESTAND;*HERSTARTNR;*WG;*WG2;*LWG;*LWG2
5803;0;"PDZ 4,3B  7""SOD323";AKT;;Aktive Bauelemente;
5810;0;61240090;MEC;;Elektromechanik;
5811;0;AT89C55WD-24PU;AKT;ATM;Aktive Bauelemente;Atmel

edited Mar 6, 2013 at 14:04

answered Mar 6, 2013 at 13:51

rojo

24.5k5 gold badges61 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kamituel · Accepted Answer · 2013-03-06 12:30:46Z

0

Will this do the trick?

perl -ne 'print unless /^\d+;\d+;;/' file.csv

answered Mar 6, 2013 at 12:30

kamituel

36.2k6 gold badges86 silver badges101 bronze badges

Collectives™ on Stack Overflow

Batch script to remove rows in a CSV that are empty for a certain column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related