Access 2003, create a function that will delete lines from a text file

I have a text file being created nightly in a network shared folder. That file has many lines including 3 duplicates. The line duplicated 3 times is:

ACCNT	Accounts Receivable - MU	OCASSET

Open in new window


What I need to do is, from within an Access function, delete the 2nd and 3rd dupe, leaving the first one intact.

Why the dupes exist is a rabbit hole that I don't right now have time to fix even though yes, that is the correct way to really fix this problem. But, if I can just have Access code which will delete the 2nd and 3rd entry of that line, it will suffice as a solution for now.

Thank you very much for any input.
LVL 1
BobbyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
Hi,

We Don't have many choice when it comes to remove data from a file:
Read everything, delete the file, and write back what we want to keep.
As for detecting duplicate rows, a collection will do the trick (since the key must be unique):
Public Sub Main()
    Dim data As Collection
    Set data = ReadFile("c:\………\myFile.txt")
    Kill path
    WriteFile path, data
End Sub

Public Function ReadFile(ByVal path As String) As Collection
    Const ForReading = 1

    Dim data As Collection
    Set data = New Collection

    Dim fso As Object    '// Scripting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim iStream As Object    '// Scripting.TextStream
    Set iStream = fso.OpenTextFile(path, ForReading)

    Do
        Dim line As String
        line = iStream.ReadLine

        If Not(ExistInCollection(line, data)) Then
            data.Add line, line
        End If
    Wend
    iStream.Close
    Set ReadFile = data
End Function

Public Sub WriteFile(byval path As String, byref data as collection)
    Const ForAppending = 8

    Dim fso As Scripting.FileSystemObject     '// Scripting.FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim oStream As Object     '// Scripting.TextStream
    Set oStream = fso.OpenTextFile(path, ForAppending, Create:=True)

    Dim line As Variant
    For Each line In data
        oStream.WriteLine line
    Next
    oStream.Close
End Sub

Public Function ExistInCollection(ByVal key As String, ByRef col As Object) As Boolean
    ExistInCollection = ExistInCollectionByVal(key, col) Or ExistInCollectionByRef(key, col)
End Function

Private Function ExistInCollectionByVal(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
    Dim item As Variant
    item = col(key)

    ExistInCollectionByVal = True
Exit Function
Error:
    ExistInCollectionByVal = False
End Function

Private Function ExistInCollectionByRef(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
    Dim item As Variant
    Set item = col(key)

    ExistInCollectionByRef = True
Exit Function
Error:
    ExistInCollectionByRef = False
End Function

Open in new window

BobbyAuthor Commented:
Thanks. Where do I add
ACCNT	Accounts Receivable - MU	OCASSET

Open in new window

?
BobbyAuthor Commented:
or are you saying that it is assumed there are no other duplicate lines, and that's how your code works?
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Fabrice LambertConsultingCommented:
Erm …..

Do you even know how VBA work ? (or any programming language) ?

You don't need to copy / paste the content of your file in the code. The code will read the content of your file, perform the desired operation, and write it.
All you need to do is call the Main procedure, and provide a valid path to the ReadFile function.

or are you saying that it is assumed there are no other duplicate lines, and that's how your code works?
In fact, it will remove any duplicate row.
BobbyAuthor Commented:
"In fact, it will remove any duplicate row." That's the answer. Thank you. I'll try your code asap.
Doug BishopDatabase DeveloperCommented:
I would recommend replacing Sub Main() with the following code. Using what Fabrice provided will work, but if there is a crash while writing the new dedupped file, you will have lost your original file. This will rename the original file, changing the .txt to .tmp. It is also generic as to the extension, so if it is .dat, .log, .csv, etc. it will rename to filename.tmp. If a temp file with that name already exists,m it will first be deleted. It will also give you an error if the filename you are trying to work with does not exist. You could even modify to pass the full path and filename to Sub Main() as a parameter.
Public Sub Main()
    Dim fullPath As String
    Dim path As String
    Dim fso As New FileSystemObject
    Dim fileName As String
    Dim fileExtension As String
    Dim tempFilename As String
    Dim data As Collection
    Dim err As Integer

    fullPath = "c:\Users\18eu\Documents\ME-XX-70100 Stats.txt"

    path = fso.GetParentFolderName(fullPath) & "\"
    fileName = fso.GetFileName(fullPath)
    fileExtension = fso.GetExtensionName(fullPath)

    If Dir(fullPath) = "" Then
        ' file does not exist. Take appropriate action
        err = MsgBox("The file " & fullPath & " does not exist.", vbCritical + vbOKOnly, "Error")
        
        Exit Sub
    End If

    tempFilename = Replace(fileName, "." & fileExtension, ".tmp")
    If Dir(path & tempFilename) <> "" Then Kill path & tempFilename
    Name fullPath As tempFilename
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobbyAuthor Commented:
Running the code Fabrice provided as-is (with my file path inserted of course), I get a compile error Wend Without While towards the end of Public Function ReadFile.
Fabrice LambertConsultingCommented:
    Do
        Dim line As String
        line = iStream.ReadLine

        If Not(ExistInCollection(line, data)) Then
            data.Add line, line
        End If
    While not iStream.AtEndOfStream

Open in new window

Side note:
No point awarded, mistake ?
BobbyAuthor Commented:
Fabrice,

I tried that fix but it still errors out. You didnt show how it ends, so i tried all possible variations but it errors out. Can you please show it pasted in context, so i see how you intend for it to be inserted. Thanks.
BobbyAuthor Commented:
Got it, thanks.
BobbyAuthor Commented:
No idea how i didnt award points before. How do i do that now?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.