This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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:
1.Inputhas "LIST ALL SHEET NAMES" selected, & Ouput File Name as Field = "Full Path"
2.Text to column toolsplits 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"
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:
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!
@ripcityscorp15 You have 2 action tools, and the second one is completely overwriting everything that the first one is doing. I would connect both of your control parameters to 1 action tool. Perhaps I'm missing something, but this simpler formula seemed to work with your dataset (where #1 is the file path and #2 is the sheet name):
For future reference, you can use the field [Destination] in the formula to use what's already in the tool. The action tool is the 1 case where you can use data coming in as well as the data that's already in your tool to build your formula. In this particular case, i think it's easiest to just use 1 action tool. For an example, there are times where I use a function like:
Replace([Destination],"text to replace", DateTimeParse([#1],"%m/%d/%Y"))
Incredibly helpful! There was very little, via search, I could find in the Community, giving good detail about this specific functionality of the Action Tool. Thank you for taking the time to explain it all out!