Tool Mastery | Output Data

[JordanB]

This 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 Michael 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

• Output to your Database Connections

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

• 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

• 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 output 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 filename as a field. De-select to keep your output data the same as the end of your workflow

See the attached workflow, as well as the hyperlinks above (different sheets; different 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.comif 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.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French
German
Japanese

[JordanB]

• Output to your Local and Network Drives

• Output to your Database Connections

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

• 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

• 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 output 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 filename as a field. De-select to keep your output data the same as the end of your workflow

See the attached workflow, as well as the hyperlinks above (different sheets; different 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.comif 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.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French
German
Japanese

[JordanB]

See the attached workflow, as well as the hyperlinks above (different sheets; different 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.comif 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.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French
German
Japanese

[NJT]

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

[NeilR]

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

[NJT]

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! 

[Harshmore]

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 

[NJT]

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.

[Harshmore]

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

[NJT]

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.

[Harshmore]

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. 

[NJT]

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 🙂 

[LaurenU]

@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.

[MichaelSu]

@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

[NeilR]

@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.

[SimonCLoose]

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?

[NJT]

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...

[SivaAlteryx]

Thank you very much

[srilakshmi123]

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

[NeilR]

@srilakshmi123 to write to the same folder your workflow is saved in you simply don't include a path in the Output Data tool configuration, just the file name.

[MeganBowers]

@srilakshmi123 to have the output saved to the same folder as the workflow, you can use a relative path. For example "./outputfile.csv." It also works to have the file path just be "outputfile.csv," but I like the style of the ./ to make it clear that you are setting a relative path.

Also, you can use the same structure to write to different folder levels: "../output.csv" will write to one folder level above where your workflow sits, and "../../output.csv" will write to two levels above.