subset of columns from multiple sheets

Question

Hi all:

I have a unique situation....

I have an excel file that has about 60 sheets.  I can input the sheet names, then filter out (to use) about 10 of them -- that's easy to do.

Now here is where the problem is....

The 1st 8 rows of each sheet (and all sheets are the same) have instructions for use, that I do not need, and the column headers start on row 9.  There are 30 columns, but I only need 3 of those columns, the rest I can ignore.

I'm using the dynamic input tool, but what template do I provide it?

Is there a simpler way for me to simply append all the sheets by column (ignoring content), then I can just select the column (by location) then do a dynamic rename?

I'm new to Alteryx, so please keep the solution simple.

Thanks.

-Tesh.

DataNath · Accepted Answer

To get rid of the first 8 rows:

Sample tool (Preparation Tools) > Skip first N rows > N = 8.

To assign values in column 9 to headers:

Dynamic rename (Developer Tools) > Rename Mode > Take Field Names from First Row of Data > Tick 'All' fields.

Trim to only 3 necessary columns:

Select tool (Preparation Tools) > Untick all that aren't needed.

In terms of all of the inputs coming together, are your files named similarly? I.e. Book1, Book2, Book3 etc? If so, you can use a wildcard on your Data Input tool and connect this straight to the first tool.

prpatel · Accepted Answer

Hi DataNath,

The sample tool only allows me to remove 1st 7 rows if I am bringing in just one sheet.  But how do I tell Alteryx to bring in 10 sheets from the same workbook (excel file), and remove the 1st 7 rows from all those sheets then append them?

So it's not multiple books, but multiple tabs in one book.

I wish I could share the file or workflow, but there is significant sensitive data that the company does not want to share, and I think that the workflow is not helpful unless you have the raw data.  I'll see if I can de-sensitize the data and then will attach if I cannot find a quicker solution.

Thanks again to you and all others out there for your help.

-Tesh.

Qiu · Accepted Answer

@prpatel 
I agree with @DataNath , we can set the data to start with Row#9 in the Dynamic Input Tool,

Refer the quick sample below.