how to load words of a string into a table

[Anonymous]

Hello

I'm blocked with this problem for several days ...

I can extract a string from a XML file and I can put it into a field of a table, by using LOAD FROM file (XmlSimple, Table is ...).

Now, rather than inserting the string, I would like to insert each word of this string (words are separated by « ; ») into the table. And there, I am blocked.

I handle well functions like « subfields », « substringcount »…  to extract each word but I do not know where to put the loop (« for » or « do while ») in order to load each word of the string into the table…

Thanks a lot for your help!

[Miguel_Angel_Baeyens · Accepted solution]

If you don't specify the third parameter in SubField(), it will create as many rows as different values, without any loop required:

SubField('Value1;Value2', ';') AS Field // will create 2 rows

SubField('Value1;Value2;Value3;Value4;ValueA', ';') AS Field // will create 5 rows

[sunny_talwar]

Would you be able to share few rows of data that you have and what exactly you would like to have as an output. It would also help if you can share what you have used as a script thus far.

[ogautier62]

Hi,

what you need seems to look at this :

load ......, subfield(trim('your text field'),' ',iterno()) as word, ....

from 'your table' while iterno()  <= substringcount(trim('your text field')," ")

so you have a loop (while iterno() for each text field which depends on number of blank char)

regards

[Anonymous]

It seems working by simply removing the third parameter. thank you for the tip !

However each word is extracted "as is", even with the blank space just before the word. It would not be a problem if all words would be with this blank space, but the first word has no blank space which makes the ordering (ascendent for example) not working.

Would you know how to normalize the format of the words (ie without blank space) ?

Thank you very much for your help

[sunny_talwar]

To remove Blanks, use Trim() function

Trim(SubField(....)) as ....

[Anonymous]

It works.

Thank you very much!