Writing to the same Excel file in Alteryx.

One common output style from an Alteryx workflow may be to write different structured outputs to the same Excel file upon different worksheets.

This can be problematic to say the least.

The reason for this is that once Alteryx opens the excel file to upload the 1st sheet, Excel will lock the file making it un-editable by other processes.

There are two ways which we can get around this, one is through using the ‘Parallel Block Until Done’ tool available within the Crew Macro pack.

I’ve found problems with this, which is why I have highlighted a 2nd way, through the R tool and the ‘openxlsx’ library.


Using the Parallel Block Until Done tool.

The Crew Macro pack has been designed by Alteryx employee Adam Riley. There are a tonne of great tools within the pack, and best of all they are well supported and documented, and regularly patched.

The Parallel Block Until Done tool is a development of the standard block until done tool that comes with Alteryx. It is designed with the purpose of writing different streams to different outputs in a set order. In that it outputs all records from input 1 to output 1, before writing the records from input 2 to output 2.

A great description of the Parallel Block Until Done tool can be found on Adam Rileys chaosreignswithin site.

The workflow below (which is also attached here), highlights how you should look to configure your workflow for writing to the same file but different sheets.

parallel.png


The second option, as mentioned previously, makes use of the R tool in Alteryx and is a little more complex to set up (however, I have never experienced an error when writing to Excel in this way).

1st things first, we need to ensure we have the Rtools package installed on our local machine, if not, you can download it from here.

This is because the second package we need, ‘openxlsx’ requires the zip execution file that exists within the Rtools package.

We now must also download the ‘openxlsx’ library and copy it into our default library location for Alteryx which is something like: C:\Program Files\Alteryx\R-3.2.3\library.

That or we can install it directly within the R code.

Next we need to create a stream for each individual data table we wish to write into the R tool.

r

Now we need to create our R script.

1st things first, we need to declare the location of our ZIP execution file within the Rtools package.

Sys.setenv(R_ZIPCMD= “C:/Rtools/bin/zip”)

Now we need to open the ‘openxlsx’ library.

library(openxlsx)

Next I will declare a short name for each of my datasets.

Orders <- read.Alteryx(“#1″, mode=”data.frame”)
People <- read.Alteryx(“#2″, mode=”data.frame”)
Returns <- read.Alteryx(“#3″, mode=”data.frame”)

I will now create a list of the datasets I wish to pass into my excel file, and their sheet names.

List_of_datasets <- list(“Orders” = Orders,”People” = People,”Returns” = Returns)

Note the value in quotation marks represents the sheet name, whilst the value after the = represents the data frame you will be passing to that sheet.

Finally we will use the writexlsx() function to write our data to a specific excel file.

write.xlsx(List_of_datasets,file=”C:\\Users\\Moss Ben\\Documents\\My Tableau Repository\\Datasources\\10.0\\en_US-EU\\Sample – Superstore New.xlsx”)

direct.png

Now once you run the workflow successfully you will see that a new excel document has been located at the given file path.

excel2

excel.png

One thing to note with this second method is that special characters will automatically be replaced with full stops.

The workflow for this second method can be found here.


And there you have it. Two ways to output data to multiple sheets through Alteryx.

Ben

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s