Text file with multiple separates - how to format it

Help Activities
,
1

Hi there,
I have a text file like this with two headers. Headers are separated by tab and the data are separated with tab, comma and blank row in between:

TestName ScoreST
EngLang AA,A+,B,BB,C,A+
BB,CC,B+,AB,C+
Geography B+,C+A+,A+
A+,BA+,A+,B

Physicals A+,B+,A

The data means the text file has EngLang with two entries, Geography has with two entries, there’s an empty row and the Physicals with 1 entry.

I write Generate Data Table with Comma,tab and newline criteria only but not getting the results as needed. I want the output like this in Excel:

TestName ScoreST
EngLang AA,A+,B,BB,C,A+
BB,CC,B+,AB,C+
Geography B+,C+A+,A+
A+,BA+,A+,B
Physicals A+,B+,A

2

Input text file
image

Output like this
image

3

You might read text file, then split lines like this:

image

then parse every line following your specific format needs

4

@dokumentor tkanyou. Does this mean me do not use Generate Datatable ?

5

I recommend to parse every line and use Add Data Row to insert data to datatable (you will need to use Build Datable before in order to initialize datatable with corresponding columns)

Hope it helps!

1 Like