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

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.

Input

Output

Accepted answers

All comments

KGT

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

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.

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?

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.

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!

Data is getting filled in more than 1 sheets based on the category as explained in above post. So your method will not work in these scenarios.

KGT

OK, there's a lot of trying to work out the issue here. Something about multiple sheets getting in the road but I can't see why these suggestions aren't working.

I'm guessing that:

You are using the option "Take File/Table Name From Field" on the Output tool,
The field you are passing in will define the sheet/range ("......xlsx|||Sheet1$A2:G100").
The data table is being constructed in the python tool and then passed back to the Alteryx workflow.
And it's not preserving dropdowns when you have "Preserve Formatting" ticked?

You said that you are "using Python for data ingestion in input template file", does this mean that the python tool tries to put data into the template, or it constructs a table, or it constructs an HTML element?

Can you please state what you are doing and what is not working, as you keep just responding with generic responses that don't answer the questions posed. Are the dropdowns inside the range you are outputting to and are the values being inserted valid for the dropdowns??

I am trying to fill the data into defined excel template which has multiple sheets and based on two input files "Allclasses.xlsx" and "class_sheets_mapping_,csv" file data is getting filled in template file as defined in python script. The excel template has predefined picklist(drop down) when the data is filled, all the existing data validation is removed. You can check the provided python script and based on Alteryx workflow snapshot. Also, i tried conditional formatting via python script but it didn't work out (just for your information).

python script for alteryx tool.txt

Conditional Workflow.yxmd

KGT

Am I right in understanding that the Python script directly creates the workbook, not an output tool? It looks like the only output ion the nodes is an error log. Is it the output tool that is writing the file, or is it the Python tool?

python tool is writing in the excel template file.

KGT

OK, so you are not looking for why the Alteryx output tool keeps removing the dropdowns, but rather why your python code is removing the dropdowns?

The code in the routine "write_sheet_with_headers" will clear all the cells inside the area that is about to be written to.

From your Python code:

Behavior
--------
- Data starts writing at row 4 (preserves template content above).
- Thin black grid borders are applied to the data region written.
- NO conditional formatting (no colors).
- Per-PO vendor gate skips POs that already have vendor MTR files in VENDOR_MTR_DIR.
- A sheet "Mandatory Missing" is added per PO with columns:
[Table, PO Number, Remarks] where Remarks = "Tag Number=<value>; Missing Columns: <col1, col2, ...>"

yes, i tried different python script but output remains the same and dropdowns is removed ever-time.

KGT

To be clear.... this is not an Alteryx problem!

The python code is removing the dropdowns, and so either change the python code to something else (you may have a hard time with this), or output a table from the python tool using Alteryx.Write(df,1) and then output that using the output tool and preserve formatting option. Or re-write the entire operation in Alteryx.

any alternate method to achieve the same ?

KGT

If it was me that had to do it, I would re-write the whole process in Alteryx Tools. You've got to figure out what the script is doing, but it looks like a bunch of cleansing, so shouldn't be too hard if you need it. Test that the operation you need works for your problem first, Can you generate a table and input into the template while keeping dropdowns first, and then embark on getting all the steps right.

Or, go back to the person who wrote the code and ask for the table to be output to one of the Output connectors as well as written to the template. You can then test what you need.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703