Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Tool Mastery | Output Data

Alteryx
Alteryx
Created

Output Data.pngThis article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Output Data Tool on our way to mastering the Alteryx Designer:

 

Once you have started a workflow within Alteryx it's hard to think about leaving! However, if you feel like that time has come, Alteryx makes that transition easier than Micheal Phelps winning Gold in the Olympics. The Output Data Tool is used to write the results of your workflow to any supported database or file formats; Alteryx also offers the opportunity to output directly to Tableau Server, and Power BI. Using the Output Data Tool, you can:

 
  • Output to your Local and Network Drives 

Pic 1.png

 

  • Output to your Database Connections

Pic 2.png

 

 

  • Create Alias connections which allows you to update your database password in all workflows where that Alias exists

Pic 3.png

 

  • Once you have chosen your output format you will have the opportunity to adjust the Options section. Within here you can choose the output method you want, number of records to output and a field map to name a few

pic 4.png

 

  • And.......as if those output formats were not  enough! The Output Tool has the option of outputting multiple files at once based on a certain field within your dataset! For example, you have run your whole analysis for every region within your database. You now want to output those records to different sheets within the same Excel file, or output them to completely different files all together. You can tick the 'Take File/Table Name from Field' and adjust the drop down menu to output to different files or sheets. For any file format other than Excel you can use the 'Change File/Table Name' to output to different files. For Excel, this same option will output to different sheets, however if you want to out to different Excel files you will need to add a formula tool and use 'Change Entire File Path' option in the dropdown menu. 'Keep Field in Output' will keep the file name as a field. De-select to keep your output data the same as the end of your workflow

 

Pic 5.png

 

See the attached workflow, as well as the hyperlinks above (different sheetsdifferent Excel files), for working examples. Workflows built in Alteryx Designer 10.6. 

 

By now, you should have expert-level proficiency with the Output Data Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

 

Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Attachments
Comments
11 - Bolide

What's the trick for having an output save to the same folder your workflow is saved in.

Alteryx Community Team
Alteryx Community Team

@NJT if you don't preface the file name with a path it will write to where the workflow is saved...

NeilR_0-1576281417560.png

11 - Bolide

Oh cool I had seen this trick in the cert test and they had .\ and it saved it wherever your workflow was saved and I had never seen that anywhere before. Great trick! 

5 - Atom

Hi...2 things that i need help with

 

1. When i have the output format as yxdb the workflow is done in about 2 mins. However if i change it to xlsb (since i need output in excel file) then it takes 35 mins. Is there a way to do it quicker? Changing it to Xlsx helps a bit but then the output file size is so huge that its becomes unusable.

 

2 I have selected overwrite file in the output option but it still gives me error (file already exist) and i am having to manually delete the previous output file before running the workflow.

 

Thanks

HM 

11 - Bolide

Can you output to csv instead of Excel? Sounds like you're pushing the limits of data for Excel, I don't think anything you can do on the Alteryx side will speed that up as it's an Excel issue slowing you down. 

 

Have you tried auto field before your output to at least use the least amount of space for the data you are using? Another option could be saving it locally and then copying the file where you want it saved that might speed it up a bit unless you're doing that already.

 

Alternatively I'd look at what you could possible remove from the output or if it's possible to summarize the data before output at all.

 

I'm sure an Alteryx engineer could answer this better than me and would love to see what you're trying to do so they can make it more efficient. Another option you could write it to the Alteryx database format which is always the fastest output and then in a second workflow just read in the Alteryx database and output your xlsb format to wherever you need it to go.

5 - Atom

WOW!!! that was quick and the CSV option worked perfectly ...thanks !!!

 

Can you plz also look at my second issue:

 

2.  I have selected overwrite file in the output option but it still gives me error (file already exist) and i am having to manually delete the previous output file before running the workflow. Below is how i have defined the output details 

 

Path\filename abc|||tab name_Data

11 - Bolide

That’s odd, when you change to CSV output it shouldn’t have a path with a tab name like that, it should just save as Path\Filename.csv and overwrite it if you don’t change the name of it. Perhaps the folder you are writing to you only have read permission and not write permission? 

Test out saving it to your desktop to see if it’s does the same thing.

5 - Atom

Its working fine for the CSV output but i have few other smaller output which i want to get in XLSB but currently having to delete the previous XLSB output before running the workflow or else i get the error. 

11 - Bolide

I'd never tried writing out as an xlsb, it is really a lot slower even on smaller files, I wonder what goes into that. I just tried writing a file out and it overwrote the existing file just fine. What version of Alteryx are you running? We might have to tag in an engineer here @JordanB I can't replicate your issue. I'm using version 2019.1 (stuck in enterprise world can't get an upgrade). Maybe @LaurenU has an engineer friend at Alteryx that can help, I've met a couple at user group meetings, but for the life of me now I can't remember their names 🙂 

Alteryx Community Team
Alteryx Community Team

@NJT hmmmm, I want to say @DiganP and @MichaelSu  have attended LBC meetings in the past. But my very smart friend @NeilR might have an answer and he is well connected.

Alteryx
Alteryx

@NJT @LaurenU @Harshmore 

 

Do you have the 2010 Access Driver Installed (https://www.microsoft.com/en-us/download/details.aspx?id=13255)? According the documentation (https://help.alteryx.com/current/designer/data-sources) this is necessary in order to read, write to a .xlsb file type.

 

Also in the output options in the Configuration pane, please ensure that you've selected 'Overwrite Sheet' rather than 'Create New'

 

Thanks,

Mike

Alteryx Community Team
Alteryx Community Team

@Harshmore First of all, I've never written to an xlsb file before, so to others seeing this message, I discovered that to enable this functionality in Alteryx, you first have to install the Access 2010 driver here (I chose the x64 option). To your second question above, as long as I didn't have the file open in Excel, and I select the Overwrite File configuration option, I'm not having an issue overwriting the file. If you're still having issues I'd recommend contacting customer support.

NeilR_0-1592502047050.png

 

5 - Atom

This is helpful, but I want to know how I can take 1 workflow and output different end points in that workflow to a different excel sheet within the same workbook. How can i do that?

11 - Bolide

Hi @SimonCLoose I'd recommend moving this conversation over to the community conversation and check out this resource and the comments to it, if you don't find your answer there, post a new thread asking your question and if possible provide a sample workflow with dummy data for what you are attempting or screenshots of your settings you're having issues with.  

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Output-to-multiple-sheets-within-an...