My final report needs to have both string (some blank cells, followed by a “header” on row 10) + numeric values (the actual values of the documents included) in some of its columns. I have not found a way of doing that successfully till now.
I am uploading a sample of what the report should look like when exported in excel. Several columns need to be kept formatted as number because of the amounts in them, despite the “header” and the blank rows.
The adding of blank rows + header on a specific row and not on top of the table, was discussed and resolved visually here, but then these columns get formatted as “DataCell” due to the inconsistent formatting of the cells in them and I cannot export in excel:
Another problem I have is that some of these headers originally come out of the system with leading spaces, which get removed no matter what I do (tried renaming them in the end, to bring back the leading spaces which I need to keep, but it did not work, the excel export again removes them). I suppose this is for a separate topic though.
When i download it again from here, I see the same - first 9 rows are blank, header with column names is on row 10, one more blank row, followed by the actual report. The fields colored in green are the ones which need to remain formatted as “number”.
And to answer the question - this is what the final output in Excel needs to look like. The report is mapped to another tool, which looks for the header on row 10 and “knows” that the first document will be on row 12. The header respectively needs to be exactly as mapped, i.e. with the leading spaces in some of the header names.
The header names with leading spaces also have trailing spaces. Does that matter? Does the exact number of leading &/or trailing spaces matter as long as there is a leading space? Also, you never provided an input table. I used your output as input with the top 9 blank rows removed.
Yes, all spaces matter, apologies for not mentioning that: the number of spaces + leading + trailing (if any). The input table is actually the same, this is how the report comes out of the system, this is also the way it is mapped in the external tool to avoid any manual formatting of the report prior this upload.
Please identify the headers with leading/trailing spaces and the number of each. Don’t make me do it. Frankly, the best fix would be to eliminate the spaces in both the table production and the backend processing. Having these unnecessary spaces is pretty poor data management.
I took some time to count the header spaces from the original Excel file. If my regex is correct there are no trailing spaces. Does this table match what you know about your data?
I did a test on one header and its possible to add the spaces back in and write it to an Excel file. Since each header is unique the only way I know of adding the correct number of spaces is to use a String Manipulation join for each header with spaces. Let me know if you want me to continue working on this. I won’t proceed unless I hear from you.
Try this. Spaces added to appropriate headers in metanode. Data saved as “General” in Excel workbook so calculations on numeric data are possible. As I said before you’d be better off cleaning up the input and postprocessing rather than going through this agony which BTW is probably pretty brittle. Let me know if I’ve hit it.
Cannot thank you enough for your help and time! Yes it does exactly what I needed, it took me some time to figure out the settings of the “Write to excel template” node, as I had never used such before. I am still not sure about the idea there (the “Template.xlsx” thing - i created one blank excel file in the folder where i keep the reports and working files). Not sure whether i need that or i should have kept the settings as they were with “relative to” and just create one such blank (?) excel template in the knime workspace folder where the project is. Just don’t get the purpose of this Template and what is it supposed to contain if anything.
Anyway, for the output file i selected the folder where i want the final report to be generated and it worked perfectly!
Thank you once again, you’re a genius!
P.S. I agree with your comments about the structure of the data/header, however, it is not me who manages that, these are just raw reports coming out of the ERP system and this is how it generates them. So I do not want whoever needs to use this flow to have to manipulate the headers manually, copy/paste, etc, so to avoid manual intervention, we need to work with what we have from the system (not customizable unfortunately).
