topic Extract Specific Character Count From Field in App Development

Extract Specific Character Count From Field

DataWhisperer — Tue, 27 Jun 2023 08:34:52 GMT

Good day,

I have a data set with a field which has free form captured data, there are values which are only of 10 characters and I want to try pull each value that is 10 characters long into it's own field. Any ideas? Subfield doesn't seem to work as there are random delimiters between the values due to the free form capture.

Item Description
Meter Numbers: 14339931983 and 14339932049
Meter Numbers: 07142285035, 07142285050, 07142285100, 07142285209, 07166767603, 07265238316 and 07265264668
Meter Number: 14363165755
14299841594
37242709626 AND 37242759852
14428988233
07148283174
37232477309

figure 1: Current date set

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7
14339931983	14339932049
07142285035	07142285050	07142285100	07142285100	07166767603	07265238316	07265264668
14363165755
14299841594
37242709626	37242759852
14428988233
07148283174
37232477309

figure 2: How I want my data to look

Re: Extract Specific Character Count From Field

MendyS — Tue, 27 Jun 2023 08:51:17 GMT

@DataWhisperer

There is a solution, it's a bit confusing, I'll try...

The first step - to download all the characters that are not numbers,

Second step - for each row will be a key that is the original value (without the letters dropped in step 1)

Third step - to split into two columns, first column will be the key and second column will be the splitting of the values using the space character,

Step four - to make a cross table.

Please let me know where you are getting stuck and if you need help,
best

Re: Extract Specific Character Count From Field

Or — Tue, 27 Jun 2023 09:20:58 GMT

Something along the lines of:

Load Mid(Item,(10*(IterNo()-1)) + 1,10) as Item_New

While IterNo()*10 < Len(Item);

Load KeepChar(Item,'0123456789') as Item

From SomeTable;

Might not be precise but that should be the right neighborhood and you can tweak it as needed.

Re: Extract Specific Character Count From Field

barnabyd — Tue, 27 Jun 2023 21:46:00 GMT

G'day @DataWhisperer, what an interesting puzzle!

I would start by cleaning the data to produce something consistent to work with. Based on the sample you have provided:

trim off the leading and trailing spaces
convert all text to lower case to reduce the number of combinations
convert each type of delimiter to one that you prefer - this seems like an impossible task but you'll find that there aren't as many variations as you think - probably less than 10 replace() statements

Something like this:

replace( replace( lower( trim( [Item Description] ) ) , 'and', ',' ) , 'other delimiter', ',' )

After this, you should be able to use the subfield().

I hope this helps.

Cheers, Barnaby.

Re: Extract Specific Character Count From Field

MarcoWedel — Wed, 28 Jun 2023 20:32:53 GMT

Hi, another solution might be:

mapNonNum2Space: Mapping LOAD Chr(RecNo()), ' ' AutoGenerate 65535 Where not IsNum(Chr(RecNo())); mapPurgeMultiSpace: Mapping LOAD Repeat(' ',100-RecNo()), ' ' AutoGenerate 98; table1: Generic LOAD *, 'Field'&AutoNumber(RowNo(),RecNo()), SubField(Trim(MapSubString('mapPurgeMultiSpace',MapSubString('mapNonNum2Space',[Item Description]))),' ') Inline [ Item Description Meter Numbers: 14339931983 and 14339932049 Meter Numbers: 07142285035, 07142285050, 07142285100, 07142285209, 07166767603, 07265238316 and 07265264668 Meter Number: 14363165755 14299841594 37242709626 AND 37242759852 14428988233 07148283174 37232477309 0123456789,9876543210 abc1234567890defg0987654321 numbers:2345678901/1098765432/123456789 there1234are567numbers89012hidden3456789in0123this4567text890 ] (delimiter is ';');