How to separated rows when it is not grouped by Record ID, only separated by a comma?

[mzsweetumz]

How to separated rows when it is not grouped by Record ID, only separated by a comma?

I tried the text to column function and split these into rows, but it only allows me to select 1 column to split and that's not what I'm trying to do.

Any help is appreciated!

[Thableaus]

Hi @mzsweetumz 

Just a quick question - why don't you read this .txt file as .csv? Is there any problem with the file structure?

Cheers,

[Luke_C]

hi @mzsweetumz 

Some sample data would be helpful, but you could add a record ID, transpose all your columns into one field, then use the text to columns tool to split it into rows all in one shot. Then I'd probably use the tile tool to add a secondary ID based on the first record ID to help group the data, then cross-tab it back based on the 2 record IDs. 

[RolandSchubert · Accepted solution]

Hi @mzsweetumz ,

you could transpose the fields, use the Text To Columns tool to split data to rows, add a row id using the Multi-Row Formula tool and use a Crosstab tool to restore the columns.

What do you think?

Best,

Roland 

[mzsweetumz]

@RolandSchubert This worked PERFECTLY! It gave me exactly what I needed and it makes a lot of sense after reviewing your workflow. Thank you so much!

Also, thanks to everyone else who offered assistance! :)

[mzsweetumz]

@RolandSchubert I QA'd the dataset and realized some of the data were merging together. I am not sure why.

@Thableaus Oh does read as a CSV.

@Luke Here is the data sample... I have to save the text as a word doc as they wouldn't allow me to upload the .txt file for some reason.