Multiple files with different schema into different sheets of 1 output file

Hi,

I have multiple input files with different schema that i want to combine into different sheets of 1 output file.

I used the union tool to join all the input files and multiple formula tools to tag each input such that they have a dynamic file name (today's date) and source (one source = each sheet).

However, with the union tool, i see lots of columns with empty fields for those columns that are blank for that particular source. How do i fix this so that i can get multiple sheets in one output file with a clean format (ie. having data on each column)?

Also, how can i use block until done tool to write more data to that same output file after i have consolidated the input files and did some manipulation/data cleansing?

Output

Accepted answers

All comments

grazitti_sapna

@Sohdaw , Please check the below post and let me know if it is helpful. If not please share the sample workflow so that I can understand your problem statement more clearly.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-output-multiple-tabs-within-a-single-Excel-file-based-on/m-p/508185#M105325

Felipe_Ribeir0

Hi @Sohdaw

Try the attached workflow. The idea is to use a batch macro with a data cleansing tool to write each tab without the other tab headers.

Dataset to be written:

After running the workflow

Data written to output.xlsx|||Sheet1

Data written to output.xlsx|||Sheet2

Wf.yxzp

Sohdaw

Thanks @Felipe_Ribeir0 , this looks to solve my query! You are amazing!

But now i got another question... how do i configure the batch macro to fit my workflow?

- For my inputs i used a union tool to join them all up. How should i configure my Macro Input - using File input?

- I used [FileName] = [User.Directory]+"Output\Forecast"+DateTimeFormat(datetimetoday(),"%Y%m%d") + ".xlsx|||"+[Source] instead of [Output] = output.xlsx|||Sheet1. Should i still have the filter tool with True/False?

- How should i configure the action tool based on my [FileName]?

Felipe_Ribeir0

Hi @Sohdaw , you are welcome!

Have you tried to just replace the inputs by your own inputs? and change the Output column to FileName column here:

I believe that by doing this, it should work! The filter is just to filter each sheet for each iteration, and then select the non null columns with the data cleansing tool.

If it does not work, please share a print of the error.

Sohdaw

Hi @Felipe_Ribeir0 great! I missed out on updating the batch macro under questions to [FileName] but now that i have done so i'm one step closer!

Now getting this error on my batch macro. I have 4 iterations to run.

I suppose it is an error with the Action Tool #7 on the mode? My sources are eg. Apple / Cucumber / Pineapple / Orange.

Felipe_Ribeir0

Hi @Sohdaw

Try this new version, i believe that you just need to replace the inputs

Wf.yxzp

Sohdaw

hi @Felipe_Ribeir0 ,

Sorry this is getting more complex. In replacing the inputs to the batch macro i see it run 4 times - i have 4 sheets and hence there were 16 messages just for that Macro tool, though the output file still only had 4 sheets.

The output also still has all the columns - some of which are empty and dont have data for that particular sheet hence not needed for that sheet.

Can I stick to the old version but can you explain with a bit more detail on how that error came about and how i can fix that?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703