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 want to input data in excel but sometimes the sheet name is different, how do i input the files regardless of the name of the sheet?
Other than that, another question is if i want to input sheets name that starts with the same logic like aa122, aa123, aa124, how do i make it happen?
And for the last question, when i input files that maybe the excel itself sometime will also named wrong, do i have to rename it manually myself or there's any way to read those files?(What i learned is maybe can use directory tool but i'm not sure if it is applicable).
3 questions in 1 here, and there are multiple ways of doing it, im not sure if mine are the most elegant, but they might be good enough.
I assume that there is only one sheet in your excel? otherwise you have to be realy sharp on your naming. That out of the way, you can use the "import list of sheet names" function from the input tool, and then feed that list into a dynamic input tool. select your file and sheet as input template, and then select the "Change file / Table name" option to import any sheet name. Again, this only works if you have same layout for all sheets, or only have 1 sheet
if there are more than 1 sheet, you should use the same logic, but add a filter in the middle with Contains([Sheet name],"aa12")
youre right with the directory tool: set it up so that it captures the files in the folder where your excel should be. if there are multiple files you might have to set up some filters. once you have the correct file, as you might have guessed, throw it into a dynamic input to get the file opened.
Hello yes, feed the list of sheet names to the dynamic input tool
Dynamic input is used if youre specifying the name of your data input elsewhere in the workflow - we can use that to our advantage by using the filtering and lookup options of alteryx to find the exact names we are looking for, instead of having to hardcode it.
however, it pops out and says cannot found the file as photo.
What do i need to amend to let it work? thanks!
And also I'm not sure that what's the meaning of dynamic input, i assume the template is use to be the standard which let the original input file rename as the template? Or i misunderstanding something? Thanks a lot!!!
Hey Andy The dynamic input needs the template to help it know what the data file should look for - basically what column headers you will have, and data types. Only then you can use the data further in your flow when you dont know the exact data. Regarding your example, i assume your template file is "valid". i notice you add your full file path to the flow, you could concatenate the path to the sheetnames with "|||" inbetween, and change the action to "change full path" if youre still having trouble, feel free to send a zip file with the data and i can do a mockup for you
The Dynamic Input tool allows you to cycle through multiple input files, going through them one by one without having to be configured specifically for each file. In a sense, the Dynamic Input tool is the Input Data tool on steroids. 😁
Because my workflow was designed to feed the entire file path to the Dynamic Input, you need to configure the Dynamic Input to recognize it as a full path, not just a file/table name, like so:
Thanks for your help. However, after i tried still cannot get it done.
Let me simply explain again what I need to fix.
I have one excel, and in that excel will have one sheet. And for the sheet, we are not sure what the sheet name would be. Sometimes is Enquiry(1), sometimes is Enquiry. But the format is fix this is certain.
But what I faced is that if the sheet name changed, I cannot run the model since it would say "does not match a sheet name or the excel file is corrupt".
After clarify that, I used the model you advised but don't know why it still cannot be done.
Attached is each screenshot for the steps, could you advise which step is wrong?
(and another question is that, what I put into template file, I named the sheet name in excel "enquiry", so I assume no matter what name of the input file's sheet name, it would be ok to read. MAYBE is this step I'm wrong but not sure. How do I let it calculate no matter what the sheet name of input is? Thanks a lot~!)
@AndyHH It's worth noting that the Dynamic Input tool doesn't handle changing table specifications, if this happens then it'll fail, that route is batch macro.
If the specifications don't change then you can go down the route of what you've tried but instead of using FileName use Sheet Names
As for your initial request, your could read the directory, sort by created, take the first row, use formula tool to create sheet name path (FullPath + "|||<List of Sheet Names>"), use the Dynamic Input to read the sheets of that latest file (Change Entire File Path) , then feed into another Dynamic Input to read the sheet data
I'd image your table specifications might change but give this a go, any issues give me a shout.