text parsing script?

[skearton]

Hi All,

I have an old database for products that has the size measurement in a single field. I need to be able to parse out the numbers and units of measure and bring that data into a new database that has four separate fields, Height, Width, Hunit & Wunit (i.e. inches, mm, cm, foot).

Of course the old size field has quite a variation of entry formats: i.e. 1-1/2" x 6", 1 1/2 x 6", 1.5" x 6", 38.1 x 157 mm, 25 Module (797 mm height), 4 x 8 ft...

As you can see I'm also dealing with decimals and fractions as well just to make things interesting. Anybody out there have a script or custom function that could do this? Thanks for all the help again!

[LaRetta]

Hi Skearton,

It appears that there is always a comma between your entries. And I must assume your entries are always in the same order sequence, first height, second width etc. If we can also assume no entry is blank (unless it also has a comma, designed as ,,) then you can use (for the first entry)

GetValue ( Trim ( Substitute ( textField ; "," ; ¶ ) ) ; [color:blue]1 )

Just change the blue number for the second field and so on.

Edited January 10, 2010 by Guest

[skearton]

Thanks LaRetta,

I may have not been clear in my request...the examples I showed that are separated by a comma are actually individual entries in the field. So a single entry could be:

1 1/2 x 6"

or 1-1/2" x 6"... etc.

You are correct in assuming that the entries were base on H x W.

[LaRetta]

It could be done; I can't find a program or calculation already built which would handle the various combinations you require. What are the consistent results you want to achieve and how accurate, since there will be quite a difference (0.0393700787 mm = 1 inch).

But I do not understand this: 25 Module (797 mm height). Is 25 Module the width? Or do you mean Module 25 autocad? And I would need to receive examples of every possible text combination (if you haven't already provided it).

If others haven't helped you solve it, I'll give it a go later.

[LaRetta]

Wait ... is all you want is to put the pieces into individual fields? You don't need the amounts added or otherwise manipulated or to be the same consistent units of measurement?

So with: 1 1/2 x 6"

you want 1 1/2 in Height, inch in Hunit, 6 in Width and inch in Wunit? We still need more information. Do you want the dashes removed so the second example matches the first? And what about the 25 Module (I still don't understand that), I have no 'x' to separate the H & W.

Edited January 11, 2010 by Guest

[skearton]

Hi LaRette thanks for the help.

I think if I can get the majority of the info transferred that would be great as I realize that the oddball stuff like 25 module, I will have to cut and paste into the field.

You are correct that I want the first number in the height field, the "in" (or mm) in the Hunit field and likewise for the width using there appropriate fields.

[LaRetta]

Here's something to play with. Note that I cheated and put the Wunit same as the Hunit. If you have a mix in this string, ie, 1 1/2" x 4 feet, then you'll have to break the calculations for the two units apart. I just ran out of time (and got a bit lazy because I didn't want to test if either height or width was empty - had no unit).

And you will need to find and omit (or individually deal with) the Module ones.

If you have a mix of inches in one dimension and feet in another, let me know and I'll take it further, if you get stuck. I would suggest two things: 1) create calculations as I did and check your data before setting the new fields and 2) back up before setting those fields.

Check your data calcs carefully for the proper results. It doesn't take much in something like this to throw the parsing off.

Edited January 12, 2010 by Guest

[LaRetta]

I don't want to hold up your need for this information so here it is (and I will still fix the attachment as soon as possible). What I suggest you do is:

Create a text field (global) called gUnits. In it, put:

"

in

inch

inches

mm

cm

ft

feet

foot

... or any other possible units you may have. OrigMeasure is your original text field holding your text values. Then create the following calculations, all text:

cHeight = Trim ( Filter ( GetValue ( Substitute ( OrigMeasure ; "x" ; ¶ ) ; 1 ) ; " .0123456789-/" ) )

cWidth = Trim ( Filter ( GetValue ( Substitute ( OrigMeasure ; "x" ; ¶ ) ; 2 ) ; " .0123456789-/" ) )

cHunit = Filter ( GetValue ( Substitute ( OrigMeasure ; "x" ; ¶ ) ; 2 ) ; gUnits )

cWunit = cHunit

You can then use these calculations to set your REAL height and width text fields when ready. As I indicated, I didn't deal with issue that height may have unit and width may not (in which case you should test and grab the unit from Height and so on. Let's see how you go with this first. :smile2:

[skearton]

Thanks LaRetta! That is exactly what I wanted!

But now I have another question (and I can't think anymore because I'm getting a migraine) but how do I take the text from the calculated fields and place them in the final fields so that it is editable for future records?

Edited January 12, 2010 by Guest

[LaRetta]

Create a field called Height (text). Find all records except your Module records. You can do this by searching for word Module in the originalText field. Then Show Omitted records.

Place your cursor in the Height field and select from FM menu, Records > Replace Field Contents. Click the radio button "Replace with Calculated Result."

A calculation dialog will open. Paste your calculation from the cHeight field and say OK then REPLACE to the next dialog. Be sure to BACK UP FIRST and do not run this if being served and other User might be in those records. Repeat with each of your other three fields.

UPDATE: I would leave that originalText field there. The calculations can actually be removed if you plan to script this because you can add the Replace Field Contents script step which would hold each calculation. But I would prefer to always see what the value displays before changing it. You can even give User button called Split which takes the OriginalText and splits it into each field manually.

There is even possibility that you can add an Auto-Enter (Replace) right on the new Height field (you will have to fix all prior data first). I really don't suggest this method because if a User types something totally bogus, they could scramble the prior data. If you choose this method, allow ability to revert back to OriginalText and re-split it. Only you can decide how this data should be manipulated and handled by Users.

Edited January 14, 2010 by Guest
Added Update

[skearton]

I'm now using this calculation in another database and I need to also catch some other situations but can't figure out how to adjust it to do this.

The typical entry by a user looks like: 6 x 6" and the calculation is able to extract the height and width out into separate fields.

The problem now is sometimes a user may input it as 6 X 6" and that capital "X" instead of lowercase "x" screws up the calculation.

Also, sometimes the user is entering a diameter measurement, like 6" diam. (or 6" dia.) and I need to capture for that as well.

How would you adjust the calculation to factor in these options?

[LaRetta]

The real answer is to have the data properly input to begin with.  Otherwise, I need you to provide me with one each of every combination which the field may contain and also what the result should be when parsed into individual fields.  I cannot tell from this old thread exactly what you are currently doing.

But again, the answer is to use field-level validation and/or script triggers to assist the user into proper data entry.  What do you WANT the results to be?  I believe we should work on that instead of correcting garbage entries.  :-)

Added ... if you use a free-form field such as this, you will never be able to depend that the data does not contain other bogus data and your parsing will fail, as it is now doing.  

Edited April 13, 2015 by LaRetta