Splitting large CSV in smaller CSV

By | April 25, 2014

I had to use a tool today that gave me a frustrating error: “your csv file have more than a 1000 rows”. I had multiple CSVs that had more than 1000’s of rows actually, so splitting it manually would have been a total waste of time and sanity. That’s where PowerShell came in handy. As it took me more than 5 minutes to get the script right, I’m sharing it here.
The script is looking for all CSV’s within a defined folder ($location variable in the configuration), and split them by the number of rows defined in $rowsMax.
It is also removing the quotes in the CSV as by default export-csv generates quotes.

Hopefully, this works for you too!

 
4 Kudos
Don't
move!

8 thoughts on “Splitting large CSV in smaller CSV

    1. Dan

      Thanks Jesse, I was having the same issue and your suggestion helped.

      Reply
  1. Markos

    There’s an error in the for loop, I had to change it to for($i=$rowsMax; $i -le $content.length ;$i+=$rowsMax). Your loop only selects 1 record in 1st iteration ($i=1) and never actually gets to the end of the file.
    Thanks for the script though, it saved me lot of time.

    Reply
  2. kluo

    I have a file 340 lines, and the script only pop out 300 lines in a new file, left out 40 lines no output. Do you know what is wrong?

    Reply
  3. kluo84

    I have a csv file with 350 lines, the script gave the out put of 300 lines but didn’t give any output for the last 50 lines. Any help? Thanks

    Reply
  4. moubai

    got the same problem for FullName + bad loop
    except to get 1091 row with 30Rowsmax, but get 1080 row instead.
    So i change a little thing

    $insertLocation = ($_.Name.Length – 4);
    $length = $content.length + $rowsMax
    for($i=$rowsMax; $i -le $length;$i+=$rowsMax){

    Now i get all my row.
    the error is due the -le parameter, when you get the $rowmax = 1080
    1080 + 30 = 1100 = Larger than 1091, so the loop end.
    if you had $rowsMax + length, you go over a little of then end, but with no error

    Reply

Thoughts?