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

Pull in the last 10 columns from an excel spreadsheet

Kay327
6 - Meteoroid

Hello,

 

I am using an .xlsx Excel spreadsheet as input in my workflow. The issue is the spreadsheet contains over 300 columns and I only need 12 of them. The first 2 columns (labels) and the last 10 columns (last 3 months of data). Is there a way to only pull in the columns of the spreadsheet you need for your workflow?

 

Thank you.

7 REPLIES 7
mceleavey
17 - Castor
17 - Castor

Hi @Kay327 ,

 

Probably the easiest way is to define a range in your Excel spreadsheet which includes the first 12 columns, and in the input tool, rather than selecting a sheet select the range you previously defined.

 

M.

 



Bulien

kelsey_kincaid
12 - Quasar

Hi @Kay327 ,

I don't know of a way to pull only the columns you're interested in into the workflow, but there are ways to pare it down to only the columns you want after the data is already in the workflow. Would something like the attached work for you?

 

EDIT: I just realized that I should have done Last 10 instead of Last 3 in this workflow, but that should be an easy edit should this work for you.

 

kelsey_kincaid_0-1619119834217.png

 

 

 

mceleavey
17 - Castor
17 - Castor

Alternatively, you can reduce the data down in the workflow:

 

mceleavey_0-1619119947558.png

 

M

 



Bulien

Maskell_Rascal
13 - Pulsar

Hi @Kay327 

 

This solution is similar to the one @kelsey_kincaid provided, but doesn't require you to flip the data so you maintain your field types. 

 

I'm using a Field Info and Sample tools to get a list of field names I want to keep, Union to combine them, and a Dynamic Rename tool writes these field names to the Field Descriptions. From there, the Dynamic Select tool. is configured to only keep fields where the description is not empty. 

 

Maskell_Rascal_0-1619120144902.png

 

If this solves your issue please mark answer as correct, if not let me know!

 

Thanks!

Phil

RaviP
8 - Asteroid
DQAUDIT
9 - Comet

@Kay327 

 

Just to round out your options.  Here's another method.  It's seems like the theme here is Transpose and Crosstab is the way to go.

 

DQAUDIT_0-1619120448835.png

 

I'm assuming that columns 1,2 are your Key.  With the input file import your Excel File and mark the option that says First Row contains data.  This will yield F1,F2 and so on column headings.  Assuming your column headings are predictable you could pair this with a dynamic rename to bring in the desired titles.

smoskowitz
12 - Quasar

@Kay327 --

 

One option is to make sure that in the Input Tool is to make sure that First Row Contains Field Names is Checked. 

 

You can then Transpose the data and do some prepping to parse the number from F. Do a Max on the numbers and bring that into a dynamic select to keep the F+Max. So if the max is 100 you can do some prep to keep F88-F100 and  bring that into a Dynamic select. Sounds good in my head, but I think its doable. 

 

Thanks.

Seth

Labels