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. Any suggestions?

Input Data from Excel - There were Errors: Input Data (4): Corrupt File: XML parse failure

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.

Capture a.PNG

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.

Capture b.PNG

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.

Any suggestions?

Workflow

Accepted answers

DavidP

Hi Robert,

The app attached to this post (specify excel range in input tool.yxwz) does what you describe. It's the same app I attached two posts ago where I explained the formula

Replace([#1],"$", "$A7:AT") for the Action Tool.

It's also pretty close to your app.

You do have to select an input filename for the input tool when you set up your workflow. It's just a placeholder and at run-time the action tool updates the filename variable in the input tool with the value obtained from the file browse tool and loads that file.

When you choose your file with "Select your Excel file", the app also asks you to specify a Sheet. You have to select Sheet1 (or whatever Sheet you want to load). The formula in the Action Tool then inserts the range A7:AT into that filename with the Sheet name specified before it is sent to the input file to ensure that only the specified range is loaded. If you select List of Sheet Names, the formula as it is currently written won't work.

I don't think the dynamic input tool is the way to go here, as the normal input tool does what you need.

specify excel range in input tool.yxwz

All comments

DavidP

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.

Input tool define a range.png

robertfishel

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?

DavidP

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.

DavidP

Here's a picture

Input tool define a range with app.png

robertfishel

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?

Capture c.PNG

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335