community
cancel
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

Meteoroid

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:

 

clipboard_image_1.png

 

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.

 

Thanks!

Alteryx
Alteryx

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)

Meteoroid

Hi Joe,

 

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

 

Thanks!

Rohit

Alteryx
Alteryx

Hi @adrrs 

 

Do the columns you want all have the same name?

 

Edit:

 

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.

Meteoroid

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

Highlighted
Aurora

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

 

Macro.png

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

 

Results.png

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

 

Dan

 

 

 

Meteoroid

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!

Alteryx
Alteryx

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.

Meteoroid

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!

Alteryx
Alteryx

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.

Labels