How to stack data from multiple worksheet of same excel but different structure

amsrivastav

Hi Folks,

I have one excel file (XLSX format) where I have three worksheets say SheetA, SheetB and SheetC.

Columns of SheetA are ColA , ColB and ColC

Columns of SheetB are ColA , ColB

Columns of SheetC is ColA

I want to stack data of all three sheets into one output; I DO NOT WANT to use MACROS in Alteryx Flow.

I tried to use Dynamic Input but it is giving me error stating files having different structure.

Please suggest!!

Thanks

Amit

Data Investigation

Accepted answers

All comments

danilang

Hi @amsrivastav

If you insist on not using macros then your only remaining option is to have 3 separate input tools, one for each sheet. Once the data is read, use a select tool to remove the columns that you don't want from each sheet and then union set to Auto Config by Position.

Dan

aatalai

@amsrivastav if you don't want to use a macro, and given that it is only 3 sheets, I would recommend having 3 data input tools one for each sheet and then using a union tool to stack them, how does that sound?

aatalai

@danilang great minds think a like

amsrivastav

Hi @danilang ,

Three files which i have mentioned is just an example, there could be any number of worksheet, so i cannot create individual connection for each tab.

Please suggest

Regard

Amit

amsrivastav

Hi @aatalai

Three files which i have mentioned is just an example, there could be any number of worksheet, so i cannot create individual connection for each tab.

Please suggest

Regard

Amit

aatalai

@amsrivastav then it might be difficult to do without the use of a macro. Is there a particular reason why you don't want to use one?

amsrivastav

Hi @aatalai

My flow needs to be publish on the Alteryx Server and Alteryx Server doesn't understand the Macros, hence i am trying not to use Macros in the ALteryx.

If there nay way to use Macros on Alteryx Server, I will be glad to use Macros as well.

Regards

Amit

aatalai

@amsrivastav is there a fix number of sheets? And if so how many? or will it change regularly (and if so is there a range?)?

aatalai

@amsrivastav does this post help? I don't deal too much with server

amsrivastav

Hi @aatalai

No, number of sheets are not fix, it can be of any number.

For Range, maximum range I have observed is from A to BA in excel, to be precise data was available from A1 to BA99999, but again, it may increase further as well.