How do we output a fixed width delimiter file?

The extension of the file output is .LI6, the base can be read by Notepad++.

The file has around 10 columns, but the length of each is not all consistent and it is delimited by varying space.

Example data, “|” indicates a split in column on Alteryx:

A | B | C | D | E

1230E | 20231031 | B | HSE | GHI-123456

The data is similar to the above for about 10k records. Now the spacing between A and B is 1 space, B and C has 3 spaces, C and D has 5 spaces, whilst D and E had 2 spaces.

How can we achieve this? Looking to learn!

Output

Optimization

Accepted answers

apathetichell

@caltang - there are no nulls with a fixed width files - every non-entry is just space/spaces - and your padright() will pad the nulls with the full value of spaces.

and my take is that a fixed width file is that there is a max mapping (ie column a/x characters... column n/n characters) - this mapping is fed into the batch macro and is applied to the relevant column...

admittedly - doesn't have to be a batch macro - you could transpose and then match [Name] vs a column size mapping - apply your padding and and then crosstab.

All comments

binu_acs

@caltang Do you have the expected output based on the above input to have a better understanding of your request?

apathetichell

@caltang for fixed width - it's good to have a padright() function (usually in a batch macro) and you can feed in the column names and the needed width.

caltang

Hi @binu_acs here is a sample output. Good point, I forgot to add that there can be some scenarios where certain columns are null, and this is not standard as well. Any pointers?

Sample Output.txt

caltang

Hmm, okay @apathetichell - I will try that out myself. Though I am concerned about the random nulls that can exist in column, then this would mean I need to account for the max length of each column, and if a column is null, then I will need to make the padleft/right dynamic as well.

apathetichell

@caltang - there are no nulls with a fixed width files - every non-entry is just space/spaces - and your padright() will pad the nulls with the full value of spaces.

admittedly - doesn't have to be a batch macro - you could transpose and then match [Name] vs a column size mapping - apply your padding and and then crosstab.

caltang

Your way did it my friend! Thanks @apathetichell

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335