Transform Text file to Excel

[tjamal1]

Hi 

I have a text file that contains text like this

School 1
(Desc)
5 Harwood Avenue, Unit 205, Ajax ON L1S 2C1
905-420-8641

School 2
(Desc)
1 Rossland Road West, Suite 203, Ajax ON L1Z 1Z2
647-889-4985/905-686-2224

School 3
(Desc)
846 Finley Ave, Unit 846, Ajax, ON L1S 3R8
647-739-1046

I want to transform these text based on the following criteria

1. The First line of every block goes to School Column (e.g School 1, School 2)

2. 2nd Line goes to Description Column

3. 3rd line goes to Address Column

4 4th line goes to the contact number Column

How can I achieve this in Alteryx Designer?

[Emil_Kos]

Hi @tjamal1,

I have prepared a solution for you:

The output:

[tjamal1]

Thanks @Emil_Kos for the reply and Alteryx file. 

Can you help us reading this Text file for the same purpose? 

[Emil_Kos]

Hi @tjamal1,

This workflow currently don't work because the description line is missing for the first example:

School 1
(Desc)
5 Harwood Avenue, Unit 205, Ajax ON L1S 2C1
905-420-8641

This is expected or this is just an issue with the sample file? 

[tjamal1]

Some Schools don't have a description.

[Emil_Kos]

Hi @tjamal1,

Rest of the lines are always available? 

The description always starts with ( and ends with )? 

[tjamal1]

The rest of the line always available 

Desc starts with a small bracket and ends with a small bracket too. 

Example

(Digital Curriculum Available)

[Emil_Kos · Accepted solution]

Hi @tjamal1,

It took me a while:

If this was helpful please mark my post as solved as I spend a decent amount of time on it 🙂 

[tjamal1]

Hi, @Emil_Kos Thank you soo much for the solution.  It did help very much.

Is there any way I can change the Number of Tiles dynamically based on the number of records I have. ?

[Emil_Kos]

Hi @tjamal1,

I shouldn't use tile tool in the first place.

This will work like a dream!