Jump to content

extract concatenated data


This topic is 6520 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

smile.gif

Link to comment
Share on other sites

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. wink.gif

Link to comment
Share on other sites

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 smile.gif

Version: v6.x

Platform: Mac OS 9

Link to comment
Share on other sites

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",":")

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. smirk.gif

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6520 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.