extract concatenated data

[LVA]

I have a string that contains various other fields data concatenated.

I need to separate the data. I

[Lee Smith]

Hi LVA,

I suspect that we don't have enough information to give you a complete answer, however is this the result you are looking for:

1 1 16/02/04 15:00:00 Problem StreetAddress RSA

There are tabs in the spaces above, which is better illustrated with the returns below.

1

1

16/02/04

15:00:00

Problem

StreetAddress

RSA

Lee

[CobaltSky]

Hi LVA,

The LeftWords and MiddleWords (etc) functions treat the majority of non-alphanumeric characters, including date and time separators, as word separatoprs, so the date and time would be being treated as three words.

So for example, if you want to return the date as text, you could use:

MiddleWords(YourGlobalField, 3, 3)

- or if you want the date returned in FileMaker's internal date format, you could use something like:

Date(MiddleWords(YourGlobalField, 4, 1), MiddleWords(YourGlobalField, 3, 1), Right("20" & MiddleWords(YourGlobalField, 5, 1, 4))

Alternatively, you could use conventional text parsing with the Middle( ) and Position( ) functions, to extract the components of the string based on the positions of the semicolons. If there is a possibility that items may appear in different places in the field, and/or have a variable number of 'words' in them, then that may be preferable.

An example of a formula to parse the date (as text) out of your example string in this way is:

Middle(

YourGlobalField,

Position(YourGlobalField, ";", 1, [color:"blue"]2) + 1,

Position(YourGlobalField & ";", ";", 1, [color:"blue"]3) -

Position(YourGlobalField, ";", 1, [color:"blue"]2) - 1

)

Note, you can change which value is extracted by substituting different numerals in place of the ones that I've color coded blue in the above formula. For instance if you add 1 to each of the blue numbers the time (also as text) will be extracted from your example string instead of the date.

[Lee Smith]

That's a nice calculation Ray, but wouldn't it require a Script to parse out the different lines into records, or a change in the field type from global to a regular text field.

Maybe I should clarify what I had in mind a bit more.

Looking at the sample data, I don't see any reason to use calculations or scripts in FileMaker to separate this data into fields. A more straight forward approach would be to use a text editor (i.e. BBEdit), before import and separate the fields with tabs there instead.

It appears that one simple find and replace would take care of this, thereby allowing you to import the data directly into your fields.

Find semicolons and replace with a Tabs.

Lee

Version: v6.x

Platform: Mac OS 9

[Ugo DI LUCA]

CobaltSky said:

The LeftWords and MiddleWords (etc) functions treat the majority of non-alphanumeric characters, including date and time separators, as word separatoprs, so the date and time would be being treated as three words.

Ray,

As I already pointed in the past, Time separators wouldn't be considered as text separators in my French Version. Is this adressed in 7 ?

Actually, wether you're dealing with Time or other, the result of any "Word" based function when a ":" is enclosed would vary according to wether a number is closed to the separator.

ABC:JKIO => 2 words

12:00:00 => one word

ABC5:GHYUI =>2 words.

ABC5:7GHYUI=>1 word

So that

MiddleWords(YourGlobal,6,3) wouldn't extract the date in the French version. The result with LVA's calc would be :

15:00:00;Problem;StreetAddress

Middle(global, Position( )... is more accurate in this instance, and appears to be crossed version.

or an alternative I'm using would be :

Substitute(MiddleWords(Substitute(YourGlobal,":"," TimeSeparator"),6,3)," TimeSeparator",":")

[Lee Smith]

Hi Ray,

There is a typo in your second calculation.

Date(MiddleWords(YourGlobalField, 4, 1), MiddleWords(YourGlobalField, 3, 1), Right("20" & MiddleWords(YourGlobalField, 5, 1, [color:"blue"] 4 ) )

should read:

Date(MiddleWords(YourGlobalField, 4, 1), MiddleWords(YourGlobalField, 3, 1) , Right("20" & MiddleWords(YourGlobalField, 5, 1[color:"blue"]), 4 )

)

Lee

[CobaltSky]

Hi Lee, Hi Ugo,

Lee, Thanks for picking up the typo in the second calc I posted. It's perhaps also worth commenting that the order of elements in that calc is referenced to the regional format of the date in the example provided by LVA, and would not be applicable to dates elsewhere (in US format, for example).

Ugo, the majority of localized versions of FileMaker, plus the US version, treat the : as a word separator regardless of where it occurs (between numbers, letters or any combination of the two. This is referenced in the techinfo article at http://www.filemaker.com/ti/103853.html without mention of the French version exception.

In the Australian and US versions and a number of other localized versions:

ABC:JKIO = 2 words

12:00:00 = 3 words

ABC5:GHYUI = 2 words

ABC5:7GHYUI= 2 words

I don't actually know where LVA comes from, but I and most others who post here tend to speak in terms of the default operation of FileMaker (typically the US functionality) unless the poster explicitly requests otherwise. It is, after all, a US-hosted web site we are posting to, even though you are in France and I am in Australia. A much more frequent ocurrence is that calc formulae are posted using comma separators for the US version, rather than the semi-colon as for some localized versions. I note that you frequently do this yourself (including in this thread).

Whether using commas as the separators in a function, or talking about the behavior of time separators in terms of the majority of international versions, I hope you'll accept that no slight is intended against those nations and cultures where a different practice is followed.

[Ugo DI LUCA]

CobaltSky said:

Whether using commas as the separators in a function, or talking about the behavior of time separators in terms of the majority of international versions, I hope you'll accept that no slight is intended against those nations and cultures where a different practice is followed.

Sure.

I should have added "for information". This could have some bad effects in some solutions supposed to be shipped here from the other side of the pond.

And yes, I'm a host here and I adapt myself with either the separators and translated function names when I post.

[LVA]

Thanks for all your help.

The date we use here is day, month , year

I used the following script that Lee sugested, but I changed it a little:

Date(MiddleWords( _gVL_EMSNewCase ; 4; 1); MiddleWords(_gVL_EMSNewCase ; 3 ; 1 ); MiddleWords(_gVL_EMSNewCase; 5 ; 1 ))

I garanteed that the date will use the year with four digits so that I won