Txt file Trailing Whitespace. Alignment affected

[Inactive User]

Hi All, can someone help me to delimit the data below. 

For the amounts it should have 3 columns

debit, credit, balance

I tried to used the replace the whitespaces using regex and replace it with pipe(|). However, the other whitespaces are delimited and this should have bakances of 0. I attached the sample input.

The picture is the desired output.

[Yoshiro_Fujimori]

Hi @Inactive User ,

I guess you need the first 3 rows, as the 4th row is different from the rest.

If that is the case, you can use Input Data tool specifying the file as Flat Ascii.

Then you can set the column width as below.

Result

I hope this works for your case.

[Yoshiro_Fujimori · Accepted solution]

Sorry I found an error in Input Data Tool for the Balance column.

I corrected it and added some data cleansing.

Workflow

Output

It should be better than the old one.

[Inactive User]

Thanks for the idea. However, this still does not work , there are instances wherein the amounts are not aligned properly thus being cutoff in another column.

[Yoshiro_Fujimori]

@Inactive User ,

Is it possible to define more clearly which number should come into which column?

Row 1,2,4 have two numbers, while Row 3 has one number.

Also it is not clear which the two numbers in Row 4 belong to.

It is hard to guess from your description and data.

[Inactive User]

The output will somehow look like this

[Watermark]

Anon, part of the problem is your sample data is too small, and not being familiar with it, we can't tell what that third row is with only a "0" is supposed to represent, debit? credit? balance?

Should the 3 columns all be the same width?  If there are no values in that width then it's empty? 

I can't help you without knowing how to treat various conditions. 

Here is what I have, which to an extent isolates the entries to deal with, and separating off the data you want to parse, but need more information to help any further. 

[Inactive User]

Hi thank you for your reply. I can't seem to run the workflow. Here is a sample desired output