Create one Excel Output File with several sheets?

horth007

Hi All

I have a problem to create one excel fiel with three sheets. Currently, I do the following

Creation of three Excel workbooks via formula and the following name definition:

1) German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [First_Pay Date]+" Germany Scope and Summary.xlsx||Scope Check summary"

2)German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [Pay Date]+" Germany Details.xlsx|Details"

3)German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [Pay Date ] +" Germany ERROR REPORT TO CHECK.xlsx|Error CHECK"

Finally threee excel wokrbook wil be created, with specific named sheet. But I would like to receive one workbook with three sheet instead of:

German Austrian Distribution\Output Verification Reports\"

+DateTimeToday() + " Pay Date " + [Pay Date ] +" Germany Summary.xlsx with the sheets 1) Scope check summary 2) Details 3)Error CHECK

The Block Until Done is not working, as the Information are prepared via different steams....

How the output file must be configured to ensure this?

Thanks a lot....

workflow.png

Output

Accepted answers

All comments

grazitti_sapna

@horth007 in order to do so you need to have :

a) 1) Scope check summary 2) Details 3)Error CHECK names in one column suppose the sheet name column consists of all the specified sheet names at row level.

b) configure your output tool as follows

I hope this helps if not then I would suggest you provide a sample workflow so that we can help you out.

Thanks!

horth007

Thanks, it is one file now,

But the problem is, the formula results are different in the headers and when using the union tool, the three sets of header will go to each sheet.

grazitti_sapna

@horth007 is it possible for you to share a sample workflow?

horth007

@grazitti_sapna I made a sample input data, based on the 3 sheets I need. let me know if this sample workflow helps?

InputGermany Details.xlsx

InputGermany ERROR REPORT TO CHECK.xlsx

InputGermany Scope and Summary.xlsx

Test.yxmd

grazitti_sapna

Hi @horth007, I have gone through your workflow and have made some tweaks but unfortunately, there is no way to create a dynamic file name unless creating a separate file as you are doing now.

One way I can tell you is to create a file name manually as per the required format and pass sheet name 1 column as in the attached file to get the desired output that is all the sheets in a single file

I hope this helps!

Thanks!

Test.yxmd

horth007

@grazitti_sapna Thanks for your help, I will try if easier if I will consider three outputs instead. But in case your help is very much appreciated.

grazitti_sapna

@horth007 you are most welcome.

HomesickSurfer

Hi @horth007

Export the attached workflow package.

Sample formula expression to output 1 file with input sheetname in the same directory as input, prefixed dates to filename:

If this works for you, please like, share and/or accept as solution.

FileGetDir([FileName])+DateTimeToday() + " Pay Date " + [First_Pay Date]+" "+"Germany Output Verification Reports"+Replace(REGEX_REPLACE(FileGetExt([FileName]),'[[:punct:]]|[\$\+<=>\^`\|~]',''), "xlsx", ".xlsx|||")

Germany Output Verification Reports.yxzp

horth007

@HomesickSurfer Thanks for this, but for me it is not working. I only attached a simplified test workflow as example. In the complete workflow, there is no input file to prepare the file name.

I saw you added to the input file the option "5 Output File Name as Field Full Path to generate the File name for the further consideration.

But in the workflow, the considered data are from a "summarize" or "union" tool, therefore, I cannot add the full path to have the proper names.

I only have the output folder to the related server "\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\"

Then the file name should be created dynamic

+DateTimeToday() + " Pay Date " + [Pay Date]+" Germany Summary.xlsx"

to consider three sheets in the workbook:

|Scope and Summary.

|ERROR Report to check

|Details

Not sure if this would to complex as I can also go with three workbooks

HomesickSurfer

Hi @horth007

Use the below just before the output tools.

Configure the output tools as:

For:

\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\2022-05-18 Pay Date 2022-05-16 Germany Summary.xlsx|||Scope and Summary

"\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\"
 
+DateTimeToday() + " Pay Date " + [First_Pay Date]+" Germany Summary.xlsx"
 
+"|||Scope and Summary"

For:

\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\2022-05-18 Pay Date 2022-05-16 Germany Summary.xlsx|||Details

"\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\"
 
+DateTimeToday() + " Pay Date " + [Pay Date]+" Germany Summary.xlsx"
 
+"|||Details"

For:

\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\2022-05-18 Pay Date 2022-05-16 Germany Summary.xlsx|||ERROR Report to check

"\\euro\dfs$\Digitalization\Alteryx Tools\German Austrian Distribution\Output Verification File\"
 
+DateTimeToday() + " Pay Date " + [Pay Date]+" Germany Summary.xlsx"
 
+"|||ERROR Report to check"

horth007

Hello @HomesickSurfer Unfortunately, not working and only one sheet will be written in the file.

But I also do not have the option, "Overwrite sheet or range", as I am still on Alteryx version 2019.4.8.22007. Not sure if this could be source of the problem?

However, when the output folder is empty, I can use output option "Create New Sheet". Then on workbook will several sheets will be prepared.

The only downsize, I cannot re-run on the same date without error (or deleting the fiels from the output folder) and these bak files will be prepared in addition:

2022-05-18 Pay Date 2022-05-16 Germany Summary.xlsx.bak

2022-05-18 Pay Date 2022-05-16 Austria Summary.xlsx.bak

Not sure if there is any possibility to solve this?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703