split into rows

Hi everyone,

I came across following problem,:

The source excel data is like :

data	org	company	personal	usage	info
2018-03-15 2018-03-15	398783614 398783614	31050161393600002149 31050161393600002149	6236684220008412455 6217000130041118372	salary salary	2331.78 SH31000009193009701 2215.95 SH31000009193009701
2018-05-15 2018-05-15	398783614 398783614	31050161393600002149 31050161393600002149	0000100532908 6217003860022562324	salary salary	519.0091930 3591.18 SH31000009193009701
2018-03-23 2018-03-23	398783614 398783614	31050161393600002149 31050161393600002149	6217001820025722459 6236680130004083750	salary salary	1401.65 SH31000009193009701 1503.5 SH3100009193009701

every cell contains two rows of data and however combined as one with a line break '\n'

And i would like to split the data like this :

data	org	company	personal	usage	info
2018-03-15	398783614	31050161393600002149	6236684220008412455	salary	2331.78 SH31000009193009701
2018-03-15	398783614	31050161393600002149	6217000130041118372	salary	2215.95 SH31000009193009701
2018-05-15	398783614	31050161393600002149	0000100532908	salary	519.009193
2018-05-15	398783614	31050161393600002149	6217003860022562324	salary	3591.18 SH31000009193009701
2018-03-23	398783614	31050161393600002149	6217001820025722459	salary	1401.65 SH31000009193009701
2018-03-23	398783614	31050161393600002149	6236680130004083750	salary	1503.5 SH3100009193009701

it seems the text-to-column does not help.

How Alteryx achieve this? Need help.

Thanks

sample.xlsx

Workflow

Datasets

Transformation

Accepted answers

Luke_C

Hi @BOBBY321

The text to columns is the right tool, but since it only does one field I'd suggest transposing your data to do it in one shot then crosstabbing back:

New Workflow3.yxmd

All comments

messi007

@BOBBY321,

Could you share the source file (Excel or CSV) it will help us to give you the solution.

Regards,

Luke_C

Hi @BOBBY321

The text to columns is the right tool, but since it only does one field I'd suggest transposing your data to do it in one shot then crosstabbing back:

New Workflow3.yxmd

BOBBY321

I have upload a sample. It is from an OCR result, so have much to do with the data cleaning

binu_acs

@BOBBY321 one way of doing this

Arrange_records_18_07.yxmd

BOBBY321

That helps. Thank you Luke

IraWatt

Hey @BOBBY321,

Here is another way of doing this:

This solution uses the transpose and cross tab tools which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117