CODEWITHSUNDEEP
excelvbatext
user2520338
user2520338

Reputation: 41

How to remove empty lines from large txt file using excel VBA?

I'm trying to import a large TXT file into an excel file using VBA. The file is pipe delimited. But the source file is kind of messed up in that there are random line breaks which doesn't let me import the ifles correctly. 

COLUMN1|COLUMN2|COLUMN3
DataA1|DataA2|DataTextA3

DataTextA3Continued

DataB1|DataB2|DataTextB3

There technically shouldn't be that carriage return between DataTextA3 and DataTextA3Continued but this is the file I'm getting. How do I go about removing this in VBA? I tried replacing using vbCrLf but the file is large (20MB) and it crashes excel. Appreciate the help!

Thanks!

Code below. 

Sub ReplaceStringInFile()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

' Edit as needed
sFileName = Application.ActiveWorkbook.Path + "\DATAFILE" + Format(Now(), "yyyymmdd") + ".txt"

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
    Line Input #iFileNum, sBuf
    sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, vbCrLf, "THAT")

iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

End Sub

Upvotes: 1

Views: 707

Answers (1)

Comintern
Comintern

Reputation: 22205

If the extra newlines are always associated with the last column, I'd concentrate on the pipe delimiters, not on the lines. A "record" would constitute of 3 fields, so read the entire file into a buffer, and then assume that every third item needs to have any newlines replaced. Something like this:

Public Sub ReplaceStringInFile()
    Dim filepath As String
    ' Edit as needed
    filepath = Application.ActiveWorkbook.Path + "\DATAFILE" + Format(Now(), "yyyymmdd") + ".txt"

    Dim handle As Integer
    handle = FreeFile
    Open filepath For Input As #handle
    Dim buffer As String
    'Read the entire file.
    buffer = Input(LOF(handle), handle)
    Close #handle

    handle = FreeFile
    Open filepath For Output As #handle

    Dim lines() As String
    lines = Split(buffer, "|")
    Dim idx As Long
    'This is your output buffer.
    Dim record(0 To 2) As String
    'I'd probably add more bounds checking here, but this should give the gist.
    For idx = LBound(lines) To UBound(lines) - 2 Step 3
        record(0) = lines(idx)
        record(1) = lines(idx + 1)
        'Do your replacement here.
        record(2) = Trim$(Replace$(lines(idx + 2), vbCrLf, " "))
        Print #handle, Join(record, "|")
    Next

    Close #handle
End Sub

Upvotes: 1

Related Questions