How to split CSV file into multiple files using PowerShell

Split CSV file into multiple files using PowerShell

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

Freeware: Export Outlook data to CSV file

Tools for Exchange Server

Recommended articles

How to set up out of office replies in Office 365

How to set up out of office replies in Office 365

Out of office messages in Microsoft 365 are extremely useful. Read all about them.
MailTips in Microsoft 365

MailTips in Microsoft 365

MailTips, or mail tips, are the notifications Outlook, OWA or Outlook for iOS and Android shows when an email you create meets some requirements. Why are they so important?
How to migrate Microsoft 365 mailboxes with native tools

How to migrate Microsoft 365 mailboxes with native tools

Mergers and divestitures are the moments in an organization’s lifecycle when you, as an admin, might face the task of moving your users’ mailboxes. Nowadays, with more and more companies investing in the cloud infrastructure like Microsoft 365, a very common mailbox-move scenario is the tenant-to-tenant migration. Read on to get a comprehensive guide on how to prepare for the migration and run it by using the native migration means offered by Microsoft.

Comments

  1. Using switch -NoTypeInformation for Export-CSV should prevent this from appearing in generated files, then there will be no need to subsequently remove it :).

    • avatar
      Adam the 32-bit Aardvark says:

      Thanks! Don’t know why I didn’t write what to do to prevent the header from appearing .

  2. 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.

    • avatar
      Adam the 32-bit Aardvark says:

      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 */

  3. 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.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

CodeTwo sp. z o.o. sp. k. is a controller of your personal data.
See our Privacy Policy to learn more.