Convert .txt to excel

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?

Transformation

Accepted answers

fmvizcaino

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

2020-03-10 Convert txt to excel.yxmd

danilang

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.