I had a similar idea : use substitute and search for 2x newlines.
To get the column names right, one could use the ":" as a separator, but:
- ":" is used also within the entries
- sample has a ":" - but misses a newline afterwards.
So I combened the two approaches:
- search 2x linebreak
- search ":"
to find the headers.
To separate the column name and the entry, one can stack the columns with the setting Contiguous.
Col2 is just used temporarily, so either it can be deleted at the end - or added as a Transform column.
Columns which are generated via Text to Columns from a Transform Column are the first ones in the table.
This makes it easy to select and stack them. Just count the new columns and select them by index.
With the Contiguous setting, some dummy columns are added, those are removed in the last step.
dt = Open( "$DOWNLOADS/example3.jmp" );
tmpCol = dt << Transform Column ( "split",
Character,
Formula(
Substitute(
Regex(
Substitute( :Column 1, "\!r\!n", "#", "Sample:", "Sample^\!n" ),
"#{2,}(.*?):",
"^\1^",
GLOBALREPLACE
),
"#", "\!r"
)
)
);
before = NItems(dt << get column names());
dt << Text to Columns(
columns (tmpCol),
Delimiters( "^" )
);
newCols = NItems(dt << get column names()) - before;
result = dt << Stack(
columns(
Eval(1::newCols)
),
Number of Series( Eval(newCols/2) ),
Contiguous,
Drop All Other Columns( 1 )
);
// Move column names up
result << Move up;
cols= result << get column names();
cols = Filter each({col}, cols, Contains(col << get name, "split"))
result << Delete Columns(cols);
Doesn't look nice and smooth ...
Plan B: set up a Turing machine to calculate the entries ; )