Extract number from String

[orange]

You may get some ideas from this recent post.

Does this string adocVER2_CAD: always precede the numeric value you are searching for? Is this the only place ":" appears? If so, then you can adapt Ajax's solution to get your numerics.

[DOSRoss]

You may get some ideas from this recent post.

Does this string adocVER2_CAD: always precede the numeric value you are searching for? Is this the only place ":" appears? If so, then you can adapt Ajax's solution to get your numerics.

Reply:
No ... it doesn't. The best way to get the data is ... Find the 'ms', see if there is a digit in front of it, then start the collecting of All digits in front of it. I don't see a consistent 'rule' to 'find and move forward' to collect the data.

[Perceptus]

Can you run this query from the SQL backend directly? using substr and such?

[CJ_London]

ok

we find ms, reverse the string prior to ms, which puts the number (if any) first, Val it to confirm it is a number, convert back to a string and reverse again


strreverse(val(strreverse(left(myfield,instr(myfie ld,"ms ")-1))))

[ssanfu]

Building on orange's post #5..................

<snip> There would be many lines in the log that have "ms" (i.e. 'msg') but not a number precede it.
</snip>.

Do the lines that have "ms" and a number preceding it always have "elapsed" after the "ms"?? Maybe need to look for "ms elapsed" instead of only "ms".


I would use VBA to parse the text file (log) to another text file, then import the NEW text file. In this case, VBA can do a faster, better job parsing than trying to do everything in a query. (IMHO)

VBA can also read a line, check for "elapsed", if not in string, move to next line....

My whole objective is to get the "Line Number" which will is separated when the txt file is imported and the "ms" number.

Didn't see a line number is the provided example string (Post #1), but using VBA would be able to parse that also.....


My $0.02...........

[orange]

I think it would be helpful if DOSRoss showed a few more records so we can see exactly what he is facing.

[DOSRoss]

I think it would be helpful if DOSRoss showed a few more records so we can see exactly what he is facing.

The actual record would have ‘ID’ (line number), ‘DATE’, ‘Command’, ‘Time’. The examples below only show the 'Command' portion of the record.
I would like to find the records that had numbers (digits) before (preceding) the ‘ms’ and extract the number, then put that number in that records ‘Time’ field. Then I could sort on the ‘Time’ field and get all the reporting that I need.

Examples of lines that the time Would be collected …
adocExecuteComplete| SQL Request exceeded reasonable time, adocVER2_CAD: 6627ms elapsed, type cmdText, status esOK, cursor ctUnspecified, lock ltReadOnly, records 1
[GUI] TSpeedButton.Click(frmBrowActiveCalls.Panel3.btSpP ending)| Exiting: Elapsed time 609ms
TfrmAvailableUnits.ThreadedRefresh| Disconnect Query: 11ms
TfrmAvailableUnits.ThreadedRefresh| adsUnits Open: 14322ms
ThreadedGlobalRefresh| Available Units Refresh: 280ms
TfrmAvailableUnits.ThreadedRefresh| Refresh Clone[2]: 35ms
TfrmBrowActiveCalls.ConnectQuery| Refresh Active Calls with 8 records and 8 grid rows: 14ms
TfrmBrowActiveCalls.ConnectQuery| Refresh Edit Call: 1ms


Examples of lines that would be ‘passed by’ the code …
TFrmWatchDog.HandleKeyMessage| [GUI] Mouse Button: msg - hwnd = x
TFrmWatchDog.ActiveFormChange| [GUI] Setting the active form to frmAvailableUnits_1($6281900):Clone EMS, FIR, RES Units Assigned
Show_SYS| Check EMS Situation
TfrmAvailableUnits.ThreadedRefresh| Entering for window Clone EMS, FIR, RES Units Assigned
TDM.RMSSubjAlertsOnCall| Background thread is reading Alerts Cache object. Existing cache is being used for 15024709.
adsActiveCallsAfterScroll| Call (15024709) Call_Status (A) categories () disp_zone (7670) esn (524) internal_esn (2700) actual_incid_city (MCMINNVILLE) run_zone_fire (101I) run_zone_ems (MEMS2) tract (322R)

[CJ_London]

what does 'passed' mean? I've revise my code having now seen some other examples

Time: iif(instr([myfield],"ms")=0,0,val(strreverse(val(strreverse(left([myfield],iif(instr([myfield],"ms")=0,1,instr([myfield],"ms")-2)))))))

this will return 0 if there is no 'ms' in the string or if there is it is not preceded with a number

[CJ_London]

one other thing - Date and Time are reserved words - using them can result in unexpected results

[DOSRoss]

one other thing - Date and Time are reserved words - using them can result in unexpected results

Everything is actually in TEXT except the ID - which is an Access generated number equaling the 'Line Number' of the Log that is being imported.
So I haven't even begun to work with the Date issue ... only the digits representing a time value.

[ssanfu]

From the data examples in Post #22, I created a text file.
Then I created a Parse example dB.
This demo extracts the Elapsed time from the text file.

Both files are in the attached Zip.

The OP also wants to get the line number and the date/time. These could be easily added to the parse code.......


Good luck with your project......

[DOSRoss]

From the data examples in Post #22, I created a text file.
Then I created a Parse example dB.
This demo extracts the Elapsed time from the text file.

Both files are in the attached Zip.

The OP also wants to get the line number and the date/time. These could be easily added to the parse code.......


Good luck with your project......

Thank you very Much.
I have learned a great deal from all the input of this Forum.
I very much appreciate all the time each of you have invested.

Thank you,

Ross