How do I create a database from a string?

[wikhardt.zsolt]

Dear Community Members!

How shall I create a database from a sting variable?

I need 5 coloumns and 40 rows in all.
The coloumns should be “Question”, “Answer 1”, “Answer 2”, “Answer 3”, “Answer 4”.

The string file format could be seen as attached.

The structure is always the same. 1st line question, 2nd is the 1st answer and so on. This sequence (1 question and 4 answers) is repeated 40 times.

I have tried “generate data table from text” activity but did not help.

Thanks in advance!

[Yoichi]

Hi,

Is your input as the following, isn’t it?

Q1
A1-1
A1-2
A1-3
A1-4
Q2
A2-1
A2-2
A2-3
A2-4
Q3
A3-1
A3-2
A3-3
A3-4

If yes, the following sample will work.

yourString =String.Concat(yourString.Split(vbcrlf.ToCharArray,StringSplitOptions.RemoveEmptyEntries).Select(Function(s,i) if((i+1) mod 5=0,s+vbcrlf,s+chr(9))))

Then add headers and use GenerateDataTable with CSV (TSV) parsing mode.

Sample
Sample20231009-1aL.zip (2.7 KB)

Regards,

[Anil_G (Anil Gorthi)]

@wikhardt.zsolt

Welcome to the community

Try this approach

Say all data is stored in str and use build dataatble and required question and answer columns and build a newdt

1. Use a for each loop and give the in argument as str.Trim.Split({Environment.NewLine},Stringsplitoptions.None) and assign an index variable to index property in for loop properties panel
2. Inside loop use a if condition with index mod 5 = 0
3. On the then side use add data row with {currentItem,"","","",""} and give newdt as datatable
4. On else side use assign with dt.Rows(dt.RowCount-1)(index mod 5) = currentItem

After the loop newdt will have data in required format

Hope this helps

Cheers