Create multiple tiny CSV files from a large one – with PowerShell

Some time ago I wrote an article how to split a large CSV file by using categories that are used inside: Splitting up a big CSV file to multiple smaller CSV files using PowerShell

Sometimes you just want to separate a large file to smaller ones, e.g. because you are having trouble with large Exchange migration batches. I am starting to make Exchange migration batches not larger than 20 users because during the last weeks and months I very often face the problem that Migration batches just stuck and do nothing – without any error or further message. Most times they run, some few times… just nothing..

When you have small batches the troubleshooting is easier, the double work is less (in case that you have to recreate MoveRequests. Also be aware that there are situations when you must recreate your Moverequests and/or batches. See the official list of hybrid migration errors @Technet.

Okay back to topic. Let’s assume you have a CSV file with approx. 100 lines and want to split it up to CSVs with maximum 15 entries. To achieve this, you need some variables:

$csvlinecount = 15
$csvinputpath = "D:\Migration_Users.csv"
$csvcount = 1
$csvpointer = 1
$csvname = "Users"

What to use them for? It’s easy:

• $csvlinecount: Specifies the number of maximum lines in the new smaller CSVs.
• $csvinputpath: the path to the CSV that will be split.
• $csvcount: a counter we need later for naming of the result CSVs.
• $csvpointer: a pointer we use to navigate in the source CSV. It is initialized with 1 that it starts at the first line with data.
• $csvname: A fragment we use for naming the target CSVs

And the script itself is also easy:

$csvtemp = Get-Content $csvinputpath
while ($csvpointer -le $csvtemp.Length)
{
   $outpath = ((Get-ScriptDirectory) + "\" + $csvname + "_" + $csvcount.ToString() + ".csv")
   $csvtemp[0] | Out-File $outpath -Force #title
   $count = 1
   while ($count -le $csvlinecount)
   {
     $csvtemp[$csvpointer] | Out-File $outpath -Append
     $count++
     $csvpointer++
   }
   $csvcount++
}

To make the script work, you need an additional function “Get-ScriptDirectory” which delivers you the directory where the script is running in.

In general this script starts a new CSV file after 15 entries in the source file.

The result looks like this:

Above you see the 3 source CSVs, after 3 Scriptruns later it looks like below. Easy 🙂

The whole script:

function Get-ScriptDirectory
{
   if ($hostinvocation -ne $null){Split-Path $hostinvocation.MyCommand.path}
   else{Split-Path $script:MyInvocation.MyCommand.Path}
}

$csvlinecount = 15
$csvinputpath = "D:\Migration_Users.csv"
$csvcount = 1
$csvpointer = 1
$csvname = "Users"

$csvtemp = Get-Content $csvinputpath
while ($csvpointer -le $csvtemp.Length)
{
 $outpath = ((Get-ScriptDirectory) + "\" + $csvname + "_" + $csvcount.ToString() + ".csv")
 $csvtemp[0] | Out-File $outpath -Force #title
 $count = 1
 while ($count -le $csvlinecount)
 {
   $csvtemp[$csvpointer] | Out-File $outpath -Append
   $count++
   $csvpointer++
 }
 $csvcount++
}

Happy splitting 🙂

Related articles:

Splitting up a big CSV file to multiple smaller CSV files using PowerShell