Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to input data regardless of the sheet name? / file name

AndyHH
7 - Meteor

Hi Community!

 

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). 

Many thanks if you have any feedback for these!!

10 REPLIES 10
Nanoq
8 - Asteroid

Hi Andy

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.

  1. 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
  2. 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")
  3. 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.

    Let me know if this helps
AndyHH
7 - Meteor

Hi @Nanoq ,

 

Thanks for your quick reply. For q1. since in dynamic input i still have to select a sheet, in this step should I also select "Import only the list of sheet names" or ?

And as I can use later, may I know the logic of using this tool? Thanks a lot for your kind help!

Nanoq
8 - Asteroid

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.

Peachyco
8 - Asteroid

I'm assuming that:

  • There is a directory that contains the Excel files that we need.
  • There is only one sheet in each Excel file, or all of the sheets in each Excel file will be used.
  • All the sheets have the same data structure.
  • We want to take all of the data from these Excel files and output them as one dataset.

 

Then maybe something like this would work (just change the target directories/files in the Input Data and Dynamic Input tools):

Untitled.png

AndyHH
7 - Meteor

@Nanoq @Peachyco Thanks for your help,

 

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!!!2021-10-15_15-09-55.png2021-10-15_15-09-41.png

Nanoq
8 - Asteroid

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

 

Peachyco
8 - Asteroid

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:

Untitled.png

AndyHH
7 - Meteor

Hi @Peachyco @Nanoq !

 

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~!)

 

 

matt_dix
5 - Atom

@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

 

Input.png

 

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

 

Input.png

I'd image your table specifications might change but give this a go, any issues give me a shout.

 

Matt

Labels