Extracting data from extremely large csv files I have a 40gb csv file with over 60 million rows for data analysis. Each row has a unique identifier (some numbers). For example, the first row's unique identifier will repeat approximately 150,000 rows later.

I would like to have a method to run through the entire file, and extract rows with the same identifier and write them into new csv files. Is there a good, automated way to do that? Please note that the file is very large and excel has problems opening it.
Powershell to the rescue!

A CSV file is just a text file structured in a certain way. It doesn't have to open up in Excel, that's just what your default settings are configured to do at the moment. Use Powershell to protect yourself from nasty conversions that Excel does under the hood.

Assuming my.csv is saved in your c:\temp folder and looks like this when you open it in Notepad...

uuid,foo,bar
00000000-0000-0000-0000-000000000001,bing,bong
00000000-0000-0000-0000-000000000002,bork,björk
... lots more rows
00000000-0000-0000-0000-000000000001,blat,splunge


...then you can extract the duplicate values with the following script.

Set-Location C:\temp
$myCsv = Import-Csv ./my.csv
$dupes = ($myCsv | Group-Object uuid | Where-Object Count -gt 1).Name

$myDupes = $myCsv | Where-Object uuid -in $dupes
$myOkays = $myCsv | Where-Object uuid -notin $dupes

$myDupes | Export-Csv myDupes.csv
$myOkays | Export-Csv myOkays.csv


Hey presto! You've now got one 


Your original file for comparison 
A file containing all the duplicates 
A file containing just the uniques records


You may want to do some more work to determine which of the duplicate records to keep and possibly append back into your "good" file, but the exact details of that are probably best left for another question. Possibly over on StackOverflow if you're gonna keep flexing those Powershell skillz.