Extracting data from extremely large csv files [closed]

Question

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.

Answer 1

Copy into PostgreSQL

Psha... Microsoft Sheeple. PostgreSQL is the clearly superior platform for this task. None of that faffing about with downloading things and figuring out an arcane configuration manager, just use your package manager (brew in my case) and brew install postgresql

Then, assuming more /tmp/my.csv looks like this..

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 just pipe the following script.sql into psql -f ./script.sql and you're friggin done! Al-dente copy-pasta

drop table if exists myCsv, myOkays, myDupes;

create temporary table myCsv (
     "uuid" uuid
    ,foo    text
    ,bar    text
);

copy myCsv 
from '/tmp/my.csv' delimiter ',' csv header;

select 0::int AS "dupe_count", * into myDupes from myCsv limit(0);
select * into myOkays from myCsv limit(0);

with dupes as (
    select "uuid", count(*) as "dupe_count"
    from myCsv
    group by "uuid"
    having count(*) > 1
)
insert into myDupes
select d."dupe_count", mc.* 
from myCsv as mc
join dupes d on d."uuid" = mc."uuid";

insert into myOkays
select mc.* 
from myCsv as mc
where not exists ( 
    select 1
    from myDupes d 
    where d."uuid" = mc."uuid"
);

Hey presto! You've now got

1. Your original file
2. A table with all duplicate records
3. A table with just the records you want

Of course you will want to do some further data sanitising. You may even want to export the data back out to csv on your filesystem to use in your data science tool. But exact details of errors you encounter there are probably best for another question.

Answer 2

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

1. Your original file for comparison
2. A file containing all the duplicates
3. 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.

Answer 3

Bulk insert to SQL Server

Who's that nutjob telling you to use Powershell?! Off topic! This is a site for DBAs, not developer nutjobs!

Use the developer edition of SQL Server you've got installed on your laptop to import the data and sanitize it in the database before you do analysis.

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 use the following script to load the data and split out the dupes.

CREATE TABLE #myCsv (
     [uuid] uniqueidentifier
    ,foo    nvarchar(max)
    ,bar    nvarchar(max)   
);

BULK INSERT #myCsv 
FROM 'C;\temp\my.csv'
WITH ( 
       FIELDTERMINATOR = ',', 
       ROWTERMINATOR   = '\n', 
       FIRSTROW        = 2 
     );

WITH dupes AS (
    SELECT [uuid], COUNT(*) AS dupe_count
    FROM #myCsv
    GROUP BY [uuid]
    HAVING COUNT(*) > 1
)
SELECT d.dupe_count, mc.*
INTO #myDupes 
FROM #myCsv mc
JOIN dupes d ON d.[uuid] = mc.[uuid];

SELECT * 
INTO #myOkays  
FROM #myCsv mc
WHERE NOT EXISTS (
    SELECT 1
    FROM #myDupes md
    WHERE md.[uuid] = mc.[uuid]
); 

Hey presto! You've now got

1. A table with your original data
2. A table with all duplicate records
3. A table with just the records you want

Of course you will want to do some further data sanitizing. You may even want to export the data back out to csv on your file system to use in your data science tool. But exact details of errors you encounter there are probably best for another question.