Help Deleting Duplicates In CSV File

by 31 replies
42
I have HUGE CSV files (1.5GB) that I need to delete duplicate records out of.

I normally just use excel 2010 and I can do it with the push of a button. But these files exceed the 1,000,000 row limit.

I've searched the internet, found a couple PHP scripts that are supposed to do it, but I'm not exactly the sharpest tool in the shed when it comes to programming.

Before I go through the hassle of hiring someone on Odesk and paying them for what should 2 minutes of work I thought I'd ask here.

I've attached an example of the CSV file. (obviously a small one) in hopes that someone can help. I'd really appreciate it.

For the purpose of these files anytime a keyword is used twice the record is considered a duplicate, even if there is other data that's different in the row.

Hope that makes sense. The file is small, but I had to zip it because I guess you can't attach CSV files.

Thanks,
Paul
#programming #csv #deleting #duplicates #file
  • open up your csv file and insert this macro then run it / make a copy first justincase
    to add the macro open the file go to tools > macro > visual basic editor
    then select > insert > module
    in that page paste the code above and save / close the editor

    return to your page select tools > macro > macros > dltemyduplicates & then run
    • [1] reply
    • This has to be done in excel, right? The problem is the entire file doesn't open because it exceeds more than a million rows.

      Is there another program that I can use to open it and still run it?
      • [1] reply
  • Thank you, I also go to try
    • [1] reply
    • I used the sed command on it. Learned how to do it from this page (ctrl+f "delete duplicate, nonconsecutive lines from a file").

      edit: scratch that. This is an even better way... (source)
      awk '!x[$0]++' oldfile > newfile
      awk and sed commands are typically found on Linux or UNIX systems, but if you're using Windows, you can install cygwin.
      • [1] reply
  • I forgot about Windows Powershell.

    You can do it like this...(source)
    gc $filename | sort | get-unique > $newfileName

    It works good, but I don't know how it would work on a file as big as 1.5 GB. You'd just have to try it and see.

    Here's another way to do it if you go the cygwin route:

    sort test.csv | uniq > result.csv
    • [1] reply
    • Okay, getting closer and I really appreciate all the help. Things are starting make sense (honestly I don't how you programmer guys do this stuff!)

      I moved the file to C:, changed directory per your instructions, and got the following error.

      -bash: file1.csv: Permission denied

      So it would seem it found my file, but doesn't think I have permission to write to the C: drive is crazy. I'm the administrator on the machine and just put the darn test file there.

      If we can get this figured out I'll be forever greatful.

      I was "playing" around with windows powershell earlier trying to figure it out. I'll give that a shot, too.
      • [1] reply
  • You should close your cygwin shell. Then go to:

    Start Menu > All Programs > cygwin

    Then, right click on "Cygwin Terminal" and in the menu that comes up, click on "Run as administrator". Once you do that and you're running as administrator you should have no problems doing whatever you want.

    Since you've already got your text file located at C:\test.csv and you're now running cygwin as administrator, you can pick up on the instructions I mentioned earlier at the point shown below:

    navigate to the root of your c: drive by typing:
    cd /cygdrive/c
    Then you'll be at the root of the c: drive and you can just run the awk command on your file like this (the same command you mentioned above):
    awk '!x[$0]++' test.csv > test1.csv
    Once you've done that, you'll have the file called test1.csv located at c:test1.csv and that is the file with all duplicates removed.

    EDIT: The following command might work better and not choke on the large file or take so long because sorting the file first makes it easier (for your computer) to remove the duplicates.

    sort test.csv | uniq > result.csv
    • [ 1 ] Thanks
  • I was going to say another quick work around would be set up a local server and import it directly into a mysql database you could format the data and delete what you need and even run batch strings.
    • [2] replies
    • Thanks for all your help!

      I've just written a step-by-step for the non-techies like me of how to do this. It's posted here. Remove Duplicates in CSV Using PowerShell | Import data into a sreadsheet.

      Full credit given to this thread, the source mojojuju cited included.

      You guys are awesome. Thanks!
    • Is there an "idiots guide to mysql and php" that would show me how to do this? This is actually what I want to do long term is build a huge database so I can query how I want.

      I know how to set up WAMP and get a database set up, but other than that I'm clueless as how to import files, delete duplicates, etc during the process.
      • [1] reply
  • Open the CSV and sort the row you want to remove duplicates from smallest to largest. Insert a column next to that one and put the forumla in, assuming the duplicates are in cell A2

    =EXACT(A2,A3)

    Drag that down to the end of the spread sheet.

    Filter B1 now and only show 'TRUE' columns. Those are your duplicates.

    Delete those, problem solved.
    • [ 2 ] Thanks
    • [2] replies
    • Again, for small files you can easily do this in excel. I'm working with files that 5,000,000 rows of data. Excel 2010 can only handle 1,000,000

      So your solution doesn't apply in this case.
    • THANK YOU! That sure works for me, whose goal it is to NEVER have a file exceeding 1 million records.
  • Do it 5 times?
    • [2] replies
    • Wish it were that easy. When you import the file it imports a million records, then quits the import. It leaves you with your original file with 5 million records, and then the new one with 1 million.

      So you can't just "do it 5 times" believe me I wish it were that easy.
    • You couldn't just run the excel macro 5 times because you would only get the duplicates in that particular spreadsheet.
  • Banned
    [DELETED]
  • Did you used DuplicateFilesDeleter software ? I did use it for deleting duplicate file. Hope that help you. Thanks
  • Banned
    [DELETED]
Join the discussion

Next Topics on Trending Feed