Split Comma-separated values within a column/row to separated rows and columns

[mrnice]

Hi all,

I have a data set being returned from the download tool which has the column names and values in a single row and in a column separated by commas. My initial approach was to use the "Text to Columns" and split the data to Rows and then generate a unique sequence based row count (as the every 51 rows there is a new unique line starting (e.g. ColumnName, Row1, Row2). This would then allow me to Transform tools to get the data in the right format but no luck so far.

Edit: Sample file added

Thank you in advance and please let me know if you need me to provide an example.

[MarqueeCrew]

sample data would help us help you.

[BenMoss · Accepted solution]

As @MarqueeCrew says sample data would work a charm here, but from my understanding you can achieve what you want with two multi-row formula tools.

The first creates a rowcount which restarts every 51 records. Lets create a new field and call this 'RowCount'

if [Row-1:RowCount] = 5 then 1 else [Row-1:RowCount]+1 endif

Then the second, lets call it group, would be...

if [RowCount] = 1 then [Row-1:Group]+1 else [Row-1:Group] endif

Hope this helps.

Ben

[mrnice]

Exactly what I was trying to achieve ...Thank you Ben