Write excel data file with column with drop down list.

Hi

I was wondering if there is a not complex way (or any way) to write some data in knime as a xlsx (excel) with added column that contains drop down list. I would like to have excel file that has column where for each row I can choose a predefined option.

For example:
File is composed of 6 rows of some numbers.

ID
123
245
678
999
1234

I would like to add column “extra info” with pre-defined options like: yes, no, not sure.
Now when someone gets this excel when he clicks on second column for each row he will have option to choose.

ID. extra info
123. No
245. No
678. Yes
999. Not sure
1234.

Thanks

Yes that is possible with the help of Python and openpyxl

image

You could also check out this thread

6 Likes

Thanks :slight_smile: Can you help me to adapt this to my work ? I divide tasks upon some workers:

KNIME_project_dropdownlist.knwf (46.7 KB)

I am noob to Python - sorry :frowning:

Is’t there any other way to do it with some standard nodes? I heard that there is a community extension continental or something like that - but did not find a solution.

I do not know any continental extension that could do what you want.

https://www.knime.com/community/continental-nodes-for-knime-xls-formatter

1 Like

Unfortunately there isn’t. Can you help me adopt your concept to my workflow?

@89trunks I am not entirely sure what you mean. I checked your workflow and do not understand why you are using the loop. I have adapted the Python code and added some more comments for clarity.

There is no need to loop thru the lines. You would just tell Python openpyxl that the validation rule you have defined should be applied to the area B2:B6. The number of lines is now created dynamically from the data and given to Python by Flow Variable - you could also just set the range by hand if you would know it.

2 Likes

Thank you for help - I get the gist of it. The comments really clarify some things.
BTW: Why I use loops ? Because I have one big data and I have to split it into small files (depending on how many people will be given the tasks) and I wanted each small files to have this drop down list. So I always do it in loops - but never before had to add this drop down list.

Thanks again you are life saver for me.
Do you recommend some site that explains the Python code just like you did by adding the comments ?

Thanks again :slight_smile:

1 Like

It is a real good solution and works fine.

1 Like

If you want to loop thru several files you could think about something like in these examples or you would have to do some other combinations of nodes before and after the python node. Might involve some planning.

There are tons of python courses and introductions out there but ones that do really explain what is going on and why are hard to come by. And then there are very advanced courses with statistical stuff that first throw a bunch of formulas at you and then let you figure out the code by yourself basically.

For machine learning and python the best site and books I have come by is

Which gives you working code samples and explainaitions taylored to the task at hand. Of course there is always more to learn and it might not be a general introduction into Python.

About that, one idea if not the most creative one is to take the Kaggle self study course:

And then the actual NSA has produced a python course that seems pretty good especially covering a lot of relevant issues, but I have yet to see someone bring that into an actual jupyter notebook instead of a scanned PDF.

https://www.google.com/search?q=nsa+python+tutorial&ie=UTF-8&oe=UTF-8&hl=de-us&client=safari

And depending on how you like to study, a video course from Udemy or an old fashioned book might be suitable for you.

3 Likes

Again thanks for help :blush:

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.