Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Importing named columns from multiple sheets of different excel files


Hi all,


Having a bit of trouble with this one, so thought of reaching out to the excellent community here.


I am trying to hard code an index of all sheets from different excel files and their specific columns that I want. Here is a screentshot of my index file:




Now my objective is to pass that filepath as the input to either a batch or dynamic input tool and import them in one convenient one. However, I kept getting a "you have come across a bug" error.


Perhaps I am missing something but any help on this issue would be appreciated.




Are you able to upload your workflow/macro/packaged process?


I think you will need to go down the batch macro route, as there may be slightly different field types across the different sheets (it's even impacted in having different string lengths)


Hi Joe,


Attached is just an example of excel file I was using as an index. I dont have the actual workflow (internal restrictions).





Hi @adrrs 


Do the columns you want all have the same name?




If they do, my macro that I created will be incredibly useful for you: here


If not, let me know and it can be tweaked.


If by column names you mean the index, then no, they change for most sheets.


Hi @adrrs 


Here's a solution based on a batch macro.  


The main workflow just passes the bookname/range string to the control parameter of the macro



For each Book/Range record passed in to the macro, the 2 action tools update the file path and add a variable to the result with the range information so you can keep track in the main workflow.  The macro is set up so that the result columns can vary between iterations.  For the sample I've set up, here are the results



I set up the column names in the 3 Excel workbooks to reflect their respective book and sheet names.  The attached package contains all the sample excel sheets as well







This is great


Just one follow-up: is it possible to extract multiple, disjointed columns from a worksheet.? For instance, say I want columns A1:C200 and also columns BU1:CD200 so that the rows align.


Thanks a lot!


HI @adrrs 


You should be able to do that by adding another row to your index file. It will then batch through that as a new set of data.


Thanks! Would that then just add the new named columns in the index as additional columns? If so, is the output additional columns aligned to the other columns or would they be as separate rows? I am asking as I would prefer the former.


I can also test this at my end but though I'd ask on the forum too!


In @danilang  solution I am not too sure without testing myself.


With my macro, it would read the column names in, and if they were called the same they would be aligned.


But it would always be extra rows of data in both solutions. Due to the batching nature.