UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Look For Specific Text String, Go Back Two, Then Return Rest Of String To The Next Space    
 
   
bakersburg9
post Jun 16 2016, 06:00 PM
Post#1



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


A few years ago, someone helped me write a VBA procedure that looked for any string, without spaces, that had 2 periods in the string, and return the text string from period to period, excluding duplicates, since the string would appear more than once, but I want unique cases. This is to pull file paths out of a block of text. So in English, it would find a colon followed by a back-slash - it would go back 2 spaces, then go right to the end of the string. All file paths have a colon followed by a back slash, so it would pull out C:\Users\Smith123|MonthlyReports\uneSales.xlsx is this even possible ?
Go to the top of the page
 
LPurvis
post Jun 17 2016, 06:29 AM
Post#2


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Hi there.

In your example is the pipe a typo?
C:\Users\Smith123|MonthlyReports\uneSales.xlsx
(I'd imagine so as it's a disallowed character AFAIK ;-).

You could write a custom function to walk through the string - likely not dissimilar to your last one. But in either case you should be able to use a Regular Expression.
That said - the requirement that it removes duplicates.. that might be less trivial.

A simple fetch:
?fRegExpFind(strYourString, "[a-z]:\\[a-z0-9\.\\]*\s", False)

Where your function is fairly standard - something like:
CODE
Function fRegExpFind(strText As String, strPattern As String, _
    Optional blnMatchCase As Boolean = True, Optional strDelim As String = "")
    
    Dim objRegExp As Object
    Dim objMatches As Object
    Dim lngI As Long

    Set objRegExp = CreateObject("VBScript.RegExp")
    With objRegExp
        .Pattern = strPattern
        .Global = True
        .IgnoreCase = Not blnMatchCase
    End With
        
    If objRegExp.Test(strText) Then
        Set objMatches = objRegExp.Execute(strText)
        For lngI = 0 To objMatches.Count - 1
            fRegExpFind = fRegExpFind & strDelim & objMatches(lngI)
        Next
    Else
        fRegExpFind = ""
    End If
    
    fRegExpFind = Mid(fRegExpFind, Len(strDelim) + 1)
    
    Set objRegExp = Nothing
    Set objMatches = Nothing
    
End Function

You might not want the delimited string it outputs, perhaps an array would better suffice? You could more easily walk through the array and only output non-duplicates.
(I used to have some somewhere that returned an ADO recordset. ;-)

Cheers
Go to the top of the page
 
BruceM
post Jun 17 2016, 06:38 AM
Post#3


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


As I understand you have a block of text "Some Text and Then C:\Users\Smith123\MonthlyReports\JuneSales.xlsx". I assume this is a made up path, and the pipe character was just inadvertent use of the Shift key. If you just need to go from the drive letter to the end of the string you could do (assuming the text is in the field YourText):

Mid([YourText],Instr([YourText],":\") - 1)

I'm not clear on whether the requirement to exclude duplicates was from the problem a few years ago or the current problem or both. If you wish to exclude duplicates with the current problem it depends a little on what you mean by that. One possiblity is you could use the expression as a calculated query field, and use SELECT DISTINCT for the query.
Go to the top of the page
 
Jeff B.
post Jun 17 2016, 07:31 AM
Post#4


UtterAccess VIP
Posts: 9,880
Joined: 30-April 10
From: Pacific NorthWet


Just for the sake of complication, you could also use an expression (say, in a query) to return everything to the right of the location of the ":\" concatenated with the one character before the location... just sayin'...
Go to the top of the page
 
orange999
post Jun 17 2016, 07:31 AM
Post#5



Posts: 1,713
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Here is a procedure to parse a string and extract elements representing filenames. This is based on your description of requirements. As others have pointed out, the pipe character seemed to be a typo. I created some test data (mock up) to test the procedure. No logic to deal with duplicates. It prints the retrieved elements to the immediate window.

The test data:
C:\Users\Smith123\MonthlyReports\uneSales.xlsxD:\MyDir\test.txtF:\outlookArchive\Summer2015.txtc:\user\jimmy\documents\travel.accdbD:\user\frank\downloads\serviceInfo.zipG:\WeeklySummary_012.datH:\Presentations\2012\May12.ppt


CODE
'---------------------------------------------------------------------------------------
' Procedure : ParseText
' Author    :  
' Date      : 17/06/2016
' Purpose   : This is to pull file paths out of a block of text. So in English,
'it would find a colon followed by a back-slash - it would go back 2 spaces,
'then go right to the end of the string. All file paths have a colon followed
'by a back slash, so it would pull out C:\Users\Smith123\MonthlyReports\uneSales.xlsx
'
'Note: The original post had a pipe char"|" between 123|Monthly (typo???)
'
'Original is at http://www.UtterAccess.com/forum/index.php?showtopic=2037505&hl=
'---------------------------------------------------------------------------------------
'
Sub ParseText()
    Dim fso As FileSystemObject
    Dim MyFile As TextStream
    Dim MyStr As String
    Dim colCount As Integer            'number of files in the incoming string
    Dim sElement As String
    Dim i As Integer
10  Dim p1 As Integer: p1 = 99         'default non 0 value
20  Dim p2 As Integer: p2 = 99         'default non 0 value
30  On Error GoTo ParseText_Error

40  Dim colonslash As String: colonslash = ":\"
    Dim db As DAO.Database

50  Set db = CurrentDb
60  Set fso = New FileSystemObject
70  Set MyFile = fso.OpenTextFile("C:\users\mellon\documents\RawFileInfo.txt", ForReading, False)
80  MyStr = MyFile.ReadAll
90
    'how many file names are present  (each one contains a colon)
100 For i = 1 To Len(MyStr)
110     If Mid(MyStr, i, 1) = ":" Then
120         colCount = colCount + 1
130     End If
140 Next i
150 For i = 1 To colCount
        'Find the endpoints of the string
160     p1 = InStr(MyStr, colonslash) - 1              'starting point of the string to retrieve.
170     p2 = InStr(Mid(MyStr, 4), colonslash) + 1      'ending point of the string to retrieve
180     If p2 = 1 Then
190         sElement = MyStr
200     Else
210         sElement = Mid(MyStr, p1, p2)   'string to retrieve
220     End If
230     Debug.Print sElement  '; Len(sElement) debugging
        'reset the string to be processed (remove the element just retrieved)
240     MyStr = Mid(MyStr, p2 + 1)
250 Next i

260 On Error GoTo 0
270 Exit Sub

ParseText_Error:

280 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ParseText of Module ModuleTesting_CanKill"

End Sub


The result:
QUOTE
D:\MyDir\test.txt
F:\outlookArchive\Summer2015.txt
c:\user\jimmy\documents\travel.accdb
D:\user\frank\downloads\serviceInfo.zip
G:\WeeklySummary_012.dat
H:\Presentations\2012\May12.ppt
Go to the top of the page
 
BruceM
post Jun 17 2016, 09:39 AM
Post#6


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I wonder if we are overcomplicating this. The OP's explanation of the solution several years ago may be blurring the picture, which seems simply to be "This is to pull file paths out of a block of text. So in English, it would find a colon followed by a back-slash - it would go back 2 spaces, then go right to the end of the string." I take that to mean the intent is to find a drive letter, and extract everything from there to the end of the string.
Go to the top of the page
 
doctor9
post Jun 17 2016, 10:27 AM
Post#7


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


bakersburg9,

If you want to extract the path from a path/filename, just use InStrRev() to find the LAST backslash's position. Then use Left() to extract the left part of the original string up until the point found by InStrRev().

Hope this helps,

Dennis
Go to the top of the page
 
ADezii
post Jun 17 2016, 10:56 AM
Post#8



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


I agree with BruceM in that we are making this much too complicated unless I am misreading the OP's intent. Wouldn't this work?
CODE
Mid(strTestString, InStrRev(strTestString, ":\") - 1)
Go to the top of the page
 
BruceM
post Jun 17 2016, 11:00 AM
Post#9


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I'm looking forward to hearing from the OP :-)
Go to the top of the page
 
LPurvis
post Jun 17 2016, 11:25 AM
Post#10


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Well the lack of an example of the starting text as well as the desired result is certainly a stumbling block.
However the two statements:
>> This is to pull file paths out of a block of text.
and
>> then go right to the end of the string.
seem somewhat incongruous. If the file path were always at the end, then we're really just trimming off the start.

Agreed though that the previous endeavour information isn't required and more info about the current one is still yet.
(If it's not the end of the string which terminates the file path - what is it.)
We await...
Go to the top of the page
 
bakersburg9
post Jun 17 2016, 12:29 PM
Post#11



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


That's awesome! Didn't work on my machine, though - what references do I need to set up ?

It didn't like the Dim fso As FileSystemObject ,
Dim MyFile As TextStream ,
or Dim db As DAO.Database

and yes, the pipe was a typo
Steve
Go to the top of the page
 
LPurvis
post Jun 17 2016, 01:31 PM
Post#12


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

We need a bit more info still.
Can you provide an example of how the full text looks, to determine where the filepath sits within that?

Cheers
Go to the top of the page
 
bakersburg9
post Jun 17 2016, 01:57 PM
Post#13



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


Here you go: -
As you can see, I've manually extracted the paths, but that's what I'm trying to avoid - trying to take the text from about 30 documents, and extract all the paths. I did this one with T-SQL code - all the table names for my SQL tables had a syntax where if there was a table being referenced, it had two periods - the proc wrote each unique case (it ignored repeats) to the buffer, then opened up a word doc and pasted the list.

CODE
FILE LOCATIONS:  
C:\MIS Files\Daily Maintenance
C:\MIS FILES\ DAILY MAINTENANCE\PREVIOUS REPORTS
C:\MIS FILES\ DAILY MAINTENANCE\PREVIOUS REPORTS\ACCOUNTMAINTENANCE


From IE or GC, click on BIC icon (link below) in favorites – if your BIC is not working, see below
Login
Click on “Document List”
Choose “New”
Open “Desktop Intelligence Document”
Hit “Cancel”
Go to the MIS Files folder in your local drive and open the Daily Maintenance Folder:
       C:\MIS Files\Daily Maintenance
Drag the  following file into BIC:
       ACCT MAINT CHANGES(ADS DEPOSIT PRODUCTIONv2)
Click “Refresh”
Once the query is done running
File < Save As <  .txt   < Save into the “Daily Maintenance” folder
Open:
       Account Maintenance Template 1.3.xlsx
Go to the “Acct Maint Changes” tab
Click on the “Developer” tab
Click on “Macros”
Run the macro
Once the macro is done running
Go back to Account Maintenance Template 1.3.xlsx
Check all three tabs to make sure they have been updated / have data
DO NOT SAVE Close the template
Go to BOOK1.xlsx
Go to the last line of data
Delete the extra/blank lines beneath
Go to last blank line
Do SHIFT + END + ARROW DOWN
File < Save As < under “Acct Maintenance Folder” < follow naming format (for previous processing date) - C:\MIS Files\Daily Maintenance\Previous Reports
Open email template
Attach excel file you just saved
Change dates in e-mail  - should say  “Account Maintenance as of [previous processing date]”
Go to the top of the page
 
LPurvis
post Jun 17 2016, 02:01 PM
Post#14


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

So it's buried within. (And these are potentially folder paths, not individual files.)

And what consistently delimits the end of the path? A carriage return?
(It can't be a space, as there are spaces in the folder names and no file extension to aim at.)

Cheers
Go to the top of the page
 
doctor9
post Jun 17 2016, 02:20 PM
Post#15


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


bakersburg9,

I'm seeing two important things that I'd like to double-check:

1. C:\MIS FILES\ DAILY MAINTENANCE\PREVIOUS REPORTS doesn't appear anywhere on it's own within the text, except as part of :\MIS FILES\ DAILY MAINTENANCE\PREVIOUS REPORTS\ACCOUNTMAINTENANCE. Is this a mistake in the example?
2. The paths always seem to be the last thing on a line of text where they do appear. Is this consistent? Or can there be non-path text after the path?

Dennis
Go to the top of the page
 
bakersburg9
post Jun 17 2016, 03:31 PM
Post#16



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


Doc,
It is a mistake - Leigh makes a point I missed - these were created by someone else - I never put spaces in file or folder names, so if I had set them up, I wouldn't have this problem - is it possible to just find the :/ and go back one, and right 25 ? Would mean a lot of manual clean up on paths that take up less than 25 characters, but if I must, I must...
Go to the top of the page
 
doctor9
post Jun 17 2016, 03:36 PM
Post#17


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


bakersburg,

If you never use spaces, it would be possible to go from the start of the path to the next space or carriage return, but the answer to my second question is important here. Are these paths always the last thing on a line of text?

Hope this helps,

Dennis
Go to the top of the page
 
bakersburg9
post Jun 17 2016, 03:37 PM
Post#18



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


no
Go to the top of the page
 
doctor9
post Jun 17 2016, 04:03 PM
Post#19


UtterAccess Editor
Posts: 17,910
Joined: 29-March 05
From: Wisconsin


bakersburg,

Okay, so then we need to key off of a space being the marker for the end of a path. Since a path MIGHT appear as the last bit of a line of text, we can fudge things by adding a space to the end of each line of text before searching. So, let's say you have already put the entire text file's contents into a string variable in your code. Here's what you can do:

1. Set up a public string array variable.
CODE
Dim strPaths() As String  '<----This appears at the top of the code module, below any Option lines, but above any functions/subroutines.

2. Add this subroutine, which populates the array.

CODE
Public Sub ExtractPathsFromText(ByVal strInput As String)

    Dim i As Integer

'   Add a space to the end of each line of text to hopefully make searching for paths simpler
    strInput = Replace(strInput, vbCrLf, " " & vbCrLf)

'   Keep looking for more paths until there are no more
    While InStr(1, strInput, ":\") > 0

'       Increase the path counter
        i = i + 1

'       Strip off all input string's text leading up to the start of the path
'       This puts the path at the start of the input string.
        strInput = Mid(strInput, InStr(1, strInput, ":\") - 1)
        
'       Tell Access to make the array one item larger, then
'       save the path in the public array
        ReDim Preserve strPaths(i)
        strPaths(i - 1) = Left(strInput, InStr(1, strInput, " ") - 1)
        
'       Strip off this path from the start of the input string so we can look for more paths
        strInput = Mid(strInput, InStr(1, strInput, " ") + 3)
    Wend

End Sub


As long as your paths don't include spaces in them, this should work. Here's how I tested the subroutine:

CODE
Public Sub TestIt()

    Dim strText As String, i As Integer
    
    strText = "From IE or GC, click on BIC icon (link below) in favorites – if your BIC is not working, see below" & vbCrLf & _
              "Login" & vbCrLf & _
              "Click on “Document List”" & vbCrLf & _
              "Choose “New”" & vbCrLf & _
              "Open “Desktop Intelligence Document”" & vbCrLf & _
              "Hit “Cancel”" & vbCrLf & _
              "Go to the MIS Files folder in your local drive and open the Daily Maintenance Folder:" & vbCrLf & _
              "C:\MISFiles\DailyMaintenance" & vbCrLf & _
              "Drag the  following file into BIC:" & vbCrLf & _
              "ACCT MAINT CHANGES(ADS DEPOSIT PRODUCTIONv2)" & vbCrLf & _
              "Click “Refresh”" & vbCrLf & _
              "Once the query is done running" & vbCrLf & _
              "File < Save As <  .txt   < Save into the “Daily Maintenance” folder" & vbCrLf & _
              "Open:" & vbCrLf & _
              "Account Maintenance Template 1.3.xlsx" & vbCrLf & _
              "Go to the “Acct Maint Changes” tab" & vbCrLf & _
              "Click on the “Developer” tab" & vbCrLf & _
              "Click on “Macros”" & vbCrLf & _
              "Run the macro" & vbCrLf & _
              "Once the macro is done running" & vbCrLf & _
              "Go back to Account Maintenance Template 1.3.xlsx" & vbCrLf & _
              "Check all three tabs to make sure they have been updated / have data" & vbCrLf & _
              "DO NOT SAVE Close the template" & vbCrLf & _
              "Go to BOOK1.xlsx" & vbCrLf & _
              "Go to the last line of data"
     strText = strText & "Delete the extra/blank lines beneath" & vbCrLf & _
              "Go to last blank line" & vbCrLf & _
              "Do SHIFT + END + ARROW DOWN" & vbCrLf & _
              "File < Save As < under “Acct Maintenance Folder” < follow naming format (for previous processing date) - C:\MISFiles\DailyMaintenance\PreviousReports" & vbCrLf & _
              "Open email template" & vbCrLf & _
              "Attach excel file you just saved" & vbCrLf & _
              "Change dates in e-mail  - should say  “Account Maintenance as of [previous processing date]”"
                            
    Call ExtractPathsFromText(strText)
    For i = 0 To UBound(strPaths) - 1
        Debug.Print strPaths(i)
    Next i
    
End Sub


As you can see, at the very end of my test subroutine, it loops through all of the found paths and displays them in the Immediate Window.

Give this a try, and see what happens.

Dennis
Go to the top of the page
 
bakersburg9
post Jun 17 2016, 04:17 PM
Post#20



Posts: 4,952
Joined: 2-November 04
From: Downey, CA


I just got like 5 letters - no paths - maybe operator error
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:07 PM