Breaking strings to fields

[prieper]

Dears,

I am trying to convert some data, which come in a format like

100110 Durum wheat 100111 Seed 100119 Other 100190 Seed, White, Other
100200 Rye
100300 Barley
100400 Oats 100410 Seed 100480 Other

I would like to convert it into a databaseform like

CODE DECRIPTION
100110 Durum wheat
100111 Seed
100119 Other
100190 Seed, White, Other
100200 Rye
100300 Barley
100400 Oats
100410 Seed
100480 Other

Do you have an idea, how to break up the individual lines, of which each may have betw 1 and 100 different records?

Peter

[rbecher]

Hi Peter,

how about a replace ' 1' (blank+1) with ';1' and then the use of subfield? This would work if code is starting with '1' every time..

load left(code_desc, 1 6) as CODE, mid(code_desc, 7) as DESCRIPTION;
load subfield(replace(field, ' 1', ';1'), ';') as code_desc from ...;

- Ralf

[prieper]

Hi Ralf,

The cracking of the string into individual fields it not the problem, the problem I see, is how to convert them into into individual records?

So one idea might be to insert in the text-file at each code-number a CRLF, but have no clue, how to do.

Peter

[prieper]

Tried to crack the lines with kind of brute force, with a loop counting from i=1 to 100 and then using the construct with Substring(Line, Digit, $(i)), but wonder whether there is a more elegant solution.

Peter

[rbecher]

This will do the subfield function without the third parameter. Can you upload a sample? I would make a small app..

[rbecher · Accepted solution]

Hi Peter,

I made an example app with your data from the first post. There you can see how this goes..

- Ralf

[prieper]

Works! Had only the three-parameter-variant in mind - sometimes it is worth to read the manual.

Thanks & Have a nice day!
Peter