CODEWITHSUNDEEP
vbams-access
T. Karter
T. Karter

Reputation: 738

How to modify file line by line

I have a txt file in this syntax:

'foo','bar'
'foo','foo bar'
'foo','foo
bar bar'
'bar', 'foo'

I want to find each line that doens't start with ' and correct them. I want to end with:

'foo','bar'
'foo','foo bar'
'foo','foo bar bar'
'bar', 'foo'

The new line must be removed and added to the end of the previous line with a leading space. My code cicles through a file line by line and checks, if the first character is unequal to 'already.

I thought about adding every line to an array and do the correction within that array. I'd use

Open myFile For Output As #1
Write #1, lineOfArray
Close #1

to update the file.

My current code:

Sub Update_File(fileToUpdate As String, fileSys As Variant)

    Set File = fileSys.OpenTextFile(fileToUpdate)
    Do Until File.AtEndOfStream
        currentLine = File.ReadLine

        If Left(currentFile, 1) <> "'" Then
            'Magic here
        End If
    Loop
    File.Close

End Sub

I am struggeling with what is best practice and what is lean and quick code because the script should run over many 1000 files at the end. I don't know if it is possible to store the current and the next line and if next line doesn't start with ' then currentLine = currentLine & " " & nextLine and somehow update the the file, decrease the loop value by one and go ahead.

Upvotes: 0

Views: 176

Answers (3)

YasserKhalil
YasserKhalil

Reputation: 9548

Based on TinMan's wonderful code, try this vserion

Sub Test_UpdateFile()
UpdateFile ThisWorkbook.Path & "\Sample.txt", CreateObject("Scripting.FileSystemObject")
End Sub

Sub UpdateFile(ByVal filePath As String, ByRef fso As Object)
Const forReading = 1

Dim lines()     As String
Dim dirty       As Boolean
Dim r           As Long
Dim x           As Long
Dim k           As Long

With fso.OpenTextFile(filePath, forReading)
    lines = Split(.ReadAll, vbNewLine)
    .Close
End With

For r = 1 To UBound(lines)
    If Left(lines(r), 1) <> "'" Then
        lines(r - 1) = lines(r - 1) & " " & lines(r)
        lines(r) = Empty
        dirty = True
        x = x + 1
    End If
Next r

If dirty Then
    ReDim nLines(0 To UBound(lines) - x + 1)

    For r = 0 To UBound(lines)
        If lines(r) <> Empty Then
            nLines(k) = lines(r)
            k = k + 1
        End If
    Next r


    With fso.CreateTextFile(filePath, True)
        .Write Join(nLines, vbNewLine)
        .Close
    End With
End If
End Sub

Upvotes: 1

AcsErno
AcsErno

Reputation: 1615

What about this: 

currentline = File.ReadLine
NextLine = vbNullString
Do 
    If Not File.AtEndOfStream Then
        NextLine = File.ReadLine

        Do While Left$(NextLine, 1) <> "'"
            If Len(currentline) > 0 Then currentline = Trim(currentline) & " "
            currentline = currentline & Trim(NextLine)
            NextLine = File.ReadLine
        Loop
    End If

    Write #1, currentline
    currentline = NextLine

Loop Until File.AtEndOfStream

Upvotes: 0

TinMan
TinMan

Reputation: 7759

Here is how I would do it.

Sub Update_File(ByVal FilePath As String, ByRef fso As Object)
    Const ForReading = 1

    Dim lines() As String
    Dim r As Long
    Dim Dirty As Boolean

    With fso.OpenTextFile(FilePath, ForReading)
        lines = Split(.ReadAll, vbNewLine)
        .Close
    End With

    For r = 0 To UBound(lines)
        If Left(lines(r), 1) <> "'" Then
            lines(r) = "'" & lines(r)
            Dirty = True
        End If
    Next

    If Dirty Then
        With fso.CreateTextFile(FilePath, True)
            .Write Join(lines, vbNewLine)
            .Close
        End With
    End If

End Sub

Upvotes: 1

Related Questions