Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Find string at line # in text file, further split the string

    Hello again!

    I've got a program that outputs a report in PDF. I've written a procedure to convert the PDF to a .txt file. The information is always unique in each file, but the formatting is always the same. For instance line 86 will always output the following string: (obviously the last name, first name and middle will be different for each case)

    Last Name: McBeepeeton First: Beepy Middle: B

    I use the following to get that string:

    Code:
    Sub FindString()
        Dim fn As String, myLine As Long, txt As String, x
        fn = "C:\Documents and Settings\beep\My Documents\pdfmergetest\print_all_doc.txt" 
        myLine = 86 
        txt = Space(FileLen(fn))
        Open fn For Binary As #1
        Get #1, , txt
        Close #1
        MsgBox Split(txt, vbCrLf)(myLine - 1)
    End Sub
    I would like to further split that line into substrings that outputs ONLY "Beepeeton" and "Beepy". I will then put those strings into a form for automation. The find the line part, and the automation part is working! Just need the bridge!

    Not sure how to go about this seems to be more advanced than the MSDN would like to reveal to me.
    Version: Access 2010

  2. #2
    Join Date
    Nov 2012
    Posts
    79
    Perhaps set use the white spaces as delimiters? My feeble little mind can't process the solution though :P
    Version: Access 2010

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Use the split function then remove the redundant data
    Dim data() as string ` this is probably incorect declaration for an array of string
    Data = split(mypdfdata,":")
    `so we have 4 elements in oyr array
    `data.0 contains lastname and is unused
    Data(1) = left(data(1),len(data(1))-5) `chop off the trailing `first:
    `repeat the process on data.2 but chop off the trailing middle
    Yoy will need to tweak the settings to make certain you remove the colon, redundant words and any surplus spaces
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2012
    Posts
    79
    Alrighty I'll give it a shot and see what I can do with it!
    Version: Access 2010

  5. #5
    Join Date
    Nov 2012
    Posts
    79
    Ok I've got part of it to work...just on the wrong line:

    Code:
    Sub test()
        Dim fn As String, myLine As Long, txt As String, x
        Dim data() As String
        fn = "C:\Documents and Settings\beep\My Documents\pdfmergetest\print_all_doc.txt" '<- change here
        myLine = 86 '<- change to suite
        txt = Space(FileLen(fn))
        Open fn For Binary As #1
        Get #1, , txt
        Close #1
        MsgBox Split(txt, vbCrLf)(myLine - 1)
        data = Split(txt, ":")  '<--------Error here see below
        data(1) = Left(data(1), Len(data(1)) - 5)
        data(2) = Left(data(2), Len(data(2)) - 5)
        MsgBox data(1) & data(2)
    End Sub
    If I run as this is, it outputs the first line of the text file which is incorrect. If I change it to:
    Code:
    data = Split(txt, ":") (myLine - 1)
    I get a type mismatch
    If I change it to:
    Code:
    data = split(txt, vbCrLf)(myLine - 1)
    I get a type mismatch
    Version: Access 2010

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry i thought you said you already had found the data.

    If you havent then you need to iterate through the file.
    Not certain but that may require using readln till you retrieve the 86 line.
    A more robust implementation would be to read each line and check to see if your data appears using instr. That way round you arent dependant on a specofic line number so the source program can change. Providing they uae the same form 'Lastname:'....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    or
    Code:
    Public Function ExtractNameWithRules(TargetString As String) As String
        
        Dim strSub As Variant
        Dim ix As Integer
        
        strSub = Split(TargetString, " ")   
        'actually you only need six of them: strSub = Split(TargetString, " ", 6)
        
        
        '////// BEGIN TEST STUFF TO BE DELETED
        For ix = 0 To UBound(strSub)
            Debug.Print CStr(ix) & " = " & strSub(ix)
        Next
        '////// END TEST STUFF TO BE DELETED
    
    
        ExtractNameWithRules = strSub(2) & " " & strSub(4)
    
    End Function
    
    'in immediate window:
    '            Print ExtractNameWithRules("Last Name: McBeepeeton First: Beepy Middle: B")
    '            0 = Last
    '            1 = Name:
    '            2 = McBeepeeton
    '            3 = First:
    '            4 = Beepy
    '            5 = Middle:
    '            6 = B
    '            McBeepeeton Beepy
    
    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2012
    Posts
    79
    Well by
    Code:
     MsgBox Split(txt, vbCrLf)(myLine - 1)
    I've found the data that I need and it outputs to a msgbox so I can see it for reference.

    Essentially after rereading your initial response and tinkering with the code a bit, I need to get the line (86), split it, and then populate the array data() with the output, correct?
    Version: Access 2010

  9. #9
    Join Date
    Nov 2012
    Posts
    79
    Quote Originally Posted by izyrider View Post
    or
    Code:
    Public Function ExtractNameWithRules(TargetString As String) As String
        
        Dim strSub As Variant
        Dim ix As Integer
        
        strSub = Split(TargetString, " ")   
        'actually you only need six of them: strSub = Split(TargetString, " ", 6)
        
        
        '////// BEGIN TEST STUFF TO BE DELETED
        For ix = 0 To UBound(strSub)
            Debug.Print CStr(ix) & " = " & strSub(ix)
        Next
        '////// END TEST STUFF TO BE DELETED
    
    
        ExtractNameWithRules = strSub(2) & " " & strSub(4)
    
    End Function
    
    'in immediate window:
    '            Print ExtractNameWithRules("Last Name: McBeepeeton First: Beepy Middle: B")
    '            0 = Last
    '            1 = Name:
    '            2 = McBeepeeton
    '            3 = First:
    '            4 = Beepy
    '            5 = Middle:
    '            6 = B
    '            McBeepeeton Beepy
    
    izy

    Well look at that!

    Could you explain the following? I believe it means that you are essentially looping through the array but I'm not really sure of the process

    Code:
        '////// BEGIN TEST STUFF TO BE DELETED
        For ix = 0 To UBound(strSub)
            Debug.Print CStr(ix) & " = " & strSub(ix)
        Next
        '////// END TEST STUFF TO BE DELETED
    Version: Access 2010

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why not put a breakpoint on the code and step through examing what is happening?
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2012
    Posts
    79
    Quote Originally Posted by healdem View Post
    Why not put a breakpoint on the code and step through examing what is happening?
    Always the same error, 13, type mismatch. I've run into this type of error messing with arrays in the past and I think changing the type to variant worked.
    Version: Access 2010

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    All that code does is output the elements of the array to the debug/intermediate window. You can safely delete it once you know its doing what you want. As the comments suggest.

    Personally id stick with my routine as it caters for the situation where there may be more than one first or lastname. Izyriders works providing you never get a double barreled name
    Ferinstance.
    Peter van der hoogenstadt
    First name peter
    Last name van der hoogenstadt.
    If you know your data only ever has a single forename and surname and initial Izyrisers code works. If there is a risk tat doesnt hold true then you should split on the colon as that acts as the true delimiter
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2012
    Posts
    79
    Got it!

    Code:
    Public Function ExtractNameWithRules(TargetLine As Integer, FilePath As String) As String
        
        Dim strSub As Variant
        Dim ix As Integer
        Dim lineToSTring As String
        Dim TargetString As Variant
            
        lineToSTring = Space(FileLen(FilePath))
        
        Open FilePath For Binary As #1
        Get #1, , lineToSTring
        Close #1
        
        TargetString = Split(lineToSTring, vbCrLf)(TargetLine - 1)
        
        strSub = Split(TargetString, " ")
        'actually you only need six of them: strSub = Split(TargetString, " ", 6)
        
        
        '////// BEGIN TEST STUFF TO BE DELETED
        For ix = 0 To UBound(strSub)
            Debug.Print CStr(ix) & " = " & strSub(ix)
        Next
        '////// END TEST STUFF TO BE DELETED
    
    
        ExtractNameWithRules = strSub(2) & " " & strSub(4)
    
    End Function
    Version: Access 2010

  14. #14
    Join Date
    Nov 2012
    Posts
    79
    Quote Originally Posted by healdem View Post
    All that code does is output the elements of the array to the debug/intermediate window. You can safely delete it once you know its doing what you want. As the comments suggest.

    Personally id stick with my routine as it caters for the situation where there may be more than one first or lastname. Izyriders works providing you never get a double barreled name
    Ferinstance.
    Peter van der hoogenstadt
    First name peter
    Last name van der hoogenstadt.
    If you know your data only ever has a single forename and surname and initial Izyrisers code works. If there is a risk tat doesnt hold true then you should split on the colon as that acts as the true delimiter
    Hmm. Good point. Let me play with it some more and see if I can't implement it.
    Version: Access 2010

  15. #15
    Join Date
    Nov 2012
    Posts
    79
    Code:
    Public Function ExtractNameWithRules(TargetLine As Integer, FilePath As String) As String
        
        Dim strSub As Variant
        Dim ix As Integer
        Dim lineToSTring As String
        Dim TargetString As Variant
            
        lineToSTring = Space(FileLen(FilePath))
        
        Open FilePath For Binary As #1
        Get #1, , lineToSTring
        Close #1
        
        TargetString = Split(lineToSTring, vbCrLf)(TargetLine - 1)
        
        strSub = Split(TargetString, ":")
        'actually you only need six of them: strSub = Split(TargetString, " ", 6)
        
        
        '////// BEGIN TEST STUFF TO BE DELETED
        For ix = 0 To UBound(strSub)
            Debug.Print CStr(ix) & " = " & strSub(ix)
        Next
        '////// END TEST STUFF TO BE DELETED
    
    
        'ExtractNameWithRules = strSub(1) & " " & strSub(2)
        strSub(1) = Left(strSub(1), Len(strSub(1)) - 6)
        strSub(2) = Left(strSub(2), Len(strSub(2)) - 6)
        MsgBox strSub(1) & " " & strSub(2)
    
    End Function
    Yay! works with funky names. Ouputs McBeepyton mc stupid pants Beepy
    Version: Access 2010

Posting Permissions

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