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.
Hello my fellow Alteryx enthusiasts. This is my second post on the discussion board.
I have created a workflow that I am running as an application with the magic wand button. The application is really simple, with a File Browse Tool, an Action Tool and a Input Data Tool.
The data that I am inputting is from an Excel .xlsx workbook which contains data on the first sheet. However, the first six rows of the worksheet contain header information, row 2 in particular being one long merged row that goes from column A to column DM. Row seven actually contains individual column headings, so I have set the input tool to begin reading from seven. However, I get an error when trying to run the application stating that there is an XML parse failure and the file is corrupt.
If I open the workbook, and select from column AT (which is the last column with actual data) all the way to column DM, and delete those columns, then save the workbook, the application will input the worksheet no problem. So I am wondering how to get Alteryx to input the worksheet without having to open it and delete a lot of columns on the far right of the worksheet.
It is as though Alteryx is detecting data in columns AU through DM, even though there is nothing in these columns, and I am wondering if that is because of my merged row 2, which stretches all the way from column A to column DM. I would think that telling the input tool to begin importing data at row 7 would skip past row 2 and its being merged, but this does not seem to be the case.
With the Input tool when loading Excel files, you can specify the range of data you want to load. Have a look at the highlighted bit below. You're specifying that you're loading data from Sheet1 starting at cell A7 to column AT.
Since you didn't specify a number after column AT, all rows of data to the end of the sheet will be loaded.
Of course, you'll have to specify in the Action tool to only update the relevant bit and leave the defined range.
DavidP, thank you for the quick response. I am trying to follow along. With regards to the action tool, what exactly do you mean about specifying the tool to only update the relevant bit and leave the defined range? Right now I have my Action tool set to Update Input Data Tool (Default). Should it be something else?
When you run your App, the value from the list box updates the value in the Input tool via the Action tool. Since it's an Excel file, it asks you to specify the Sheet name and when you pick Sheet1, the value that is sent to the Input Tool looks like this:
K:\Analytics\David\Test\VAT Sales - June 18 - Austria (Local Currency).xlsx|||`Sheet1$`
but we want it to look like this:
K:\Analytics\David\Test\VAT Sales - June 18 - Austria (Local Currency).xlsx|||'Sheet1$A7:AT'
In the config window of the Action Tool, the default action is Update Input Data Tool. You need to change this to "Update Value with Formula" and select the line File - value...
Then at the bottom, click on the 3 dots next to formula - a new window opens. In the Expression Window, type Replace( and double click on the #1: (File Browse) at the top
Continue typing: , "Sheet1$", "Sheet1$A7:AT")
Your formula should now look like this: Replace([#1], "Sheet1$", "Sheet1$A7:AT") - click OK. (You could also just copy and paste this formula into the Expression Window and click OK)
When you run you App now, it will send the correct value to the Input Tool, which should then only load the range you specified.
Thank you DavidP. I really close to getting this to work. My issue now is just with the fact that I want to be able to read in different Excel workbooks, not the same one every time. I was trying to us e a Dynamic Input tool to accomplish this, but still working out how the workflow should look, and what setting to use.does this look right?
If you're happy to select the Excel file and the Sheet Name to load at run-time, you can just change the formula to:
It will then apply the range to whatever sheet you select with the Browse Tool at run-time and load that sheet.
If you're looking to dynamically allocate which sheet to load or load more than 1 sheet, you can use <list of sheet names> and a dynamic input and possibly a batch macro.
You'll need a formula tool in front of the dynamic input tool to construct the file path for each of the Sheets you want to load and also the defined range. If all the sheets don't have exactly the same schema, you'll have to write a batch macro that will load then one by one.
Ok, in that case below is probably more what you're looking for.
The app uses a folder browse. The folder can include sub directories. If there are any other files in these folders that you don't want to load, you have to pick your wildcards so that it only returns the list of files that you want to load. Once you have the list of files you want to load and their full path, you can use a formula tool to modify the filename to include the Sheet and range you want to load from each file, which then feeds the dynamic input tool. Provided all the Sheet1 Schemas are the same, the dynamic input tool will load them all.
A couple of things to look out for:
- when you configure the dynamic input tool, make sure you choose Change entire file path as the action under "Read a list of data sources"
- when you choose a template file, if your range does NOT include any headers, make sure you tick "First row contains data"
I've attached the sample app shown above for you to play with.
DavidP, while the information from your last reply will no doubt come in handy for me in the future, I don't think it is quite what I am looking for. The way I intend for the app to work is, once the user opens the app, a message box appears that looks like this:
The user would then navigate to the folder location containing one Excel workbook (which has one sheet containing data on Sheet1). After selecting the workbook, which could be name anything .xlsx, the app then reads in Sheet 1 with a range of A7:AT(lastRow).
Right now, the way my app is working when the user selects the workbook and runs the application, it is pulling a list of sheet names from the workbook, and not actually looking at Sheet1, range A7:AT(lastRow). I thought maybe the dynamic input tool was the answer to my problem, but now I am not sure if it is.
The way things are currently, I am getting "A file must be specified" from my Input data tool, even though I thought the file was being specified by the File Browse/Action Tool combination. Here is what I have right now:
Please don't give up on me, I think it is close to working. Not sure what that one step is that I am overlooking/missing.