text parsing

[saralee]

Hi everyone,

I need to separate a text field with chunks of data into different fields.. for example-

3/4/2008 (location info) (actual comment text) (initial)

In the future, the date info will go to a date field, location info will go into its own text field, actual comment text will go into another text field, and so does the initial field. Anyone who has ideas about doing parsing charater by character?

Date field would always have two slashes, location field is always within parentheses, comment is just some plain text, and the initial is always within parentheses at the end.

Thank you so much! :)

[LaRetta]

Hi saralee,

Well, you didn't provide any examples but see if the attached gets you going. You would, after backing up, use either Replace Field Contents[] using the calculations (one at a time on each new field) or a looping script.

UPDATE: Oh, the comment portion would depend upon where it falls. I can determine that once I know if it is before or after location or before or after date.

StringsREV.zip

Edited June 14, 2010 by Guest

[LaRetta]

I realized that the note portion should be what is remaining after you substituted out everything else. And it should work regardless of where the note occurs in your string (but I can't guarantee it without seeing example of the actual data.

I've attached a new file (above) showing the calc for the note itself.

UPDATE: My apology ... you DID have an example. My file appears to work no matter how the data in the field is arranged but you can exchange the original c_note calculation for:

Let ( [

note_start = Position ( text ; ")" ; 1 ; 1 ) +1 ;

note_end = Position ( text ; "(" ; 1 ; 2 )

] ;

Trim ( Middle ( text ; note_start ; note_end - note_start ) )

)

Edited June 14, 2010 by Guest

[saralee]

Hi LaRetta,

Thank you so much for your help! I didn't see the scripts available in your attachment. Could you check it? Sorry & thanks!

Also, in your newly provided script, does "Let" function simply does multiple SetVariable functions altogether?

I also would like to ask about how to deal with global variables. The example I gave was an ideal case, and sometimes I need to extract some "extra" non-standard stuff and move it to another field in the same layout. Since my script is getting so complicated already, I would like to write another scrip to just deal with that, but would need to store the content somewhere after extracting it in the current script, so that would mean I would need global variables? Please advice:)

Thanks a lot!

[LaRetta]

I provided the parsing pieces via calculation so it would be easy to understand and verify that it would handle your situation. As I suggested, you would just use a script to set your new fields with those calculations (with a looping script or Replace Field Contents[]). But I've attached a new file with the script in it.

The Let() function allows you to specify variables within the context of a calculation without having to set additional script variables.

I would hate to advise on an issue that I can't see but you, you can use global variables to hold values. You can even use ONE global variable to hold multiple values with a separator between each (such as the pipe character). Then you can use the parsing techniques similar to the calcs in my file to pull the data back out.

Let() is one of the most powerful functions we have in our toolkit. You can use it inside a script (as I've done) to 'work through' the logic, similar to:

Let (

total = 3 * 6

;

total - 4

)

You can even use Let() inside of Let() ... but I must stop or I'll keep going for hours about how exciting it can be.

UPDATE: When parsing data, it is always best to create calculations and thoroughly review your results before running your scripts. It is amazing how you can find exceptions to what you think are standard strings. In truth, I preferred my first calculation for Note because it protects from those exceptions a bit better.

StringsREV2.zip

Edited June 15, 2010 by Guest

[bruceR]

An alternate technique using a variable and getValue().

StringParse.fp7.zip

Edited June 15, 2010 by Guest

[LaRetta]

Yes, this is of course a better process. When I wrote mine, I had no idea of the sequence of the four pieces (I missed it when I read the post). My original process would work no matter the order (as long as the initials were last). Thanks for providing the alternative.

Edited June 15, 2010 by Guest

[bruceR]

Well together I hope we've helped her learn a couple of useful things she didn't know before.

[saralee]

Hi BruceR, LaRetta,

Thank you very much for your kind help! I'm learning some very useful things here

OK, let me summarize a bit- so it's better to write calc functions first while testing the algorithms, and then the script will just be a combination of the functions, correct? Seems like that to me...

Thanks for mentioning about the Let function. I noticed that you don't have "$" when you define temporary variables in your file, and $ is for local variable for the current script, while $$ is for global variables for the current file. Is this correct?

I also would like to ask if there is any ways to detect a chunk of numbers/digits in a string.. I have cases where the dates are not in the order as they should be, and the connectors witin the dates is kinda random as well (/ or - or .) So just wonder if there is anyway to sorta scan through the string and figure out the position of the digits/numbers.

Thanks a lot again!

[LaRetta]

Hi saralee,

... so it's better to write calc functions first while testing the algorithms, and then the script will just be a combination of the functions, correct?

Well no. When I am parsing a lot of data, I prefer to make calculations to view the results. When scrolling through thousands of records, I can then spot any anomalies that I hadn't accounted for. The calculations can be deleted once you've created your script and move them there.

Thanks for mentioning about the Let function. I noticed that you don't have "$" when you define temporary variables in your file, and $ is for local variable for the current script, while $$ is for global variables for the current file. Is this correct?

Normally, you wouldn't want to use script or global variables within calculations (but there are advanced exceptions). You will also want to be careful not to use a regular field name as a Let() variable. If you do and you change your field name, you can break your calculation.

[saralee]

Hi LaRetta,

Thank you for your reply. I didn't quite get your last part yet. So when you don't have $ for the variables you defined in Let(), are those regular fields or just some temporary variables.

When would you use $ and $$ then? If I need to define some global variables, should I use $$?

Sorry about my slowness, hehe

Also, do you know if I can scan the whole string and extract the number's part? As written in my above post.. thanks a lot!

[LaRetta]

So when you don't have $ for the variables you defined in Let(), are those regular fields or just some temporary variables.

They are temporary placeholders within your calculation. You name your variables (without using $ or $$) up in the [color:green]Let() portion and then your calculation refers to them in the [color:blue]evaluation portion, like this:

[color:green]Let (d = Date ( Month ( Get ( CurrentDate ) ) ; 15 ; Year ( Get ( CurrentDate ) ) ;

[color:blue]Case ( d > dueDate ; "OVERDUE" )

[color:green])

Maybe others can add clarity on the explanation as well ...

When would you use $ and $$ then? If I need to define some global variables, should I use $$?

You use $ (called script variable) within scripts while the individual script is running. They are individual to the current User and they 'die' when the script stops running. You use $$ (global variables) if you need the value to persist after a script ends. You can also pass a $ variable to another script using the script's script parameter. But global variables persist (only in the current file; only for the current user) for as long as the User is logged in.

Sorry about my slowness, hehe

You are NOT and nonetheless, never apologize when learning. We all learn at different levels and then sprint ahead of those who might have appeared to be learning at a faster level at first. You maintain high curiosity and that is PARAMOUNT and you are not afraid to ask regardless of how it might appear and that is to admired.

Also, do you know if I can scan the whole string and extract the number's part? As written in my above post.. thanks a lot!

Uh, yeah, I had to take a break so couldn't do what I wanted which is to question. What do you plan to do with these dates once you isolate them? And can there be multiple dates within the string?

Anyway, I completed a file which shows how you can use script (and script variables) to pull dates out of text strings. However, it is not perfect and with many variations (and considering OS changes), it won't catch them all (file attached). BTW, if you explain more what you want to do with this information, we can tell you exactly the position of a date. I decided in this file to wrap 'valid dates' with bracket for ease of identifying them but the decided to also place them into date fields.

You will see in this file how how I used $ (script variables) to hold information while I worked on it. And I also (in the Perform Script: Save Dates, passed the $test variable from the first script to the second in its parameter).

UPDATE: I am surprised that the 2/29/2005 (when wrapped with GetAsDate() did not produce valid. FM should change it to see it as 3/1/2005 before the IsValid() test, I would think.

DateStrings.zip

Edited June 15, 2010 by Guest
Changed phrase

[saralee]

Thank you so much for your help and encouragement, LaRetta!! I really appreciate it

I think I have a better understanding of the different variables. I will take a look at your script in details tomorrow. Many thanks!!

So for the date part, eventually I want to create a calendar drop down field to store the future dates to be entered. As for the existing date info, I'm extracting them out of a text field with data in inconsistent formats and put them in a separate text field just for date info.

Since the current text field is quite messy, I can't find any fixed pattern (eg always in the parenthesis at the end, or always at the beginning etc) in terms of the position of the date part, I thought it would be easy to just find the number/digit part in the string...

I hope this explains more about what I'm about to do..

Thanks again!

[saralee]

Hi LaRetta. I found out that the function GetAsDate() does not work for my date info, because my date's formate goes mostly like 05/2004. Is there a way to recognize that format?

Thanks!

[Vaughan]

I found out that the function GetAsDate() does not work for my date info, because my date's formate goes mostly like 05/2004. Is there a way to recognize that format?

The date needs to be complete, ie, have a month, day and year and also be a valid date.

If the data is entered consistently, you can parse out the months and years and build a date from it. If there is no day value then you'll have to invent one.

A function to parse the values and build a valid date (assuming the first of the month for the day) could go something like this for a value that contains month and year:

Let(

[

m = LeftWords( date ; 1 ) ;

d = 1 ;

y = RightWords( date ; 1 )

] ;

Date( m ; d ; y )

)

If the values are mixed (mm/yy and mm/dd/yy) then you'll need to count the number of words and process those with 2 words as above, and those with 3 with a function that pulls the day value out.

Change the function above to be:

d = Case( WordCount( date ) = 2 ; 1 ; MiddleWords( date ; 2 ; 1 ) ) ;

[saralee]

This is great! Thank you, Vaughan :