Importing data and extracting field information

[enquirerfm]

I'm importing a lot of data into a database and because of the way the data is formatted I need to write a script which will extract parts of a field and then apply these to other fields. For example, the problem field is made up of various elements, examples:

(a) x6 size10 black shoes

(: x8 size8 white shoes

© x5 size 4 yellow t-shirts etc

Prices etc are already in separate fields so for any one record using one example from above I would have the following 4 fields filled from an import:

Field 1: Price $10

Field 2 (from a. above): Description e.g.

x6 size10 black shoes

Field 3: Supplier

Field 4: Country of origin.

*Field 5: Colour (empty)

*Field 6: Size (empty)

*Field 7: Qty (empty

So when I import a record, my fields 5 (colour), field 6 (size) and field 7 (qty) are all empty. I need to fill these fields with the data from Field 2 above and ultimately then delete the data from Field 2 so I am left with only 'shoes'. The record would then look like this:

Field 1: Price $10

Field 2: Shoes

Field 3: ABC

Field 4: China

Field 5: Black (colour)

Field 6: 10 (Size)

Field 7: 6 (Qty)

Is this an example of using PatternCounts and Value lists? how might it work? Any help much appreciated - thank you.

[Raybaudi]

Is Qty always preceded by an "x" [color:red]w/o a space ?

Is Qty always the first word ?

Is Size always preceded by an "size" [color:red]w/o a space ?

Is Size always the second word ?

Is Colour always the third word ?

Is Description always the fourth ( [color:red]and last ) word ?

If yes, then:

Colour: MiddleWords ( Field 2 ; 3 ; 1 )

Size: GetAsNumber ( MiddleWords ( Field 2 ; 2 ; 1 ) )

Qty: GetAsNumber ( MiddleWords ( Field 2 ; 1 ; 1 ) )

Description: MiddleWords ( Field 2 ; 4 ; 99 )

[enquirerfm]

Is Qty always preceded by an "x"

No - it is, however, contained in () e.g. (6 lge)

[color:red]w/o a space ?

Space before -

Is Qty always the first word ?

No - see above

Is Size always preceded by an "size"

No, it is combined with Qty - see above e.g. (6 lge)

[color:red]w/o a space ?

space may be there

Is Size always the second word ?

No - but it is always the second item in the qty bracket e.g. (10 sm)

Is Colour always the third word ?

No.

Is Description always the fourth ( [color:red]and last ) word ?

First words - can be quite long - eg can have 1, 2, 3, 4 or more words.

Example: French lace shoes (6 sm)red, Bertrand - this is one field when it is imported. So, I need French lace shoes (field 1), 6 (field 2 - qty field), size 4-5 (eg 'sm' in size field 3), 2009 (field 4 - year of manufacture) other fields imported OK.

If yes, then:

Colour: MiddleWords ( Field 2 ; 3 ; 1 )

Size: GetAsNumber ( MiddleWords ( Field 2 ; 2 ; 1 ) )

Qty: GetAsNumber ( MiddleWords ( Field 2 ; 1 ; 1 ) )

Description: MiddleWords ( Field 2 ; 4 ; 99 )

What do I do with these? But anyway looks as if something else required.

Thank you for any further ideas.

[enquirerfm]

perhaps we could simplify this. I've now managed to do most of this by one means or another. However, I still need to locate a text string enclosed in brackets () and export this to another field whilst changing the text to conform to the normal text used. e.g find (3 css) in field a, set field b = 3 cases. How does one do this since there are several finds/inserts to do? I've tried using a Case function but this doesn't work...

[enquirerfm]

maybe one approach is to use RightWords -- I can isolate this field using RightWords which is perfect because they I could run some kind of conversion on it.

Edited April 21, 2010 by Guest

[Raybaudi]

...I still need to locate a text string enclosed in brackets ()...

[color:red]Qty:

Let([

YF = YourImportField ;

Bstart = Position ( YF ; "(" ; 1 ; 1 ) + 1;

Bend = Position ( YF ; ")" ; 1 ; 1 )

];

GetAsNumber ( Middle ( YF ; Bstart ; Bend - Bstart ) )

)

[color:red]Size:

Let([

YF = YourImportField ;

Bstart = Position ( YF ; "(" ; 1 ; 1 ) + 1;

Bend = Position ( YF ; ")" ; 1 ; 1 ) ;

S = RightWords ( Middle ( YF ; Bstart ; Bend - Bstart ) ; 1 )

];

Case(

S = "sm" ; "Small" ;

S = "lge" ; "Large"

)

)