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.

# Configuration
$location = "C:\csvdrop\" # CSVs location
$rowsMax = 900; # how many rows per CSV?

# Get all CSV under current folder
$allCSVs = Get-ChildItem $location\* -include *.csv

# Read and split all of them
$allCSVs | ForEach-Object {
Write-Host $_.Name;
$content = Import-Csv $_.Name;
$insertLocation = ($_.Name.Length - 4);
for($i=1; $i -le $content.length ;$i+=$rowsMax){
$newName = $_.Name.Insert($insertLocation, "splitted_"+$i)
$content|select -first $i|select -last $rowsMax | convertto-csv -NoTypeInformation | % { $_ -replace '"', ""} | out-file $location\$newName -fo -en ascii
}
}

Hopefully, this works for you too!

 
3 Kudos
Don't
move!

6 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

Thoughts?