Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Delete Unwanted Lines from Text File.

    Hi,

    I tried this script to remove the unwanted lines from the text file without opening it but the result / output fails.
    Only the first line gets deleted others are not. Any one has a fast and easy script of doing it.

    Secondly the Row in text file where Total :- one row is blank and one contains the 'Amount of WithDrawal of Each' column has figures, I need to delete the empty row where the amount is empty.

    Sample text file and modified text file attached.

    Code:
    Option Explicit
    Sub TextFileTest()
        Dim strFilePath As String, _
        strFileName As String, _
        strMySQLStmt As String, _
        strNewTextForFile As String, _
        strOutputFile As String
         
        Dim varMyArray As Variant
         
        Dim blnFileRewrite As Boolean
         
        Dim objMyConn As Object, _
        objMyRS As Object
         
        strFilePath = "C:\Users\danny69\Desktop\textfiles\" 'Path (i.e. folder) containing the text file. Change to suit  - don't forget the trailing backslash.
        strFileName = "sample.txt" 'Text file from the 'strFilePath' folder above to be amended. Change to suit.
        varMyArray = Array("", "BRANCH", "BRANCH CODE", "REPORT ID", "======", " SrNo", "Selection", "Product Code", "All/Select/Range ", "Select Account Id", "From Account", "To Account", "Acct Type", "From Date", "To Date", "Filter", "Debit", "All Specific") 'Deletion criteria. Change to suit.
        
        If Dir(strFilePath & strFileName, vbDirectory) = vbNullString Then
            MsgBox "There is no file called """ & strFileName & """ in the " & vbNewLine & """" & strFilePath & """ directory.", vbCritical, "Amend Text File Editor"
            Exit Sub
        End If
         
        Set objMyConn = New ADODB.Connection 'Needs a reference to 'Microsoft ActiveX Data Objects [latest number available] Library'
        Set objMyRS = New ADODB.Recordset 'Needs a reference to 'Microsoft ActiveX Data Objects [latest number available] Library'
         
        objMyConn.CursorLocation = adUseClient
         
        objMyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strFilePath & ";" & _
        "Extended Properties=""text;HDR=NO;""" 'Note 'HDR' stands for header. Setting this to 'YES' will result in the following Loop starting from Row 2
         
        If objMyConn.State <> adStateOpen Then
            Set objMyConn = Nothing
            Set objMyRS = Nothing
            Exit Sub
        End If
         
        strMySQLStmt = "SELECT * FROM " & strFileName 'Create a SQL string
         objMyRS.Open strMySQLStmt, objMyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
        objMyRS.MoveFirst
         
        blnFileRewrite = False
         
        Do Until objMyRS.EOF
            If IsNumeric(Application.Match(objMyRS.Fields(0), varMyArray, 0)) = True Then
                blnFileRewrite = True
            Else
                If strNewTextForFile = "" Then
                    strNewTextForFile = objMyRS.Fields(0)
                Else
                    strNewTextForFile = strNewTextForFile & vbCrLf & objMyRS.Fields(0)
                End If
            End If
            objMyRS.MoveNext
        Loop
         
        Set objMyConn = Nothing
        Set objMyRS = Nothing
         
        If blnFileRewrite = True Then
            strOutputFile = strFilePath & strFileName
             'The following will either creates or overwrite the output file - no questions asked.
            Open strOutputFile For Output As #1
            Print #1, strNewTextForFile
            Close #1
            MsgBox "The """ & strFileName & """ file has now been updated.", vbInformation, "Amend Text File Editor"
        Else
            MsgBox "No action was taken as no text line(s) matched the desired criteria.", vbExclamation, "Amend Text File Editor"
        End If
         
    End Sub
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Danny,

    Attached is your code modified to remove the unwanted lines in the "sample text" file and produce an output like the "after delete sample.txt" file you posted.

    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks Maudibe for helping.

    I still need some help in the script, the text file contains some lines like Card No : xxxxx (xxxxx =numeric value) I want to modify the colon ':' to '-' dash, only for those lines "Card No : " and (2) Text file has DATE :dd/mm/yyyy which I want to verify with system date and if any mismatch warn it and then the script to hand over to next macro instead of saving the text file.

    Thanks in advance.
    Last edited by danny69; 2015-10-23 at 09:41. Reason: Added DATE

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Can you provide an "after delete sample file.txt" file that shows the desired formatting as well?

  5. #5
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Can you provide an "after delete sample file.txt" file that shows the desired formatting as well?
    Thanks for reply.

    New sample and after delete sample files uploaded.

    Thanks in advance
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •