Hi All,
Here is my current scenario:
I have an excel file with multiple tabs, all with different schema. I am currently trying to combine these tabs into one output from Alteryx.
My current workflow looks like this:
Main Workflow 1. Input has "LIST ALL SHEET NAMES" selected, & Ouput File Name as Field = "Full Path"
2. Text to column tool splits out The File Name and the Sheet Name as such:
- FileName: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx|||`2018 Budget$`
- FileName1: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx`
- FileName2: ||List of Sheet Names>
3. Select Tool: Changes the Name of "FileName1" to "File Name"
4. MACRO TOOL:
- Macro Workflow
- Currently My Macro Tool Looks like this:
- The First Action Tool is "Update Value (Default)"
- I select the box "Replace a specific string" and then remove the "||List of Sheet Names>" so all I have remaining is the Filename1 value listed previously.
- The Second Action Tool is "Update Value (Default)"
- I select the box "Replace a specific string" and then remove the "C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx||" & " S' " so all I have remaining is the First Tab Name.
- This will have same Tab name as the Tab selected in the Input File Listed below.
- Input File: Choose the First Tab of the excel file
- (It can be any tab, but easiest to just choose the First. Also, this Tab name will be the one listed in the 2nd action tool previously mentioned)
RESULT: When I currently do all of this -- the workflow works as I imagined. Pulling all the different excel tabs into one Alteryx table that can then be manipulated.
HOWEVER! I do not want future users of the tool to have to go into the Macro, and have to manually manipulate the "Replace a Specific String" in the Action Tools in order for it to work.
Therefore, I then used "Update Value with Formula". It is my understanding that the same string of data would flow into the Action Tool (aka the FileName listed above), and that using a formula would be able to get me tot he same result I had been manually typing on the First and Second Action Tool.
As a result, here are the two formulas I currently am using on the action tools in the Macro:
- First Action Tool:
- Substring([#1],0,Length(Left([#1],FindString([#1], "|"))))
- This should get me the result:
- C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx
- Second Action Tool:
- Substring([#1],Length(Left([#1],FindString([#1],"|")+1)),
(Length(Left([#1], FindString([#1], "$")))
-Length(Left([#1],FindString([#1],"|")+1)))) - This should get me the name of my first tab:
When I run the workflow, though, with the Macro using these formulas I get this error:
- Error: New MULTIPLE TABS BATCH MACRO_V2 (79): Record #1: Tool #11: File not found "C:\Users\zszykowny006\Documents\2018 Budget"
I have confidence these Formulas above are correct after testing them in a different workflow using the formula tool.
So my question is:
- Why does the full file name not come through? The problem seems to be with my First Action Tool formula...As it only only pulls through " C:\Users\zszykowny006\Documents\" and is missing "Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx"
- If the full file name comes through, I feel like this should work the same way as when I was manually manipulating the Update Value (Default) option.
I have spent many a hour on this, so any help would be truly appreciated!
Thanks!