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
- Your original file
- A table with all duplicate records
- 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.
grep
,awk
, or a Python one-liner should do just fine. – mustaccio Jun 13 '19 at 12:44