transform a string

[ecolomer]

Hi

I have a csv file with the following structure:

- First value is Id

- Second value is a string consisting of repeats separated by commas sets with the following content: "1":"3"

The first number (1) is the number of questions and the second number (3) is the value of the reply

They are 55 repetitions and one o two more items but not in all cases

I would get a table with the following structure:

Id, Question, Answer

example:

Input file:

15, "1":"3", "4":"2", "3":"1"

table result

15, 1, 3

15, 4, 2

15, 3, 1

Attached is csv file

[maxgro · Accepted solution]

s:

first 100          // for test, remove

LOAD id_project_participant,

     replace(answers, '"', '') as answers

FROM

answers.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq)

//group by id_project_participant

;

final:

load

  id_project_participant,

  SubField(f1, ':',1) as q,

  SubField(f1, ':',2) as a;

load

  id_project_participant,

  SubField(answers, ',') as f1

Resident s;