Extract From String Expression Needed 1

[UncleLare]

Hello!

I'm trying to come up with an expression to extract a particular section of data from a string (and not having much luck).

Here's the layout of the data contained in the record strings in field1 of my table:
PART_NUMBER,PRODUCT_CODE,MATERIAL_DESC,UOM,VENDOR_NAME,HTSCODE,SCHBCODE,ECCN,USML,NSN,LIST,DISC,CORE

Here's a sample of the records contained in field1 of my table:
72325310-7,77,RETAINER: ASSEMBLY,EA,HONEYWELL L & E (DE PRICED),8531909001,8531900002,9A991.d,,,564.76,,
AA1H37-3,4K,VALVE: CHECK,NEW,EXCH,EA,TEMPEST PLUS MARKETING GROUP, LLC.,8481302090,8481302090,9A991.d,,,1174.88,,500
076308-45714,27,ABRASIVE: DISC,HOOKIT,947A,3IN,NH,120+,BOX,AVIALL DEFAULT VENDOR,6805100000,6805100000,EAR99,,,,,
0200A88BNES,1H,ENGINE: 0200A88B,NEW,EXCH,EA,CONTINENTAL MOTORS, INC.,8407100020,8407100020,9A991.d,,,30723.33,,11000
P450A0123-00,6S,PLATE IDENTIFICATION,EA,MOOG WOLVERHAMPTON LTD,,,,,,,,
2157-0167,E4,SLEEVE,EA,ROCKWELL COLLINS, INC.,,,,,,93.99,

I need to be able to extract the last 8 delimited pieces of each record into field2 of my table so that field2 would contain:
8531909001,8531900002,9A991.d,,,564.76,,
8481302090,8481302090,9A991.d,,,1174.88,,500
6805100000,6805100000,EAR99,,,,,
8407100020,8407100020,9A991.d,,,30723.33,,11000
,,,,,,,,
,,,,,93.99,

I've tried using the STREXTRACT function, but can't seem to make it play correctly. The delimiters (commas) need to remain intact in field2.

Any help would be appreciated!!

 

[Olaf Doschke]

Look into the STRECTRACT flags, you can include the delimiters. A simple way to get first and last values with comma as degin and end delimiter is adding commas: [tt]","+alltrim(field)+","[/tt]

There's another function, that's easier to apply to CSV, as these are, that's ALINES, because you can override the usual behavor to split lines with other separators, simply specify comma. Also no need to add commas as that would just generate an empty start and end element. Of course, when putting together the single AFIELD array elements you need to add in commas again yourself.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

UncleLare

I think there is a comma missing somewhere in the last line.

In this case, [tt]SUBSTR(field1, RAT(",", field1, 8) + 1)[/tt] will fetch the last segment as you require (except in the case of the last record, for the reason above).

 

[Mike Lewis]

If you know that there are always 13 items in each line, then you can use STREXTRACT(), specifying the occurrence.

So to get the 5th item, you would do:
[tt]
lcItem5 = STREXTRACT(lcField, ",", "," 5)[/tt]

The 6th item would be:

[tt]lcItem6 = STREXTRACT(lcField, ",", "," 6)[/tt]

And so on.

If you don't know in advance how many items there are in the line, you can find out by using OCCURS():

[tt]lnCount = OCCURS(lcField, ",")[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Or, for a completely different approach, you can copy the items into an array:

[tt]LOCAL ARRAY laArray
ALINES(laArray, lcField, 0, ",")[/tt]

This will give you an array in which each row corresponds to one of you values. So to get, say, the 8th item from your string, you just need to access [tt]laArray(8)[/tt]. And so on.

On balance, I think this would be simpler than my previous suggestion of using STREXTRACT().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I'm with atlopes: If you have sufficient commas in all fields the simplest thing is to determine the position of the comma splitting the last 8 expressions from the rest counting commas from right with RAT.

And with any strategy you have problems, if not all fields conform to the necessary amount of commas, also when values themselves contain commas as "CONTINENTAL MOTORS, INC" likely is, that's the death of a simple splitting of values and the reason the normal CSV data structure has strings delimited with quotes to distinguish commas within string delimiters from commas as field separators.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[UncleLare]

Excellent! Many thanks to all for the quick input! I ended up using atlopes suggestion...worked like a charm!

 

[GriffMG]

Mike,

I think you missed out a comma

lcItem6 = STREXTRACT(lcField, ",", ",", 6)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​