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

Bobby
Bobby used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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

Author

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

Open in new window

?

Author

Commented:
or are you saying that it is assumed there are no other duplicate lines, and that's how your code works?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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.

Author

Commented:
"In fact, it will remove any duplicate row." That's the answer. Thank you. I'll try your code asap.
Database Developer
Commented:
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

Author

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 LambertConsulting
Distinguished Expert 2017
Commented:
    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 ?

Author

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.

Author

Commented:
Got it, thanks.

Author

Commented:
No idea how i didnt award points before. How do i do that now?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial