Removing certain fields from a CSV file

Question

I have a large CSV file (~15GB) which a client has sent over as an export of a table. The format is a follows:

field, ... , field, field which sometimes contains commas,
another field which sometimes contains commas, field, ... , field

Consequently, I can't import the data because of the shift produced in some rows where the fields contain one or more commas.

I do not need the data in those two fields, so getting rid of them would probably be the best option. Consequently, to avoid the two fields in the middle that cause trouble, I was thinking of something that for each row would take the first 9 fields (the ones before the two) and the last 57 fields (the ones after the two).

I'm guessing this is something that can be done with sed or awk but my expertise with these tools (or whatever may be better) is rather limited.

Answer 1

To get the fields you need with awk:

awk -F,   '{for (i=1 ;i<NF;i++)if(i<10 || i>NF-56) printf "%s" , $i","}
           {print $NF}
    ' your_file > new_file

Answer 2

Using Perl:

$ perl -F, -wane 'print "$F[0],$F[-1]"' your_file > new_file 

Also you could use sed since you don't care about anything in the middle, that's essentially everything in between the 2 outside commas, you can do this:

$ sed 's/,.*,/,/g' your_file > new_file

Answer 3

The first 9 fields, and the last 57 fields

perl -F, -ane 'BEGIN {$,=","} print @F[0..9], @F[-57..-1]' file > newfile

Answer 4

Couldn't resist: here's a Python (2.7) script that reads from the input and writes the modified lines back to the output:

try:
    while True:
        line = raw_input()
        parts = line.split(',')
        newLine = ','.join(parts[:9]) + ',' + ','.join(parts[-57:])
        print newLine
except EOFError:
    pass

You can either save this to a file like csvmod.py and run cat input.csv | python csvmod.py > output.csv, or if you're looking for something slightly less readable you can use the following line:

cat input.csv | python -c "exec(\"try:\n while True:\n  line = raw_input()\n  parts = line.split(',')\n  newLine = ','.join(parts[:9]) + ',' + ','.join(parts[-57:])\n  print newLine\nexcept EOFError:\n pass\n\")" > output.csv

I think for a Python 3 solution you'd just need to replace the raw_input with input.