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.
Solved! Go to Solution.
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.
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,
YES...this I think this works...when I used the input tool and asked for sheet names, I kept the "Start Data Import on Line" value as 1...I should have moved that to 8.
Thank you to you and DataNath.
-Tesh.
@prpatel
Thank you for the feedback and good to know it works.