I have a csv file that looks like this:

1,'someval','otherval',,,,,
'','someotherval','some_otherval',,,,,
1BSD,'',,,,,
2,'val',,,,,
,,,,,,
2BSD,,,,,,
2BCD,,,,,,

Now, I want to split file whenever the first column of the new row is an integer value.

So, then for the above csv input< I must get 2 new files with contents:

1,'someval','otherval',,,,,
,'someotherval','some_otherval',,,,,
1BSD,'val',,,,,

and

2,'val',,,,,
,,,,,,
2BSD,,,,,,
2BCD,,,,,,

respectively.

How can I accomplish this using Bash and/or Python ? Thanks.

up vote 3 down vote accepted

You can use the csplit utility to split on a regular expression e.g.

csplit -z file.csv '/^[0-9]\+,/' '{*}'
80
42

(the counts indicate the number of characters output into each file - you can suppress them by adding the -s option).

The output files are named xx00, xx01 etc. by default - there are options to change the prefix and suffix if you wish.

Ex.

$ csplit -z file.csv '/^[0-9]\+,/' '{*}'
80
42
$ head xx*
==> xx00 <==
1,'someval','otherval',,,,,
'','someotherval','some_otherval',,,,,
1BSD,'',,,,,

==> xx01 <==
2,'val',,,,,
,,,,,,
2BSD,,,,,,
2BCD,,,,,,
  • Please correct me if I'm wrong. In my view csplit can only split 1 file to 2, it cannot split a file into multiple files. – kashish Jan 4 at 13:47
  • @kashish did you try it? it should split into as many files as there are matches - I can't verify that because your sample only contains 2 – steeldriver Jan 4 at 13:50
  • I tried. It says csplit: *}: bad repetition count – kashish Jan 4 at 13:53
  • @kashish sorry - I can't reproduce that error. Maybe you could update your question with a more representative sample of your input data? – steeldriver Jan 4 at 15:26

I wanted to see how much of this I could do with sed, and I did manage to do quite a lot of it. We can write files with sed using the w and W commands, but I couldn't think of a way to write a different file with each iteration of a sed loop, so I had to use a shell loop. sed is probably the wrong tool to use for this job, and there's probably a nicer way to do it with sed. Anyway, here's what I came up with:

#!/bin/bash
sed ':a;N;s/\n/\x00/; ta' input | sed -r 's/\x00([0-9]+(,|\x00|$))/\n\1/g' > edited
n=0
while [ -s edited ]; do 
    ((n++))
    sed -n '1p' edited > csv-"$n"
    sed -i '1d' edited
done
sed -i 'y/\x00/\n/' csv-*
rm edited

Comments

  • replace newlines with the null character \x00 using a sed loop. This is so that we can use newlines as meaningful separators later.

    sed ':a;N;s/\n/\x00/; ta' input
    
  • pipe the result and add newlines before integers that were in the first field, and write the result to a file, edited

    | sed -r 's/\x00([0-9]+(,|\x00|$))/\n\1/g' > edited
    
  • initialise a variable to increment

    n=0
    
  • as long as edited is not empty, do things

    while [ -s edited ]; do
    
  • increment n

    ((n++))
    
  • write the first line of edited to a new file csv-$n where $n is the current value of n

    sed -n '1p' edited > csv-"$n"
    
  • delete the first line of edited

    sed -i '1d' edited
    

    that's the end of the loop, and because we only have one line for each file we want to write, this isn't as slow as processing every line of the original file in a loop, but still, it's slow!

  • for each file we created, turn the null characters back into newlines

    sed -i 'y/\x00/\n/' csv-*
    
  • delete the intermediate file

    rm edited
    

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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