Read fields and values from a record

[parispantelis]

hi,

im not a Master in scriping and i have the following problem:

i have a table (on sql server) with 3 fields:

1. Record ID

2. Timestamp

3. Data

inside the 3rd field i call "Data", i have several data like Field1:SomeValue, Field2: SomeValue,...,Field_N:SomeValue

the fields inside "Data" are not the same (in many records they start with "Field2" or "Field3" etc) and previous fields are missing.

I had an idea to export this table to TXT file and import again spliting data field to many Fields like @1, @2, @3 etc.

but inside 1st field @1 in some records i will have from "Data" the "Field1" and in some records i will have "Field2" or "Field3" etrc.

is there a way to read directly from "Data" field the several Fields and Values ?

Format is allways like Field1:SomeValue,Field2:SomeValue,Field3:SomeValue ....

The number of Fields inside the "Data" field are not specific

Any help will be appreciated.

[marcus_sommer · Accepted solution]

You could split them within qv in a way like this:

t1:

Load

     [Record ID], Timestamp,

     subfield(Data, ':', 1) as Fieldname,

     subfield(Data, ':', 2) as Fieldvalue;

Load [Record ID], Timestamp, subfield(Data, ',') as Data;

SQL Select * From db;

I'm not absolutely if the first subfield-statement worked within a preceeding-load (if not you could replace it with a normal resident load). Are Fieldname and Fieldvalue not real fields + values else more a kind of category with value you are already finish. Are this real fields you need a further generic load to create them as fields, see: Generic Load.

- Marcus

[parispantelis]

worked !!!

thanks Marcus