Table Transformer - How to split formatted lists?

[Chris]

I have a table with cells containing lists.  I would like to use table transformer to separate these into individual rows so we can generate some statistics (e.g. count the number of listed items in each cell)

For instance, if you have the following table:

Line	Test
1	Item one Item two
2	Item three Item four Item five Item six

Use something like:

SEARCH / AS @[deleted] EX('Test'->split(NEWLINE)) / AS @[deleted] RETURN (@line->'Line' AS 'Line', @[deleted]->'Test' AS 'Test', @[deleted] AS 'Items') FROM T1

To create:

Line	Test	Items
1	Item one Item two	Item one
1	Item one Item two	Item two
2	Item three Item four	Item three
2	Item three Item four	Item four
2	Item three Item four Item five Item six	Item five
2	Item three Item four Item five Item six	Item six

Which can be wrapped in a 2nd transformer:

SELECT 'Line', COUNT('Items') AS 'Total' FROM T* GROUP BY 'Line'

Resulting in:

Line	Total
1	2
2	4

Unfortunately the split function doesn't seem to "see" the line break.

E.g. if you compare SUBSTRING and SUBSTRING_VIEW:

SELECT 'Line', SUBSTRING('Test',7,5) AS 'Sub', SUBSTRING_VIEW('Test',7,5) AS 'SubView' FROM T1

Results in:

Line	Sub	SubView
1	neIte	ne Ite
2	hreeI	hree I

Both return the same number of characters, even though one of them preserves the formatting, suggesting there isn't a demarking character for the 'split' function to look for.

The obvious fix would be to manually add a character (like a period ".") to the end of each line, but this would be less reliable than the formatting already established in a template.

Is it possible to split cell content based on formatting / lists?

Many thanks!

[Stiltsoft support · Accepted solution]

Hi @Chris ,

Seems that your assumptions are correct, you may check this similar question (last comments) for reference.

I agree that using periods at the end of each line may not be obvious for your users, so maybe it's better to switch to commas instead of lists: item one, item two, item three.

And you may use the Table Spreadsheet macro instead of manually created tables: here if you have a separate row for each item, the spreadsheet will be still looking neat. And you'll be able to use pivot table like in Excel to calculate your data.

Besides, if your items come from a specific list, you may use data validation as well.