I have a txt file that can contain up to 13.000.000 randomly generated codes seperated with "\n" by the fputcsv function in php.

What is the most effective way of removing duplicates from this file?


  • 2
    Possible duplicate of Excel 2010 Removing duplicates – SeanClt Mar 29 '16 at 19:19
  • @SeanClt I have tried with excell. It crashes. – Mads Nielsen Mar 29 '16 at 19:21
  • Excel is the software xls and csv are just file format same solution applies – SeanClt Mar 29 '16 at 19:21
  • 2
    @SeanClt the OP has 13 MILLION records. The hard limit for rows in Excel 2010 is 1 million. – Burgi Mar 31 '16 at 12:47
  • If not excel then it would have to be done using vb code programmatically – SeanClt Mar 31 '16 at 13:04

I think the problem here is that Excel has a limit of 1,048,576 rows and your dataset has 13 million entries. Each entry should be on a separate line.

Assuming you are on Windows you can use a program called NotePad++ to find and remove duplicates. The answer over on StackOverflow suggests using either a regular expression or a plugin.

I would use the regular expression option. Below is a brief summary from the SO answer.

  1. Ensure each entry is on a single line
  2. Open the find and replace
  3. Select the regular expression mode
  4. Enter the expression ^(.*?)$\s+?^(?=.*^\1$) into the search box
  5. Leave the replacement box empty
  6. Click Replace All

If this data is an export from a database you might want to consider amending your SQL statement prior to the export by adding DISTINCT after the SELECT. See here for an example.

  • Please edit your answer and use better terminology. You are confusing lines, rows, columns and records. It does not help, of course, that the OP asks an unclear question - maybe wait for him/her to clean it up. – Jan Doggen Mar 31 '16 at 13:49
  • I haven't mentioned columns at all! ;) I take your point though. – Burgi Mar 31 '16 at 13:53
  • I tested this on a small file i wrote, looking like this(123 newline 123 newline 124.....) and i sucsessfully removed "123". But when i try this on my large file it removes the whole thing. What am i doing wrong? Thanks! – Mads Nielsen Mar 31 '16 at 15:57
  • @MadsAnkerNielsen the SO answer mentions that ticking or unticking the ". equates to a newline" may resolve that. – Burgi Mar 31 '16 at 17:45
  • 1
    My final solution was to use a plugin called textFX. So your answer was not the exact solution, but you send me on the right track. – Mads Nielsen Apr 1 '16 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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