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.

4 Answers 4


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
  • I might not have been clear enough, I didn't mean the first one and the last one, I meant all those before the two in the middle (1 through 9) and all those after (last-56 through last). Commented Feb 7, 2014 at 22:42
  • @user1608941 Answer updated.
    – Joseph R.
    Commented Feb 8, 2014 at 0:19
  • 1
    I'm guessing this might not work because when there is a comma in the 10th or 11th field I need to remove also the 12th or maybe even further down (if there is more than one comma). Would this be an appropriate solution? awk -F, '{for (i=1 ;i<NF;i++)if(i<10 || i>(NF-57)) printf "%s" , $i","}{print $NF}' my_file > new_file Commented Feb 8, 2014 at 0:40
  • @user1608941 You're right, I fixed it. Thanks for pointing this out.
    – Joseph R.
    Commented Feb 8, 2014 at 0:45
  • Thanks, works great! Would you expect this awk code or the perl alternative proposed in another answer to run faster? Commented Feb 8, 2014 at 9:11

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

The first 9 fields, and the last 57 fields

perl -F, -ane 'BEGIN {$,=","} print @F[0..9], @F[-57..-1]' file > newfile
  • Thanks, this also works great! I'm wondering whether awk or perl will run faster on the ~15GB file. Commented Feb 8, 2014 at 0:53

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

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

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.

  • @user1608941 Since raw_input() is probably more complex than a simple read from stdin, a similar solution which uses sys.stdin.readline() instead could be faster (but you'd need to remove the \n or \r\n from each line yourself). Similarly, using sys.stdout.write instead of print could also be somewhat faster. But these are just some thoughts, I haven't run any benchmarks about this.
    – brm
    Commented Feb 10, 2014 at 18:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .