[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Search for Time in a PDF

Hi - Hopefully Craisin will pick this up since it is Craisin who provided the solution to the related question.

I have another time format that I ran across yesterday.  I tried modifying the FindTime code to look for these new times, but I haven't been able to figure it out.

Would you mind telling me what I need to do to modify the code to look for these two new formats?

##:##:##AM
##:##:##PM
 (sometimes there is a space before the AM or PM)
0
rsburge
Asked:
rsburge
  • 17
  • 14
1 Solution
 
Chris Raisin(Retired Analyst/Programmer)Commented:
OK, I am looking into this one too for you as you requested in another question.

(By the way, what is your name?)

Cheers
Chris
(Melbourne, Australia)
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Amend the declaration line for the times in "Findtime" to:

           Dim cTimes(16) As String

then add the follwowing assignations at the appropriate place in the code
(below the already assigned values):

            cTimes(13) = "##:##:##AM"
            cTimes(14) = "##:##:##PM"
            cTimes(15) = "##:##:## AM"
            cTimes(16) = "##:##:## PM"

I do not have any PDF's (I think) with taht format in them, so I will leave it up to you to test whether the code still works for times in those formats.

I await your discoveries :-)

Cheers
Chris
0
 
rsburgeAuthor Commented:
Hi - My name is Renee.  :)

I will try those changes again and see if I can make it work.  I might have just mistyped something.

Thanks!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rsburgeAuthor Commented:
At this line...  It has a "Subscript out of range" error.

            If Len(cToken(nToken + 1)) > 0 Then
 FPF-Denver.pdf
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
I have amended the code to get around this problem.

Pleas try out the new code.

All I did was add a test:
      If nToken < uBound(cToken) then
          ........
          ........
      End If


     


Option Explicit

Public Function FindTime(nHandle As Integer, cLine As String, _
                         bAMPM As Boolean) As String
  Dim cRet       As String
  Dim cTimes(16) As String  'make sure this size meets the number
                            'of assignments made below
  Dim cToken()   As String
  Dim bFound     As Boolean
  Dim nToken     As Integer
  Dim y          As Integer

  'The various formats for time
  cTimes(1) = "#:##"
  cTimes(2) = "#.##"  'if followed by AM or PM this is a time, else 
                      'normal decimal
  cTimes(3) = "##:##"
  cTimes(4) = "##.##" 'if followed by AM or PM this is a time, else 
                      'normal decimal
  cTimes(5) = "#:##AM"
  cTimes(6) = "#.##AM"
  cTimes(7) = "##:##AM"
  cTimes(8) = "##.##AM"
  cTimes(9) = "#:##PM"
  cTimes(10) = "#.##PM"
  cTimes(11) = "##:##PM"
  cTimes(12) = "##.##PM"
  cTimes(13) = "##:##:##AM"
  cTimes(14) = "##.##:##PM"
  cTimes(15) = "##:##:## AM"
  cTimes(16) = "##.##:## PM"

  Do While Not EOF(nHandle)
    cToken() = Split(Trim(cLine), " ")
    For nToken = 0 To UBound(cToken)
      For y = 1 To UBound(cTimes)
        If UCase$(cToken(nToken)) Like cTimes(y) Then
          If InStr(UCase(cToken(nToken)), "AM") = 0 And _
             InStr(UCase(cToken(nToken)), "PM") = 0 Then
            If nToken < UBound(cToken) Then
              If Len(cToken(nToken + 1)) > 0 Then
                If InStr("AM PM", UCase(cToken(nToken + 1))) > 0 Then
                  If bAMPM Then
                    FindTime = cToken(nToken) + " " + _
                               cToken(nToken + 1)
                  Else
                    If UCase$(cToken(nToken + 1)) = "PM" Then
                      FindTime = Left$(cToken(nToken), _
                                       InStr(cToken(nToken), ":") - 1)
                      If Val(FindTime) < 12 Then
                        FindTime = CStr(Val(FindTime) + 12) + _
                                   Mid$(cToken(nToken), _
                                   InStr(cToken(nToken), ":"))
                      Else
                        FindTime = cToken(nToken)
                      End If
                    Else
                      FindTime = cToken(nToken)
                    End If
                  End If
                  Exit Do
                End If
              End If
            End If
          Else
            If InStr(cToken(nToken), ":") > 0 Then
              FindTime = Left$(cToken(nToken), _
                         InStr(cToken(nToken), ":") - 1)
              If UCase(Right$(cToken(nToken), 2)) = "PM" Then
                If Val(FindTime) < 12 Then
                  FindTime = CStr(Val(FindTime) + 12) + _
                             Mid$(cToken(nToken), _
                             InStr(cToken(nToken), ":"), 3)
                Else
                  FindTime = FindTime + Mid$(cToken(nToken), _
                             InStr(cToken(nToken), ":"), 3)
                End If
              Else
                FindTime = Left(cToken(nToken), _
                           Len(cToken(nToken)) - 2)
              End If
              Exit Do
            Else
              FindTime = Left$(cToken(nToken), _
                         InStr(cToken(nToken), ".") - 1)
              If UCase(Right$(cToken(nToken), 2)) = "PM" Then
                FindTime = CStr(Val(FindTime) + 12) + _
                           Mid$(cToken(nToken), _
                           InStr(cToken(nToken), "."), 3)
              Else
                FindTime = Left(cToken(nToken), _
                                Len(cToken(nToken)) - 2)
              End If
              Exit Do
            End If
          End If
        End If
      Next y
    Next nToken
    Line Input #nHandle, cLine
  Loop
End Function

Open in new window

0
 
rsburgeAuthor Commented:
Thank you!  I have been testing this today and I keep getting a strange symbol at cLine and FindTime keeps returning as blank.  I am going to re-copy the code in and try again.  Maybe I did something strange when I copied the code into the database.
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Please clarigy "Strange symbol" (can you do screen capture?)

What documents are you running it against (can you supply copy if not already done so) and what date are you searching for?

Cheers
Chris
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Oops!

 "Clarify"...... not "clarigy"       :-)
0
 
rsburgeAuthor Commented:
I think this may have been user error :)

I am going to test again tomorrow and see if I can replicate the error.  I did not run into the error today, but I didn't have time to test all of the same files.
0
 
rsburgeAuthor Commented:
Hi - I have been testing running the code one cLine at a time and while I can definately see the time in the very first cLine of the .txt file, FindTime is returning as blank.

I am also still getting the error where any line with cToken(nToken) is showing as <Subscript out of range>.  I did double check to see that I copied in the code you posted above.

Attached is one of the PDFs I am having trouble with as well as the .txt file that the code produced. FPF-Phoenix.pdf  PDF2Text.txt
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
OK...thx....I will test this once I get back from church.

Cheers
Chris
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Just a quick thought:

The four patterns for times you just added are:
     cTimes(13) = "##:##:##AM"
     cTimes(14) = "##.##:##PM"
     cTimes(15) = "##:##:## AM"
     cTimes(16) = "##.##:## PM"

but the time showing in your file  is      7:30:00 AM

The symbol "#' in the pattern says that any single digit must be present.
How about changing the patterns to:

        cTimes(13) = "*#:##:##AM"
        cTimes(14) = "*#.##:##PM"
        cTimes(15) = "*#:##:## AM"
        cTimes(16) = "*#.##:## PM"

The character "*" says either ZERO or more characters.

Another solution is to add the additional pattersn:

        cTimes(17) = "#:##:##AM"
        cTimes(18) = "#.##:##PM"
        cTimes(19) = "#:##:## AM"
        cTimes(20) = "#.##:## PM"

I think tha latter is the best option.

When I get back from Mass I will test it out.

Cheers
Chris




0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Well, the formats for the times were not fully defined, so I have refined them all as you will see in the code.

I have also limited the search in the Excel files to "A1" to "GA300" as you will see in the code ""FindTimeInExcel" to limit the search. I wrte a special function called "ColAddress" to return the column name (e.g. "AB") since I could not find any other way of doing it.

The function is the following:

   Public Function ColAddress(oCol As Object) 'a worksheet column
       ColAddress = Split(oCol.Address, "$")(1)
   End Function

The testing module test for the date "TestFindingDateTime" uses the following dates:

   "21/10/2011"  for PDFs
   "10/14/2011"  for Excel files

with results showing in message image below.

Results of searching files


The code is changed quite a bit in parts, so you had better just replace your existing code with the ones in the zip file.

There is still a little bit more work to be done to catch times coming BEFORE dates as detailed in your other question on finding times in question 27398935.

Please check out everything now and see if it works for you for the new time formats you detailed.

To fully answer this question by statement only, you must expand the dimension of the array cTimes() in the modules "FindTimeInExcel" and "FindTimeInPDF" to accomodate any new time "formats" you add, then simply add the definitions to the existing ones listed in the modules.

The current definitions (which include the ones you asked for in your question) are coded thus:

  Dim cTimes(24) as String

  cTimes(1) = "#:##"
  cTimes(2) = "#.##"
  cTimes(3) = "##:##"
  cTimes(4) = "##.##"
  cTimes(5) = "#:##:##"
  cTimes(6) = "#.##.##"
  cTimes(7) = "##:##:##"
  cTimes(8) = "##.##.##"
  cTimes(9) = "#:##AM"
  cTimes(10) = "#.##AM"
  cTimes(11) = "#:##PM"
  cTimes(12) = "#.##PM"
  cTimes(13) = "##:##AM"
  cTimes(14) = "##.##AM"
  cTimes(15) = "##:##PM"
  cTimes(16) = "##.##PM"
  cTimes(17) = "#:##:##AM"
  cTimes(18) = "#.##.##AM"
  cTimes(19) = "#:##:##PM"
  cTimes(20) = "#.##.##PM"
  cTimes(21) = "##:##:##AM"
  cTimes(22) = "##.##.##AM"
  cTimes(23) = "##:##:##PM"
  cTimes(24) = "##.##.##PM"

Note that times folloed by a SPACE plus AM/PM are handled by the code.
If a space plus "AM" or "PM" appears in the  PDF or Excel file when the time has been found, the code senses it and then adjusts the time to 24 hour format if "PM" is showing, otherwise it returns the time as found. If you want the time returned as "AM/PM" it would simply then ADD the AM/PM to the time found.

One thing it may NOT do correctly at the moment is return a time in AM/PM format if the time found is in 24 hour format. If you want this as an option please let me know.

I will now go back to the other question using the new code supplied here to see if we can tweak the code a bit more to satisfy that other question.

Cheers
Chris

TestFindinDateTimeInPDFAndExcel.zip  
0
 
rsburgeAuthor Commented:
Thank you so much for all of this!  I will test it as soon as I get back home.
0
 
rsburgeAuthor Commented:
This seems to be working very well.  I am just going to test again tomorrow with live data.  I will post back sometime tomorrow.  Thanks again for all of your help!
0
 
rsburgeAuthor Commented:
Thank you so much for all of this code you have provided!  
I have thoroughly tested this with my existing PDF's and most of the Excel workbooks and it is working great!

Should I go ahead and close this question or keep it open pending the other changes you mention you would like to do with regards to the time being before the date?

Thanks,
Renee
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Please keep this question open for the time being while I work on that slight enhancement.

I would prefer that this question to be closed AFTER the start of next month so that any points I earned go towards next months tally.

I have to earn 4000 a month to retain my membership, and this month I have already earned 8000. Unfortunately any excesses do not "carry over" and we start from scratch at the start of each month.

That also then gives me a chance to work on the "time before date" scenario.
Standy by for that....

Glad it is all working.

Cheers
Chris
0
 
rsburgeAuthor Commented:
No worries.  I will keep this question open for that last enhancement.  Have a super day!

Renee
0
 
rsburgeAuthor Commented:
Hi Chris - I have another open question that I haven't had much of a response on and I would like to give you the points if you would like to take a look at the question after the 1st.  It is http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27411478.html.

I didn't know if there was any other way to contact you on EE other than through my open question...  If there is a better way, please let me know.

Thanks!
Renee
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Renee,

I have started work on your new question. :-)
and I have added your name as a "filter" so that if you ask any new questions,
I will notice.

You cannot really advise me externally to EE since that is against policy. Every Expert should have a chance of answering your questions, and there may be some I cannot help you with (or I may be away on holidays or something like that).

So rest assured, just ask a new question whenever it is needed and I will try to help where I can.

In the meantime, I am still looking at the enhancement to THIS question and will come up with the amended code next week I hope.

Stand by....

Cheers
Chris
0
 
rsburgeAuthor Commented:
Thanks so much!  I really appreciate all of the help you and all of the experts on EE have given me over the years.  :)
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
I am just about to have a look at this question to see if the enhancement is possible.

Still recovering from my Suisters death, but coming to grips with it now and feeling a lot more comfortable with returning to the keyboard.

Stand By.....
Chris
0
 
rsburgeAuthor Commented:
Thank you so much!  I am glad to hear you are feeling a bit better.

0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Renee,

I only have a couple of days before I go away for three weeks visiting my Mum in Queensland for the Christmas period.

I have sort of lost track of things in your questions.

Can you please remind me where we stand with THIS question and what further work is required here?

Many thanks
Chris
0
 
rsburgeAuthor Commented:
I apologize for not responding sooner.  My daughter has been having a lot of complications with her pregnancy.  I have been staying with her so I have been away from the office.

I will get back on this as soon as I get back in the office sometime in the next week or so.

I hope you had a wonderful time with your Mum.

Thanks!
Renee
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Yes, I just arrived back from Toowoomba (after driving up there on December 21st)...distance is approx. 2300 kilometres each way, and I drove yup and back by myself.....fairly tired, and will be even more so after wading through 632 emails in my email In-box!

Looking forward to your findings...

Cheers
Chris
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Renee,

It is getting close to the end of the month and I need 2000 points to retain membership.

Is it possible to award the points for this particular question and ask for the enhancements in a seperate question? That way I will retain my membership and will do the enhancements once you clearly define them again for me (helps me get my mind back on track).

When creating the new question, mention it it further development (an enhancement on the existing solution) on Question 27400993 and mention my username asking for further help. That way I will easily find the question and can get to work on it.

Kindest ragards, and I hope your daughter is well (and her new son/daughter if the event has alreaqdy happened).....

Cheers
Chris
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
No hurry now, Renee!

I have achieved the required points, so any points for this question would be great if they went into NEXT mnonth (February).

I still think you should look at closing this question in February  (in say 7 days time)
and set up a new question for the enhancements if still required.

Kindest regards
Chris
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Thanks for the points, Renee.

Have you set up a new question for enhancements (I've forgotten what they were) or is everything OK now with this particular problem and no enhancements needed?

Cheers
Chris
0
 
Chris Raisin(Retired Analyst/Programmer)Commented:
Any response Renee?
0
 
rsburgeAuthor Commented:
Hi - Everything is working on this one right now.  I will post a follow-up if any enhancements are needed.

Thank you again for all of your help on my questions.  I really appreciate it.

Have a great day!
Renee
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 17
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now