Pull in the last 10 columns from an excel spreadsheet

[Kay327]

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.

[mceleavey]

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.

[kelsey_kincaid · Accepted solution]

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.

[mceleavey]

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

M

[Maskell_Rascal · Accepted solution]

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. 

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

Thanks!

Phil

[RaviP]

Hi @Kay327 , you might want to take a look at @JoshKushner ’s solution and the thread 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Selecting-Columns-on-Excel-Data-Import...

[JBLove]

@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.

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]

@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