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.

[GT]

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??

[GT]

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).

[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?

[GT]

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, ...>"

[GT]

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.

[GT]

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.