Pull in the last 10 columns from an excel spreadsheet

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.

Tips and Tricks

Transformation

Preparation

Accepted answers

kelsey_kincaid

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.

DynamicSelectExample-Last3+First2.yxzp

Maskell_Rascal

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

Dynamic Select Columns Based on Position.yxmd

All comments

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.

kelsey_kincaid

Hi @Kay327 ,

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.

DynamicSelectExample-Last3+First2.yxzp

mceleavey

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

solution.yxmd

Maskell_Rascal

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.

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

Thanks!

Phil

Dynamic Select Columns Based on Position.yxmd

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/td-p/89282

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703