.xls Output File

[sanketkatoch05]

Hi,

I am using a .xls file as an output file for one of my projects. The issue here is that, I'm using a reference file for the Output tool and that file has multiple sheets, I want to overwrite the data from Alteryx to one of those sheets. I tried selected 'Overwrite Sheet' and 'Delete Data and Append' options but every time Alteryx is creating a new sheet by adding a suffix as '1' to the sheet name. 

For eg. : if the reference file has a sheet name as ABC then Alteryx is creating a new sheet with the name 'ABC1'. I want Alteryx to overwrite this ABC sheet only. Lastly, I cannot change the format of the file to .xlsx or .xlsm since the Client has strictly asked to stay with .xls format only. 

Please guide me on what I should do here. 

Thanks. 

[mceleavey]

Hi @sanketkatoch05 ,

The following steps should be taken:

1. Tell your client to stop using Excel.

2. Tell your client to stop using .xls

Until you have won these battles, you can do the following:

Create the full path to the file with the sheet name pipe delimited in a formula tool as so:

Write the file out on the first run creating the new sheet.

Then change the settings in the output to be as follows:

The settings at the bottom will overwrite the settings above and will then overwrite the sheet.

This is working for me, I hope this helps.

M.

[sanketkatoch05]

@mceleavey, 

Haha, I wish I could. 

Coming back to the solution, I tried the way you said but it is still creating a duplicate sheet. I'm attaching the wf, please amend the changes. 

Thanks, 

Sanket

[atcodedog05]

Hi @sanketkatoch05 

I am getting only one sheet. If Alteryx is creating duplicate sheets that on each run it should create new duplicate sheets but for me its not. Please check and let me know.

Excel file:

Hope this helps : )

[Jean-Balteryx]

I tried as well and I get the same result as @atcodedog05 !

[sanketkatoch05]

I'm attaching the actual test file, can you check if you're able to overwrite 'Sheet1' data? Also, the output should have both Sheet1 and Sheet2. [Sheet1 having the Alteryx Data]

Thanks, 

[sanketkatoch05]

Any help on the above? @atcodedog05 @mceleavey 

[apathetichell]

It's already Friday night @for @mceleavey and hopefully  @atcodedog05 is already asleep... I don't have much experience with Alteryx and .xls files with pre-existing sheets but my hypthosis is that Alteryx is having trouble with keeping the existing data in .xls. It's effectively rendering your data and deleting the non-Alteryx produced sheet.

Alternatives:

Output via python/R (I use R more and have success with .xls files and R)

Input the other sheet into Alteryx (perhaps by using my nifty .xlsx/.xls/.csv all sheet reader) and then output as needed to both sheets.

[atcodedog05]

Hi @sanketkatoch05 

I was able to replicate this issue. Honestly, I don't know what's different with your file. I was not able to make it work. I have attached the workflow.

Output File:

@mceleavey and @danilang any thoughts on this?

@apathetichell you can also share him a workflow for this using R. It would be an alternative and great opportunity to learn  🙂

[danilang]

Hi @sanketkatoch05 

It's definitely an issue with the specific file.  For some reason, the original sheets have some property that is stopping the normal behaviour.  Unfortunately, I can't determine what the specific property is.  If you create a new .xls and use that one, the data in the sheets gets overwritten properly.

One way you might be able to address it is to create a new .xls file which has the data from the old one. but isn't a simple copy.  Save the original as .xlsx and then save the .xlsx as a new .xls file.  This should remove any unique properties.  Try your workflow using the new one.  If it works, try and convince your client to use the new file going forward.   

Dan