Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers 4

up vote 1 down vote accepted

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
share|improve this answer
    
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). –  user1608941 Feb 7 at 22:42
    
@user1608941 Answer updated. –  Joseph R. Feb 8 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 –  user1608941 Feb 8 at 0:40
    
@user1608941 You're right, I fixed it. Thanks for pointing this out. –  Joseph R. Feb 8 at 0:45
    
Thanks, works great! Would you expect this awk code or the perl alternative proposed in another answer to run faster? –  user1608941 Feb 8 at 9:11
show 1 more comment

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
share|improve this answer
add comment

The first 9 fields, and the last 57 fields

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

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.

share|improve this answer
    
Thanks, also works! –  user1608941 Feb 10 at 12:16
    
@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 Feb 10 at 18:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.