How to Output Data to Excel and then use that Excel File as input (without file error)

[Janet_Samuels]

I have a workflow that does a large amount of data cleaning, joins, etc.  At the end, this creates a dataset of Invoice Sales that I'll then use for 10 or more different analyses....all using that same input.

I didn't want to have 10 or more different connectors from that same data to the 10 downstream analyses.  Thus, I output the data to Excel File I call "Invoiced Sales".  I set this up to overwrite the existing file so that when I rerun the workflow it works great. Then, I use this "Invoiced Sales" file as my input to combine with other data and do my 10 various analyses.

My problem is that I get an error.  The workflow is still trying to output data to the Excel file named "Invoiced Sales" at the same time that it is trying to read data in from the same file.  Thus I get an error "Unable to delete Excel File (file name). Make sure the file is writeable and not open for writing in another application".

Everything works if I close the containers for the 10 downstream analyses, run the workflow to output the Excel file, then close the initial container and open the 10 downstream analyses containers.  However, that is a pain

Is there a way to tell Alteryx to output that Excel File data BEFORE working on other containers that input that same file?  The "Block until Done Tool" doesn't seem to be the correct item.  Any other suggestions?

[kboeckholt · Accepted solution]

Hi @Janet_Samuels 

---

I didn't want to have 10 or more different connectors from that same data to the 10 downstream analyses.  Thus, I output the data to Excel File I call "Invoiced Sales".  I set this up to overwrite the existing file so that when I rerun the workflow it works great. Then, I use this "Invoiced Sales" file as my input to combine with other data and do my 10 various analyses.

---

Am I understanding that this is all being done in one workflow? Why would it be an issue to connect each of those analyses to the same upstream data? It seems like outputting the data and then trying to read that output into the same workflow is creating unnecessary hurdles.

That said - if you do want to go this direction, you may want to look into using the Wait a Second macro from CReW (or one of the various similar options out there) to slow down the workflow between outputting your data and reading it back in.

[SPetrie · Accepted solution]

How are you getting the Invoiced Sales file input to the 10 downstream containers? Is it 10 file inputs, or one file input that splits to the 10 containers? Why cant you take the stream you are feeding the file output and connect it to those containers instead of another file input for Invoiced Sales? The information is already loaded into Alteryx. Saving it to a file only to reload it into the program feels unnecessary.

If connecting the main flow to the downstream containers is non-negotiable, the only suggestion I have would be to split them into separate flows. One with the main flow and the other with the downstream containers..

You can either create a chained app and run them as apps so one kicks off after the other, or use the CReW Runner macro to run the first flow and then the second.

[Janet_Samuels]

Thanks -- I'll look into this.

We set it up to output to Excel so we have the Excel output for other workflows and analysis.  The issue is that within this workflow, we then keep wanting to use that data and it was getting impossible to read the workflow as everything kept doubling back up to that output (it was getting impossible to read the workflow

[Janet_Samuels]

Thanks -- I'll look into the Wait a Second Macro.

We set it up to output the data to an Excel file so we have the Excel output for other workflows and analysis.  The issue is that within this workflow, we then keep wanting to use that data and it was getting impossible to read the workflow as everything kept doubling back up to that output (it was getting impossible to read the workflow. Thus, we input the data from the Excel file.

[kboeckholt]

@Janet_Samuels I completely understand workflows getting hard to read as they get big - tool containers, wireless connections, and annotations can go a long way with keeping workflows organized.

[pmpknface]

If I'm understanding correctly, I'd break this up into at least 2 workflows.  The first ending with the output of your "Invoiced Sales" file and the second picking up after that file is created.  Using the List Runner from the CReW Tools you can run the macros one after another so that #2 will run after #1, when your file is finished outputting.  Does that help?