Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Trouble getting data lines from MS Analysis tool with Alteryx

EH_NS
5 - Atom

I am attempting to create a reconciliation formed from monthly downloads using the excel add-on Analysis.  I have twelve excel workbooks with 1 tab each all in the same format, all with one tab, and all in the folder.  I am using Directory to access the folder and then Dynamic Input to try to get all the lines of data together.  Whenever I run the workflow I get the "No sheet specified" error i have seen posted by many others.  I have followed the fixes that worked everywhere else but it doesnt work for me.  My question is does it have to do with the Analysis tool?  Does anybody have experience trying to use Alteryx with data produced by Analysis for Excel?  Is there a workaround?

Thanks for any help.

6 REPLIES 6
mbarone
16 - Nebula
16 - Nebula

Your "FullPath" from the directory tool needs to have a sheet name.  Format needs to be like this for the path feeding into the Dynamic Input tool:  "C:\Users\mbarone\Desktop\Address.xlsx|||Sheet1".

 

If the sheet names are the same, then it's very easy - just put a formula tool after the Directory tool to update the FullPath.


If sheet names are different, then it gets a bit more complex; let me know.

EH_NS
5 - Atom

Thanks for the reply.  The process i am trying to complete will have an agent downloading the data into a folder monthly.  There will be 12 individual excel sheets with 1 tab each downloaded to this folder.  All will start with the same name but the end will be "_Jan 2020", "_Feb 2020", and so on.  Trying to get all the files i pulled in thru the use of Directory to be combined into one dataset.  And then each month a new file would be added to the same folder and the workflow would pull in the new file and add it to the rest.

mbarone
16 - Nebula
16 - Nebula

You're going to have to generate those tab names somehow for it work like you want.


You might need a batch macro that reads in each Excel file with the setting "read in a list of sheets".  Or if there's some way to generate them with a formula.

 

In the end, the Dynamic Input tool needs the FullPath to be of the form [path to excel file.xlsx|||Sheet1].

EH_NS
5 - Atom

Thanks for your help.  Maybe i will just solve the monthly reconciliation with Alteryx since that will only be one file being compared to one file.  That will be a victory at least:)  Was hoping to have a process with no manual manipulation by the agent that could be used for the whole years worth of data.

mbarone
16 - Nebula
16 - Nebula

Oh it could be done.  It's just something that's not simple.  The idea would be to read in all Excel files with their sheet names and create a list that looks like this:

FullPathSheetName
fullpath\excelfile1.xlsxsheet1
fullpath\excelfile2.xlsxsheet2

 

You would bring one of them into a canvas, change the "Select Excel Input" option to "Import only the list of sheet names".

 

And set the Input Tool's option "Output File Name as Field" to "Full Path".  You now can put them together to create your full path with sheet name.  Then convert that canvas to a batch macro (make sure to configure it to read in all files and auto config by name).

 

You then put that batch macro in your main canvas and connect  your directory tool with all the excel files.

 

If I have time later I'll put together a sample module and macro.

mbarone
16 - Nebula
16 - Nebula

Take a look at these (attached) and let me know if you have any questions.

Labels