In various situations you may find that you need to evenly divide a large CSV file into multiple smaller files.
This may be the case, for instance, when you are preparing a staged migration and need to divide a list of mailboxes into batches, but not only then.
Luckily, splitting CSV files is exteremely easy to achieve using PowerShell.
All you need to do, is run the below script.
(keep in mind that encoding info and headers are treated as CSV file meta data and are not counted as rows)
# variable used to store the path of the source CSV file
$sourceCSV = <path of source CSV> ;
# variable used to advance the number of the row from which the export starts
$startrow = 0 ;
# counter used in names of resulting CSV files
$counter = 1 ;
# setting the while loop to continue as long as the value of the $startrow variable is smaller than the number of rows in your source CSV file
while ($startrow -lt <total number of rows in source CSV>)
{
# import of however many rows you want the resulting CSV to contain starting from the $startrow position and export of the imported content to a new file
Import-CSV $sourceCSV | select-object -skip $startrow -first <number of rows in resulting CSV> | Export-CSV "<resulting CSV filepath>$($counter).csv" -NoClobber;
# advancing the number of the row from which the export starts
$startrow += <number of rows in resulting CSV> ;
# incrementing the $counter variable
$counter++ ;
}
Suggested reading
CodeTwo Admin’s Blog: How to export users from Active Directory
CodeTwo Admin’s Blog: Available scenarios of Exchange to Office 365 Migration
Using switch
-NoTypeInformationforExport-CSVshould prevent this from appearing in generated files, then there will be no need to subsequently remove it :).Thanks! Don’t know why I didn’t write what to do to prevent the header from appearing .
Thanks Adam this works. Do you happen to know how to remove this from the file?
#TYPE Selected.System.Management.Automation.PSCustomObject
It’s being inserted into each file.
To remove the line from the files you already created, you could use something like that:
$csv1 = Get-Content -Path /* your csv file location */$csv1 = $csv1[1..($csv1.Count - 1)]
$csv1 | Out-File -FilePath /* again, enter the csv file location */
Thanks for uploading this script. I have a 4M row 90M cell csv monster file which your script allowed me to break down into smaller 100K row chunks ready for some ETL work.