Date/Text Change?

[habitude]

[color:blue]I have some records that are in the following format:

--- Wednesday February 23, 2000 17:11:43 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

[color:blue]They need to be changed, so they appear in this format:

02/23/00 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

The goal is to remove the 3 dashes, the weekday, and the h/m/s, and only show the date in 01/01/01 format.

I'm working in FM6. Anyone have any tips?

Thanks in advance.

[Vaughan]

Sure. Process the string word-by-word.

Get rid of the three dashes first:

Trim( Right( textfield ; Length( textfield ) - 3 ) )

Then, according to your information, the first word is the day name, the second word is the day, the third word is the month name, and the fourth word is the year. If this isn't 100% consistent then you're screwed.

The first word you can ignor. The processing for the second word will require a Case statement to associate the month name with the month number.

[habitude]

Thanks Vaughn.

The trim function worked fine.

My question about the CASE statement -

Converting the day name, day, month & year require a separate CASE statement?

Or is there a way to to say "[color:red]if this date is Tuesday April 2, 2002 13:46:02" - "[color:blue]change it to 04/02/02" and omit the day name & time?

Also, is this a calculation through a new field, or a script?

[LaRetta]

You also posted this question on Cafe' and indicated that you were working in vs. 6. Please clarify because it makes a big difference in the best solution we can offer you.

[habitude]

Hi LaRetta...

Yes, working in FM6.

[LaRetta]

Sorry, I didn't see that in your original post.

I would suggest normalizing the records. This means that you have an ActivityDate, ActivityTime, CreatedBy, Description, and SendMethod. See the picture? In this way, you will never have to restructure the textlines again and you will be able to sort, search and even use the information in relational ways in the future as needed. If this original text line is imported (you haven’t said how it comes in), then you can use auto-enter fields to break out the data properly. You can keep the original 'text' line which is imported as a reference.

But it isn't necessary so here's a quick answer for you ...

Here is an example of breaking out the date (cMonth, cDay and cYear aren't needed but I wanted to make the calculation cDate clearer to understand only for this example file. Thank God for the Let() function in future versions. Then, when you want to display ANY format, you can do so using a combined merge field (in which the date portion can be formatted with 2-digit year) and the remaining cNewLine as per attached. I'm unsure if the remaining text always begins with "By: " so I would check that carefully.

So again, the only calcs you would need is cDate and cNewLine. But I've the feeling you will find yourself manipulating this data over and over and I strongly suggest normalizing it to begin with, importing each datapiece into its own proper field.

LaRetta :wink2:

dateTest2.zip

[habitude]

Wow, thanks LaRetta, your example worked great! Only thing is the new date has to preceed the rest of the text (01/01/01 By: ).

Each of these represents a line item of a master record.

[LaRetta]

Huh? Doesn't the red have the date before the rest of the text? Again, I'm unsure why you are horsing the data in such a way. Can you provide more information for me; maybe I'm a bit slow-minded today; it's probable.

UPDATE: "each represents a new line of a master record"

Whoa. You are regrouping these records into one record on a master record? Bad boy. Please explain why you're twisting your data instead of leaving them as records and just displaying a portal. :confused:

Edited July 21, 2007 by Guest

[habitude]

I'm sorry, I didn't mean to confuse you with the red. This is the desired outcome:

> Change this <

--- Wednesday February 23, 2000 17:11:43 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

> To this <

02/23/00 By: CW P/M T/Y "CLOSING" LETTER TO CLIENT ; CERTIFIED AND REGULAR MAIL.

The latter is a complete paragraph in a single field.

(for what it's worth, this data was pulled from an old FoxPro app that became whacked when the year 2000 rolled over, thus changing the way the date was auto-entered into a record, this is a part of the normalization process task at hand)

Edited July 21, 2007 by Guest

[LaRetta]

Then use only ONE calculation (result is text) of:

Int( 1 + Position( "JanFebMarAprMayJunJulAugSepOctNovDec" , Left( MiddleWords( text , 2 , 1 ) , 3 ) , 1 , 1 ) / 3 ) &

"/" &

MiddleWords( text , 3 , 1 ) &

"/" &

Right( MiddleWords( text , 4 , 1 ) , 2 ) &

" " &

Middle( text , Position ( text , "By: " , 1 , 1 ) , Length ( text ) )

Are we getting closer?

But again, normalization means that each piece resides in its own field. In future, you may find you'll need to 'normalize' it again further. Better to do it now than again later. The date should be separate! Each piece should be separate - that's what databases represent and why they are so powerful - breaking everything to finite!

Hmmmmm, I hear Soren's accent (whatever that may be) But I'm quite serious that you'll end up working in this data again.

:wink2:

Edited July 21, 2007 by Guest

[comment]

It may be difficult to understand this without reference to a previous thread:

http://www.fmforums.com/forum/showtopic.php?tid/188646/

I have modified LaRetta's file very slightly to what I think is what you requested. But the idea to put all dates in a separate field (as true dates, not text) is well worth considering.

dateTest2.fp5.zip

Edited July 21, 2007 by Guest

[habitude]

Works Beautifully, thank you LaRetta!!!!

[LaRetta]

I believe that, by using Comment's calcs where he uses DateToText(), it will be safer than my method of placing the slash between month/day/year. Did you really need 2-digit year? Again, this is NOT proper methodology. I would use Comment's revised calculations if you can (instead of mine); I think they are safer for all OS date-settings. I dislike horsing dates into text - it is NOT good practice. And his method of pulling the remaining line will save an evaulation (or two) as well.

I again suggest doing it right and having at least the date in a separate field as true date.

Edited July 21, 2007 by Guest

[habitude]

I agree 100%, and I did make the suggestion to my bosses that making the date proper and in its own field (at least for indexing purposes) was a better way to go... I was told to just do what I was told! LOL

[habitude]

Comment, I wanted to say thanks to you too for helping me with this issue. I learned alot from it.

I have another question, which probably should be posted in the Scriptmaker forum, but here goes anyway...

Is is possible to write a script that would automatically make all the changes with a click of a button, or do I have to repeat the same process with each calculation field when this type of task comes up in the future?

[comment]

I don't understand your question.