When I am writing an excel file with the Excel Writer node, the source data is string, but when I open the Excel file, the data format is ‘general.’ I need that data in ‘text’ format, but I haven’t found any solutions yet. Could anyone help, please?
Thank you in advance!
Hi denisfi, thank you for your reply. I’ve tried your suggestions but no luck. I found though, that Excel’s default type is ‘general’ and applies this to every cell when the written excel file is opened, even if the column type in KNIME is integer, for example. I guess I will have to chenge the type manually every time I write the file.
Will you automate it? Do you need to use excel at first option? If not, try to put the information at a database and with excel data connection, you can get the information too, just made a refresh every time that you need a update data, what do you think about it?
Hi @szityu60 , do you have python installed and/or know python?
A quick web search found some python code which uses openpyxl to achieve this. You would need to install openpyxl
e.g. at the command line run: python -m pip install openpyxl
The attached workflow demonstrates writing a .xlsx and then using openpyxl to set the format of columns A and E (from the second row onwards) to “Text”
This could be extended and turned into a configurable component (I may do that some time) but maybe you can use this with a little simple modification to adjust to your needs. openpyxl can presumably be used to set other formats (e.g. date/number) if required. My “convenience component” (open file or folder) has been tacked on the end simply to enable quick viewing of the resulting xlsx
Hi @takbb , thank you for the suggestion. Unfortunately, I can’t install openpyxl on my company computer. However, in the meantime I came up with a workaround, though it’s not as elegant as yours. I created an excel file with columns set to the needed format, and I update it with the ‘Excel Cell Updater’ node.
Hi, I’ll just say that… the Knime have some XLS nodes to manipulate the information.
But you need to set the range and tag some informations to match for yout transformation. If you can’t see those nodes, you need to add the extensions/pluggins for knime. Just search for “excel” term and add the nodes. Then, restart the program and write at the repository for these extensions.
it works to XLSX too, but in my opinion, it was limited… for files, Knime could be more expansive, more details, options for file I/O… like pentaho (hitaki) does.