0

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.

closed as off-topic by mustaccio, kevinsky, MDCCL, John aka hot2use, LowlyDBA Jun 14 at 18:40

This question appears to be off-topic. The users who voted to close gave this specific reason:

If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    what do you hope you can do with smaller csv files that you can't do with a larger one, or a larger CSV imported into a database? – danblack Jun 13 at 6:19
  • 1
    Sounds like grep, awk, or a Python one-liner should do just fine. – mustaccio Jun 13 at 12:44
2

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.

3

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.

  • Thanks! but i might not have explained myself clearly. The 40gb files has over 50 mil rows, and an identifier in each row (e.g. 0000001). This identifier then repeats around 100,000 rows later in a new row, with different data in the subsequent columns. I essentially want to extract csv files from the main one, where each extracted csv file then contains only the rows belonging to same identifier (yup i do realize i may end up with 100,000+ small csv files, instead of 1 file contain all duplicates – abigfatcat Jun 13 at 17:34
  • @abigfatcat given that the pseudo code in this answer has cached a list of duplicate ids, what would you try to change about it to get the result you describe? Did what you try work? What was the exact error you saw and what did you try to work past it? Is the detail around this perhaps different enough to warrant a separate question? – Peter Vandivier Jun 13 at 17:39
  • @abigfatcat If you feel a bit overwhelmed and/or unable to proceed with your problem, I’d be happy to assist you in chat – Peter Vandivier Jun 13 at 18:31
  • that will be great. Thanks! – abigfatcat Jun 13 at 18:44
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.