Preserving Drop-Down Lists in Excel Template When Populating Data via Alteryx

[GT]

Dear all,

I am currently working on generating output into an Excel file that contains predefined drop-down lists. However, when I populate the template using Alteryx, the drop-down lists are removed in the process.

I would greatly appreciate any guidance or solutions to help preserve these lists during data insertion.

Thank you in advance for your support.

[KGT]

Is this using a method to "Preserve Formatting" on Output tool when outputting to Excel, or is it just a straight data output?

[GT]

 just a straight data output. I am using python for data ingestion in input template file.                                                      

[KGT]

OK, so a straight output Tool with default configurations will overwrite the entire sheet with pure data.

If you output to a range, then you may be able to preserve the output around the range.

However, there is an option on the Output Tool when outputting to XLSX to "Preserve Formatting on Overwrite (Range Required)". This is the setting you want to use and test. I'm not sure how it works with Dropdowns, but I would expect that they are preserved with that setting.

Note: If you are outputting that Table and expecting to Preserve the Formatting, that may be an issue, as that data element is already wrapped.

Take a look around the community for outputting to Excel and there are many many posts around different nuances with this. 

[GT]

Sorry for late response. Preserve the formatting is not helpful in my scenario. 

[KGT]

OK, why?

I've just noticed that both screenshots have an error on the python tool, is that a real error, or one that goes away on Run?

Are you saying that when you use the Preserve Formatting option then it doesn't preserve the dropdowns?

[GT]

Regarding Python tool , error goes away when the workflow is run and second thing regarding drop down : it is not preserved when selected, My concern is that i fill the information in multiple sheet therefore i cannot execute sheet selection in the output excel file.

[KGT]

OK, you may need to provide more info.

So, you are outputting to a range in Excel, and that range has dropdowns in it, and even with "Preserve Formatting" enabled, the dropdowns (Data Validation I presume) are being removed? Is that the issue?

Is the entire range formatting being overwritten? Or just individual elements such as dropdowns? Is the text being output, a valid option for the dropdown?

I'll note, that I'm not an expert on preserving formatting as it's not something I generally do, I keep reporting separate, but I am somewhat of an expert on Alteryx and so am pretty good at breaking down how the software works. Can you provide some example, even a screenshot if need be, as I'm assuming if you haven't posted it yet, that this has too much company info in it, but a mock-up example of what's not working would be better.

[GT]

Dropdowns are being removed completely. Its a problem for me as i have to fill the data into excel template and not to change initial formatting. Input for dropdown is provided in another excel named as AllClasses.xlsx and mapping is taken from class_sheets_mapping_.csv and template file in which data is imported is MTR for PO_VendorTemplate.xlsx which consists of multiple class wise sheet, when the data is imported from allclasses file with the help of common attribute from class_sheets_mapping file to identify which sheet to fill the data and then data is filled in template file. this complete procedure is done via python and only certain of value is taken which as you can see filter is applied for removing unnecessary data flow. please suggest if any alternate workflow for same can be prepared. Also finally based on column name "Purchase Order Number" each separate output excel file generated and named same as PO number.

[dreldrel]

Hi, you may want to try to use an Excel template with data written into a named range and tick preserve formatting on the output tool. This shall help you to preserve your dropdown list. If this helps, please like the post and mark it as the solution. Thank you!