Monthly Archives: April 2014

Splitting large CSV in smaller CSV

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