Overwriting data in an excel file while skipping rows

[akshatrajj]

We have an excel template which we want to populate. The template has a few rows which need to be skipped while the data is loaded. We are able to do this by defining a name for the header row (key_2) using the formula tool in excel. As shown in the image, the data load has to start from row 5 and key_2 has been defined for the column name in row 4. Then we use the same name while configuring the output tool in alteryx and we are able to load the data in the template. The output option selected is 'Append to existing sheet/file' in the output tool.

What we want to achieve next is we want to reuse the same template for the next data load. To do the same, we deleted all the records inserted in the first run and executed the workflow again with the same configuration of the output tool. We, however, noticed that the next load started from the where the last record ended (row 5 in this case).

How can this be achieved if we want the new records to be loaded from row 5 itself?

[Qiu]

@akshatrajj 
The new version of Alteryx is having the option of preservingg formatting, which suits your requirement here.

[akshatrajj]

I tried with the same options (Append data to existing sheet as output option) and with 'Preserve formatting on Overwrite' option as checked but I'm getting similar results. The data is appended from the next row from which the previous data was deleted (row 13 onwards now).

Also, I tried by changing the output option to 'overwrite existing sheet' for the second execution while keeping the preserve formatting option checked. I get the following error upon executing the workflow:

'encountered existing data beyond the specified range'

[akshatrajj]

Hi @Qiu , I haven't been able to figure out yet as to how to resolve this. Do we have any mechanism for the same?

[Qiu]

@akshatrajj 
I am so sorrry since I think I made a mistake.

Can you change the option below?

[akshatrajj]

Hi @Qiu ,

I have already tried that. Please refer the following section in my previous reply:

Also, I tried by changing the output option to 'overwrite existing sheet' for the second execution while keeping the preserve formatting option checked. I get the following error upon executing the workflow:

'encountered existing data beyond the specified range'

[Qiu]

@akshatrajj 
Sorry about that.

Then would it be possible to upload your workflow?
or you have many columns that exceed 'Y'?

[akshatrajj]

@Qiu ,

The workflow is simple with just two tools. One is the text input tool, whereas the other is the output tool. For the output tool, I have tried with both the configurations- 'Append data to existing sheet' as well as 'overwrite existing sheet'. However, as I have exhausted my alteryx trial, I can't upload the workflow.

Could you also explain what is meant by- "or you have many columns that exceed 'Y'?" in your previous reply. Also, please let me know if any further clarifications are required related to the workflow.

[akshatrajj]

@Qiu ,

Attaching the workflow(Demo Job.yxmd) and the output template (Test1.xlsx) here for reference. Please let me know in case any further clarifications are needed.

[Qiu]

@akshatrajj 
Thank you to reply you late.