Column rearrangement based on a formatting file.

[The1804]

Hi everyone,

I am trying to get column structure and order in one file (Input1) based on a different file (Input2).

Input1

USER1	USER6	USER2	USER4	USER9
535	MKO	111	RRR	765
REEW	NJ8	222	FFF	UYT
YRF	BHU	333	DFD	YTR
45T	765	444	FDF	TRE

Input2

FIELDS

USER0

USER1

USER2

USERG

USER4

USER5

USER9

USER7

USER8

USER6

My current solution is very close to achieving that task. However it doesn't respect empty columns and pushes everything to the left.

USER1	USER6	USER2	USER4	USER9	USER0	USERG	USER5	USER7	USER8
535	MKO	111	RRR	765
REEW	NJ8	222	FFF	UYT
YRF	BHU	333	DFD	YTR
45T	765	444	FDF	TRE

My expected result looks like this.

USER0	USER1	USER2	USERG	USER4	USER5	USER9	USER7	USER8	USER6
	535	111		RRR		765			MKO
	REEW	222		FFF		UYT			NJ8
	YRF	333		DFD		YTR			BHU
	45T	444		FDF		TRE			765

If someone could take a look at my flow and give me a hint I would really appreciate it. Ideally I would like to convert the solution into a macro so it can be reused with many different column patterns.

My real data has over 50 columns.

[caltang]

It will have to follow certain naming conventions and your data will be transformed. But instead of making it a macro, you can reuse @DataNath or my workflow within a workflow - that doesn't take up too much time nor processing power.

If you do insist on a macro, then yes - it is better to open a new thread.

[DataNath]

No need to open up a new thread - turning my flow into a macro requires 2 incredibly simple changes: make the Text Input tools Macro Input tools and then add a Macro Output tool after the union:

Now if I place this in a new workflow with my field order:

And some starting data (again, out of order and with not all fields present):

Output is as desired:

One thing to note: If you'll always be using [FIELDS] as the name for the mapping/order list, you can leave things as they are. However, if this column name can be something different like [FieldNames], [FieldOrder] etc, then you'll need to enable Field Mapping in this Macro Input anchor. You can do that here:

This just allows you to map [FIELDS] - that has been used to build the macro - to the equivalent in the actual dataset you're using so that instances of it can be replaced when it runs.

Macro and example workflow both attached as a packaged workflow. Hope this helps!