Convert .txt to excel

[icruz23]

I have a txt file that is organized in batches as seen below (invoices cleared by certain checks)

I am having a hard time converting to an excel workbook, i have tried various iteration of delimiters (",", "\t", etc) and i cant find an easy way to convert all the headers herein to the appropriate columns.

First time working with txt documents and don't necessarily know how to use the text to column tool to the best of its ability. Would really appreciate the help?

[fmvizcaino]

Hi @icruz23 ,

It really depends on how your txt file was built.

Would you be able to share a sample of your txt file? 

Best,

Fernando Vizcaino

[icruz23]

attached is a sample. thank you for your quick reply @fmvizcaino 

[fmvizcaino · Accepted solution]

Hi @icruz23 ,

Attached is a suggestion showing how to interpret your txt. Please insert your dataset in the same folder as the workflow is.

Best,

Fernando Vizcaino

[danilang · Accepted solution]

Hi @icruz23 , @fmvizcaino 

Here's a different way to fill in the missing check header Info on the detail rows

Use a single multi-row tool to build a unique CheckNumber for all the rows in each check group.  Split the rows into initial rows, that have all the info to be copied and detailed row that don't.  Join on CheckNumber and take the common info from the right and the detailed info from the left.  Union both and sort. The advantage here is that you don't need a separate Multi-row tool for each of the common columns. 

Dan

[icruz23]

Thank you so much!

Just so i can learn, what exactly is the Regex replace function doing? 

Or if you could just walk me through the formula tool portion of the solution?

Thanks again for your time!