Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Ordering Columns after using batch macro to read multiple excel sheets?

kreynolds
7 - Meteor

Hello,

 

I am trying to read multiple excel files into Alteryx, but came across an issue with the column order being inconsistent across files. Is there a way to correct that while reading the files using a macro? Or can anyone think of a solution once I have brought the files into Alteryx? Below I've provided an example of what I'm looking at, as well as my desired output. I have attached the excel file as well. 

 

I'd greatly appreciate any advice.

 

Original:

 

kreynolds_0-1649359656717.png

 

Desired: 

 

kreynolds_1-1649359733885.png

 

Thank you

 

8 REPLIES 8
apathetichell
18 - Pollux

your schema is different in your second record. probably f1 is being read in as a different format - or there are phantom columns. if column names are the same - swap out union on position to union on name in interface designer in your batch macro.

kreynolds
7 - Meteor

Thanks for the response!

 

These excel files are put together by different individuals so some start on different columns, but they contain the same reports. There are no headers unfortunately so the union on name doesn't work. Any other ideas?

soccertil1108
8 - Asteroid

If you can logically differentiate specific formats of the records, then you could use a Filter to split the data into multiple streams, use a Select to set the field names and data types, then Union the streams back together. In your example the logic for the Filter would be  IsEmpty([Field1])  and you'd have two streams.

apathetichell
18 - Pollux

try swapping form union by name - to union by position...

kreynolds
7 - Meteor

I believe filtering is tricky because even the sheets that start in the first column have some empty values. Then those rows would get shifted over as well. Or am I misunderstanding?

apathetichell
18 - Pollux

if you attach a datacleanse tool in macro you can set it up to drop entirely null columns. You would then union by position. All of this assumes that your sheets are basically schematically consistent. My hunch is that may not be the case.

soccertil1108
8 - Asteroid

The only tricky part is logically understanding the differences that can occur and determine how many unique situations you need to split the flow into. Then the cleanup becomes simple. I put together a sample where there are three different record formats.

 

soccertil1108_0-1649439938872.png

 

kreynolds
7 - Meteor

This logic worked for me! I was able to apply filters to separate the sheets based on the column they start on. Thank you!!!

Labels