Txt File Cleanup Question

[canddig]

Hello Community, 

I have a question for you and I am not even sure it is possible to achieve. I have a txt file that is used to map accounts but the accounts are in a hierarchy based on how many times the name is tabbed in. So some of the columns can have one tab and some can have 5+ that would relate or roll up into the field above it. Here is an example: 

But I need it to be in this format so I can use it for my mapping 

Let me know if you have any thoughts or ideas. I have attached the sample txt file. 

Thanks in advance for thinking through this with me. 

[DataNath]

About to hop off my laptop but I started having a stab at this. Looks like the raw text has an additional layer of being 'tabbed' which pushes it a column further than you'd like, as I was thinking I could just fill down the values with multi-row formula tools if the current row was null. Have left my workflow in case it comes in handy and helps towards a solution!

[gabrielvilella]

You can have something like this. This is going to depend on how many layers you have. If you really want to make this dynamic, you can build a macro rather than copying and pasting the multi-row multiple times. 

[Ben_H · Accepted solution]

Hi @canddig,

Here's my take on it.

This method should work for any number of levels.

Regards,

Ben

[canddig]

This is a great start. It is splitting the values sometimes into separate columns but its a great base for me to work off of. Thank you! 

[canddig]

Thank you! When I try this one it is pushing everything into the first column which I am losing my hierarchy then. 

[Ben_H]

Hi @canddig,

It might not be obvious from what I uploaded but the input tool is configured like this -

Note that the Delimiters are set to \0

That's the most obvious issue I could see.

Regards,

Ben

[canddig]

This is perfect! Thank you Ben!