Delete spcific rows and columns from a text file

[Jon von der Heyden]

Hi All

I am struggling to find methods to delete specific rows and columns from a text file.

I have a text file and I would like to create another text file based on the data within. I would like to build a function to delete multiple rows from the text file {1;2;3;4;5;6;7;8;9;11}. I would also need to know how to count the number of rows so that I can delete e.g. the last 6 rows {87623;87624;87625;87626;87627;87628}.

I would like to be able to do the same with columns.

Finally, all of the data has trailing spaces and I'm not sure what the best method is to trim the data. I have attempted to trim the entire string but it didn't complete it Trim$(strResult).

I have written two functions (below). The first returns a string of the text file data excluding the first n rows. The second writes a string to a text file. The sub routine shows how it is all pulled together, although in reality I would write the output to a new text file.

Function GetText(ByVal strFileName As String, ByVal lngStartLine As Long) As String
    Dim fso As Scripting.FileSystemObject
    Dim fts As Scripting.TextStream
    Dim strResult As String
    
    Set fso = New Scripting.FileSystemObject
    Set fts = fso.OpenTextFile(strFileName, ForReading)
    strResult = Replace$(fts.ReadAll, vbCrLf, "", , lngStartLine)
    strResult = Mid$(strResult, InStr(strResult, vbCrLf) + 2)
    GetText = strResult
    
    fts.Close
Finish:
    Set fso = Nothing
    Set fts = Nothing
    
End Function

Function WriteTextData(ByVal strFileName As String, ByVal strData As String)
    Dim lngFile As Long
    lngFile = FreeFile()
    
    Open strFileName For Output As #lngFile
    
    Print #lngFile, strData
    
    Close #lngFile
End Function

Sub test()
    Dim strData As String
    Const strPath As String = "H:\FY2010\BI_SOLUTION\SPP_DOWNLOADS\FY10_P1_INCOME_LINE_ITEMS.txt"
    strData = GetText(strPath, 9, 2)
    WriteTextData strPath, strData
End Sub

 

[doofusboy]

Something like this should work [except for the last 6 lines thing; would have to give that some thought]:

Dim strReadLine As String
Dim lngCounter As Long
    Open strSourceFileName For Input As #1
    Open strModifiedFileName For Output As #2
    Do While EOF(1) = False
        Line Input #1, strReadLine
        lngCounter = lngCounter + 1
        If lngCounter > 9 And lngCounter <> 11 Then 'exclude lines 1,2,3,4,5,6,7,8,9 and 11
            Write #2, Trim(strReadLine)
        End If
    Loop
    Close #1
    Close #2

 

[mikerickson]

If you use Application.Trim instead of VB's Trim, multiple interior spaces will be trimmed down to 1.
i.e. (if - is space), Trim("-a--b-") = "a--b" , Application.Trim("-a--b-") = "a-b"

 

[Jon von der Heyden]

Thanks both!

I wondered why that bloody Trim wouldn't do the job!

I'm running the following which on first test seems quite successful, although considerably slower than I had hoped. Still, it is a monthly routine so I am ok with the timing.

Option Explicit
 
Function GetText(ByVal strReadFile As String, ByVal strWriteFile As String, _
                    ByVal varRows)
 
    Dim strLineText As String
    Dim lngCounter As Long
 
    Open strReadFile For Input As #1
    Open strWriteFile For Output As #2
    Do While Not EOF(1)
        Line Input #1, strLineText
        lngCounter = lngCounter + 1
        If IsError(Application.Match(lngCounter, varRows, 0)) Then
            Write #2, Application.Trim$(strLineText)
        End If
    Loop
    Close #1
    Close #2
 
End Function
 
Sub test()
    Dim arr()
    Const strReadPath As String = "H:\FY2010\BI_SOLUTION\SPP_DOWNLOADS\FY10_P1_INCOME_LINE_ITEMS.txt"
    Const strWritePath As String = "H:\FY2010\BI_SOLUTION\SPP_DOWNLOADS\TEST.txt"
    arr = Array(1, 2, 3, 4, 5)
    GetText strReadPath, strWritePath, arr
End Sub

Thank you again.

 

[Jon von der Heyden]

Sorry, I do have one follow up question.

Is it possible to avoid it wrapping each line in text qualifiers "blah|blah"?

Thanks

 

[doofusboy]

Is it possible to avoid it wrapping each line in text qualifiers "blah|blah"?

I think if you use 'Print' instead of 'Write' you can get rid of the quotes (if i remember correctly).

            [B][COLOR=red]Print[/COLOR][/B] #2, Application.Trim$(strLineText)

 

[Jon von der Heyden]

Thanks again mate! Works perfectly...