Consulting

Results 1 to 17 of 17

Thread: Editing Text Files using VBA

  1. #1
    VBAX Regular
    Joined
    Mar 2018
    Location
    Allen
    Posts
    7
    Location

    Question Editing Text Files using VBA

    Hello VBA Experts!

    I am trying to clean up a data file that imported some bullets and treats them like carriage returns.

    I am able to Open strFile For Binary Access etc. and delete the unwanted carriage returns and line feeds.

    The bullets are shown as a quotation mark followed by a tab ( e.g., "-> ) at the beginning of each line. Example: "->test -> test ->test
    So I only need to delete the first tab proceeded by a quotation mark.

    The code I have been trying unsuccessfully is:

    If InStr(1, strBuffer, "CHR(34)") = 1 Then
    strBuffer = Replace(strBuffer, vbTab, "")
    End If

    Is there VBA code for replacing the "-> at the beginning of the line with a ""

    Any help appreciated with this!

    Thank you,

    RBollinger
    Last edited by ROBBOLL; 03-18-2018 at 12:04 PM. Reason: Add introduction

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    CHR(62) = >

    So, maybe
    strBuffer = Replace(strBuffer, CHR(34) & vbTab & Chr(62), "")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Mar 2018
    Location
    Allen
    Posts
    7
    Location
    I forgot to mention that strBuffer = Replace(strBuffer, vbTab, "") works. So the problem is with the IF statement.

    if I comment out the IF statement it removes the tabs from the entire file.

    My depiction of a TAB (i.e., ->) probably isn't the best. But what it appears to look like using Notepad++ showing all characters.

    So if you create a tab delimited file and run Replace(strBuffer, vbTab, "") against it, it will remove all the tabs.

    To simplify the problem:
    If you edit the tab delimited file and put a quotation mark at the beginning of one of the rows. What I am trying to accomplish is to remove all the tabs just from that line.
    Last edited by ROBBOLL; 03-18-2018 at 02:21 PM.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I just noticed that you are looking for the Text String "CHR(34)" not for a Quote mark
    Const DQ = """   'I hate counting quote marks in code
    Const SQ = "'"
    Const Marker = "|"  'Edit to suit
    Constant Erase = ""
    
    If InStr(strBuffer, DQ) = 1 _
       And  InSr(strBuffer, vbTab) = 2 Then
          strBuffer = Replace(strBuffer, DQ & vbTab, Erase)
          strBuffer = Replace(strBuffer, vbTab, Marker) 'Or use Erase
    End If
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    2
    Location
    What about. Doing if strbuffer like "-> then strbuffer = mid(strbuffer, 4)?

  6. #6
    VBAX Regular
    Joined
    Mar 2018
    Location
    Allen
    Posts
    7
    Location
    strBuffer = Replace(strBuffer, vbTab, "") works. It's the IF statement that fails -- and I think it's because of my understanding of how it skips through a text file vs a table.



    I have looked at this long enough to better explain it . . .
    Problem: Importing a flat file with bullets that causes the import to fail.
    Text File (MyText.txt) tab delimited (I am using a hyphen and a greater than sign to represent a TAB. This represents about what you see when using notepad++ showing all characters)

    Column1->Column2->Column3->Column4LF
    blah blah->blah blah blah->3/19/2018->blah blah blah blahLF
    blah->blah blah->2/15/2018->blah bahCRLF <<point of failure>>
    "->blahCRLF
    "->blah blah blahCRLF
    "->2/20/2018CRLF
    "->blahCRLF
    blah blah->blah->2/10/2018->blah blahLF
    blah->blah->2/8/2018->blah blahLF


    Failure caused by importing bullet points.


    I am able to deal with the unwanted Line Feeds and Carriage Returns per the code below.
    mCr = Carriage Return and mLF = Line Feed if they are together (i.e., CRLF) they get removed.


    The problem is with the TABS after each quotation mark. They need to be remove so the content stays in one column. Here is my complete code for scrubbing the data:


    Sub Clean_Text()
    Dim strFile As String
    Dim strBuffer As String
    Dim ff As Integer
    Dim mCr As Integer
    Dim mLf As Integer

    strFile = "U:\TEMP\MyText.txt"

    strBuffer = Space(FileLen(strFile))
    ff = FreeFile
    Open strFile For Binary Access Read As #ff
    Get #ff, , strBuffer
    Close #ff
    '--------------------------------------- This is what is failing
    If InStr(1, strBuffer, Chr(34)) = 1 Then
    strBuffer = midReplace(strBuffer, vbTab, "")
    End If
    '---------------------------------------
    mCr = IIf(InStr(1, strBuffer, Chr(13)), 1, 0)
    mLf = IIf(InStr(1, strBuffer, Chr(10)), 1, 0)
    If mCr + mLf = 2 Then
    strBuffer = Replace(Replace(strBuffer, vbCrLf, ""), Chr(13) & Chr(13), Chr(13)) 'vbNullString
    End If
    strBuffer = Replace(strBuffer, Chr(29), " ")
    'strBuffer = Replace(strBuffer, Chr(34), "") 'vbNullString
    Kill strFile
    Open strFile For Binary Access Write As #ff
    Put #ff, , strBuffer

    Close #ff
    End Sub
    I hope I provided enough info. I know even simple problems can become even more problematic when you don't. I appreciate your help!


    Thank you,

    RBollinger

  7. #7
    VBAX Regular
    Joined
    Mar 2018
    Location
    Allen
    Posts
    7
    Location
    SamT: What you sent me fails at: Constant Erase = ""

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Delete the Const line and use simply
    Dim Erase as String
    This will make Erase, (any uninitialized String) = "" (an empty string)

    ps: "Constant" is not a valid VBA term
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    If you do not get a resolution soon can you post a sample line of text, even if it is made up by you.
    I have posted a couple of text delimiting examples on here.
    The difference is I use the Old Fashioned BASIC string manipulation rather than the more modern VBA versions.
    I will then attempt to use what I have found works on previous posts.

  10. #10
    VBAX Regular
    Joined
    Mar 2018
    Location
    Allen
    Posts
    7
    Location
    Thank you OBP. Here is a model of the problem:



    I have a flat file, pipe delimited such as this:


    |Column1|Column2|Column3|Column4
    |contents of column1|contents of column2|contents of column3|contents of column4
    |more contents of column1|more contents of column2|more contents of column3|more column4
    |more contents of column1
    |more
    "|contents
    "|of
    "|column2|more contents of column3|and some more contents of column4


    Note that something happened to column2where its contents of column2 are seperated by pipe delimiters messing up the integrity of the data. This is what I am trying to fix.


    Specifically: strBuffer = midReplace(strBuffer, "|", "") when run without the IF statement removes all the pipe delimeters from top to bottom. The only pipe delimiters that should be removed, are the ones with a quotation mark immediately preceding the delimiter.




    Sub Clean_Text()
    Dim strFile As String
    Dim strBuffer As String
    Dim ff As Integer
    Dim mCr As Integer
    Dim mLf As Integer
    strFile = "U:\TEMP\MyText.txt"
    strBuffer = Space(FileLen(strFile))
    ff = FreeFile
    Open strFile For Binary Access Read As #ff
    Get #ff, , strBuffer
    Close #ff
    '--------------------------------------- This is what is failing.
    If InStr(1, strBuffer, Chr(34)) = 1 Then
    strBuffer = midReplace(strBuffer, "|", "")
    End If
    '---------------------------------------

    Open strFile For Binary Access Write As #ff
    Put #ff, , strBuffer

    Close #ff
    End Sub

    Thank you, SamT, and Kgxd for your help.

    RBollinger

  11. #11
    VBAX Regular
    Joined
    Mar 2018
    Location
    Allen
    Posts
    7
    Location
    SamT: Erase by itself throws an error.


    Dim ff As Integer
    Dim mCr As Integer
    Dim mLf As Integer
    
    Dim EraseIt As String
    Const DQ = """   'I hate counting quote marks in code"
    Const SQ = "'"
    Const Marker = "|"  'Edit to suit
    Erase1 = ""
    
    strFile = "U:\TEMP\MyText.txt"
    
    strBuffer = Space(FileLen(strFile))
    ff = FreeFile
    Open strFile For Binary Access Read As #ff
      Get #ff, , strBuffer
    Close #ff
    '--------------------------------------- This is what is failing
    If InStr(strBuffer, DQ) = 1 _
       And InStr(strBuffer, vbTab) = 2 Then
          strBuffer = Replace(strBuffer, DQ & vbTab, Erase1)
          strBuffer = Replace(strBuffer, vbTab, Marker) 'Or use Erase
    End If
    '---------------------------------------
    
    
    Kill strFile
    Open strFile For Binary Access Write As #ff
      Put #ff, , strBuffer
      
    Close #ff
    End Sub
    Last edited by SamT; 03-20-2018 at 09:20 AM.

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    ROBBOL, try the code in this database, it is in the On click event of the Button on Form1.
    As I am not sure of your exact string, so see what string it gives you when you plug yours in place of my strdata after it runs.
    Attached Files Attached Files

  13. #13
    VBAX Regular
    Joined
    Mar 2018
    Location
    Allen
    Posts
    7
    Location
    OBP, I tried your code without success. How about with an actual table: Jobs.txt The actual problem representation is worth a 1000 words.

    job_id|job_desc|min_lvl|max_lvl
    1|New Hire - Job not specified|10|10
    2|Chief Executive Officer|200|250
    3|Business"| Operations Manager|175|225
    4|Chief Financial Officier|175|250
    5|Publisher|150|250
    6|Managing"| Editor|140|225
    7|Marketing Manager|120|200
    8|Public Relations Manager|100|175
    9|Acquisitions Manager|75|175
    10|Productions"| Manager|75|165
    11|Operations Manager|75|150
    12|Editor|25|100
    13|Sales Representative|25|100
    14|Designer|25|100

    Three of the rows have random "| inserted in them to screw up an import. The goal is to remove the quotation and vertical pipes to restore the integrity of the data.

    Sorry, I should have thought of this with the first posting! OBP this was actually your idea.

    Thanks for your help!

    RBollinger

  14. #14
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Maybe...
    strBuffer = Replace(strBuffer, DQ & vbTab, Erase1)
    strBuffer = Replace(strBuffer, DQ & vbTab, EraseIT)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    ROBBOL, I can't place that data in to a String as it gives an invalid character error, which may have something to do with why you can't work with it.
    However I posted database on here that imports the data from a text file one character at a time and based onthat makes decisions on wheter to include the data in the Record string.
    See the latter half of this post where I did the work for andycl
    http://www.vbaexpress.com/forum/show...o-Access/page4

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Actually you should be able to use my code
    count = InStr(strdata, "|")
    MsgBox count

    What do you get if you use that code replacing strdata with strBuffer
    Do you get a value for count?

  17. #17
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Why are you using binary?


    Path = "U:\TEMP\MyText.txt"
    s = CreateObject("Scripting.FileSystemObject").OpenTextFile(Path, 1).readall
    Debug.Print Replace(s, """" & vbTab, "")

Tags for this Thread

Posting Permissions

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