Split out field - 27-Sep-2004 05:36:03 PM PDT

[Bekah]

Hi,

I get download regularly of text field like example in subject. I can get the time with GetAsTime(MiddleWords ( text ; 4 ; 2 )) and even grab the time zone with RightWords(text; 1) but Im having trouble on the date.

They have the months as 3 letters and I don't want to hard-code this unless I have to, ie, If(MiddleWords(text; 2) = "Sep"; September; If(MiddleWords(text; 2) = "Oct"; October) ... etc. through 12 months. Having trouble foruma like - if it equals first three letters of MonthName ...

I think i can get it, if I can figure the month part because day is LeftWords(text; 1) and Year is MiddleWords(text; 3; 1). But I'm also unsure if I'll have to insert the dashes and i think it wise to ask for help.

I have a real date field (TransDate) ready for date; TransTime ready for time; and TimeZone ready for PST etc. I figure Ill need to use this script every time I import and loop through them setting the new fields, right? It'd be cool if auto-enter would just set the new fields but I haven't gotten far enough to try that yet.

Ideas on best way to handle this please?

[RalphL]

I would make a special data base to do this. I would have calculation fields to parse the text field into other fields.

Add a global text field gMonths = "JanFebMarAprMayJunJulAugSepOctNovDec"

Lets call MonthName = MiddleWords(text; 2)

Month = (Position (gMonths; MonthName) + 2) / 3

Use Date & Tme functions to create the date & time fields.

This might be a good place to use the Let function.

You will have to add 12 hours if time is PM.

[Bekah]

Thank you Ralph. I'll try this. I don't need to add to the time - my results are correct combining words 4 & 5. It converts to correct time. And im unsure why i need another file(?) This imports into table - adds to existing transactions (online). Only this one text field needs split. I thought it better to leave my import mapping the same. Seems worth it to just have one extra text field compared to another file (and then still having to import into Transactions again??). I'm new so unsure.

I will try your Month conversion using global. I had already tried Let like this: Let( ["Sept"; 9 ... etc to try to convert 3-letter month to month number, thinking it would be easier but it wouldn't take it. I guess i'm still unsure how to put together what you gave me. I don't want (or need) a calc for each piece, do i? I'd rather script it than have unstored calcs, right?

[RalphL]

I like using a separate file to protect the main file from an import error. When you tranfer the data from the special file it can be stored. If you use the Let function you can define the global field in it and no longer need a global field.

I think you need to use the Subsitute function in your Let function.

[RalphL]

Here is a calculation for the date using the Let function:

Let ( [ Months = "JanFebMarAprMayJunJulAugSepOctNovDec" ;

D = LeftWords ( Text ; 1 ) ;

MName = MiddleWords ( Text ; 2 ; 1 ) ;

M = ( Position ( Months ; MName ; 1 ; 1 ) + 2 ) / 3 ;

Y = MiddleWords ( Text ; 3 ; 1 ) ] ;

Date ( M ; D ; Y ) )

This gives a stored field.

[RalphL]

Here is another calculation for the date using the Let & Subsitute functions:

Let ( [ D = LeftWords ( Text ; 1 ) ;

MName = MiddleWords ( Text ; 2 ; 1 ) ;

M = Substitute ( MName ; [ "Jan" ; "1" ] ; [ "Feb" ; "2" ] ; [ "Mar" ; "3" ] ; [ "Apr" ; "4" ] ; [ "May" ; "5" ] ;

[ "Jun" ; "6" ] ; [ "Jul" ; "7" ] ; [ "Aug" ; "8" ] ; [ "Sep" ; "9" ] ; [ "Oct" ; "10" ] ; [ "Nov" ; "11" ] ;

[ "Dec" ; "12" ] ) ;

Y = MiddleWords ( Text ; 3 ; 1 ) ] ;

Date ( M ; D ; Y ) )

[-Queue-]

And another, using Ralph's first idea.

Date( (Position( "JanFebMarAprMayJunJulAugSepOctNovDec"; MiddleWords( Text; 2; 1 ); 0; 1 ) + 2) / 3; LeftWords( Text; 1 ); MiddleWords( Text; 3; 1 ) )

Let's only benefit in this case is clarity. It doesn't speed up the calculation any.