Hi All,
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!
Maybe you have different kind of information for the same column… other option is make a “string manipulation” where you can set / convert the information from a string:
You can make a new column from a string where you can convert types as the image above. The types are: string, to boolean, toDouble, to Int and to Long… just it, There aren’t other types.
You can try to save at the CSV file and then, when you open at the Excel program, the program will try to convert the information to be read, try it too!
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?
Seeya!
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
Ultimately, a sharepoint list is updated with the data, but at this point my KNIME is not authorized to connect to it, so I’m using excel until then.
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.
We’ll, I hope that helps you… tks
@denisfi good Idea. These are the Excel Nodes by Continental. They might help @szityu60 solving the problem.
Thank you @denisfi and @mlauber71 I’ll take a look at the Continental nodes.
