deleting duplicate entries in a .txt document

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
345
Hi there, I have a .txt document which my spreadsheet writes a string of values to.

Each line is 5 strings long seperated by a comma (,) eg:

Birmingham, 124, aaaaaa, bbbbbb, cccccc
Birmingham, 125, aaaaaa, bbbbbb, cccccc
London, 176, aaaaaa, bbbbbb, cccccc
Newcastle, 132, aaaaaa, bbbbbb, cccccc
London, 177, aaaaaa, bbbbbb, cccccc

What I'm looking to do is remove duplicates based on the first column (city name). In this example Birmingham & London. The entries which are higher up in the list are the older ones which need to be deleted (Birmingham 124 & London 176)

This code will remove duplicates from the text file and create a new (temp output) file based on column 1 (j=0) but it will ONLY filter out column 1

Code:
Sub removeMapDuplicates()
    Const ForReading = 1, ForWriting = 2
    Dim i, j
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objInputFile = objFSO.OpenTextFile(ThisWorkbook.Path & "file_to_remove_duplicates.txt", ForReading)
    Set objOutputFile = objFSO.OpenTextFile(ThisWorkbook.Path & "output.txt", ForWriting, True)
    Set objDict = CreateObject("Scripting.Dictionary")
    j = 0
    'On Error Resume Next
    While Not objInputFile.AtEndOfStream
        arrinputRecord = Split(objInputFile.Readline, ",")
        strFirstField = arrinputRecord(0)
        If objDict.Exists(strFirstField) Then
            j = j + 1
        Else
            objDict.Add strFirstField, strFirstField
        End If
    Wend
    colKeys = objDict.Keys
    
    For Each strKey In colKeys
        objOutputFile.WriteLine objDict.Item(strKey)
    Next
    
    objInputFile.Close
    objOutputFile.Close
End Sub
Is there a way (without importing the entire file into the workbook) to remove duplicates from my original .txt file?

If you can point me in the right direction, I'd be very grateful

Thanks

Rob
 

Forum statistics

Threads
1,085,905
Messages
5,386,697
Members
402,011
Latest member
yousraemara

Some videos you may like

This Week's Hot Topics

Top