Hi,
I’m very new to Knime.
I have been tasked with building a workflow whereby I need to add data from one spreadsheet into another.
The data from spreadsheet A needs to input into specific cells into spreadsheet B.
The two files are excel.
Can anyone please assist ?
Any help would be greatly appreciated.
Thanks
@jasonk2022 First of all welcome to the world of KNIME.
Yout task sounds not too complicated.
Just read both Files using the xls Reader Node into two Tables. Then start with your magic.
In case you provide more details about this magic we can further support you
I agree its not too complicated however I’m new to Knime 
Spreadsheet 1 : Date of Purchase (A1) Amount (B1)
01/01/2022 (A2) 10 $ (B2)
01/02/2022 (A3) 22 $ (B3)
The above has 2 columns and 2 rows.
Spreadsheet 2 : Has 3 columns and 28 rows
I would like to create a workflow whereby I can : 1) Input date (cell A2 spreadsheet 1) into cell B15 spreadsheet 2 2) Input Amount (cell B2 spreadsheet 1) into cell C15 spreadsheet 2
I understand starting the workflow with 2 excel file readers however I do not know how to transpose the data from spreadsheet 1 into specific cells of spreadsheet 2.
Thanks
The Excel Cell Updater node will likely be what you will want at the end once you have pinpointed what you want written and where it will go in the main workflow process.
1 Like
OK thanks. I’ll remember that going forward.
Any advice as to how I would proceed once I loaded the 2 files using the excel reader nodes ?
The concatenate or joiner nodes may not be suitable for what I require.
Thanks
I have too many questions to offer suggestions without more info. Can you upload a workflow with some dummy data that matches your file structure, and include some description of the methodology that you would use to pinpoint the value that you would want written from spreadsheet 1 to spreadsheet 2?
Food for thought - You do not necessarily have to combine the files in KNIME first in order to use the cell Updater, unless cross reference is required to generate the values in spreadsheet 1 that need to be written to spreadsheet 2. You can just read in spreadsheet 1, isolate values and use excel Updater to write to specific cells in spreadsheet 2.
1 Like
file test 1.xlsx (11.2 KB)
file test 2.xlsx (10.5 KB)
As per previous post :
File Test 2 : Date of Purchase (A1) Amount (B1)
01/01/2022 (B2) 5 $ (D2)
01/02/2022 (B3) 10 $ (D5)
File Test 1 : Has 3 columns and 28 rows
I would like to create a workflow whereby I can : 1) Input date (cell B2 file test 2) into cell B13 (file test 1) 2) Input Amount (cell D2 file test 2) into cell C13 (file test 1) 3) Input date (cell B3 file test 2) into cell B14 (file test 1) 4) Input Amount (cell D5 file test 2) into cell C14 (file test 1)
Thanks
Are you just starting to move from Excel into KNIME? I get the feeling that you are approaching the KNIME workflow process with a “one off” excel manual approach. While I could work in a vacuum and build something that manually targets row and column placements like this, the workflow will be needlessly rigid and prone to errors vs a rule based approach with more context which will be somewhat dynamic. If you build workflows based off of rigid column and cell references then it will break if the column order, number of rows or sort is different the next time this excel file comes to you.
The better way to approach problems in KNIME is to think of “why” instead of “where”. Why are you are targeting those specific cell values? Is there a logic that you can define to help explain which value should be targeted?
Unless you are plugging values into a formula based Excel template that is used for on the fly manipulations, then you are likely approaching this wrong. My guess is that you are trying to combine 2 sources that cross reference each other somehow. If that is the case then you are much better served reading both sources in and then combining them logically within KNIME instead of trying to write preset cell references back and forth. (file 1 is blank, so I can’t really tell from here what is going on)
2 Likes
No worries thanks.
Column order, number of rows etc will remain constant.
'Im simply trying to transpose data from one file into the other and was hoping there would be an available solution or help.
I’ll keep trying
1 Like
If you end up struggling with it, then you can upload a file 1 with some info in it. That will probably give forum users more context to make sure that we don’t provide you with a rote solution that isn’t in your best interest.
Unless this is just a one off task (which I doubt, or you would just copy and paste it in Excel), then “how” you approach getting to the right output is just as important as the output. It is always helpful to give a brief description of what you are trying to accomplish from a practical / contextual standpoint on these forum posts as well.
Thanks.
One file has the data. The other file is blank.
I need to transpose the data from the file with data into the specific cells I referenced.
I believe I have specifically detailed how I require the workflow to function.
As I said I am new to Knime and am trying to learn.
Ok. I will throw together a workflow that places those values into specific spots on the blank file.
I assumed that you meant cell “D3” instead of “D5” since there are only 2 rows, but you should be able to easily adjust it to point wherever you need.
Excel Updater.knwf (50.0 KB)
This should perform the rote task you asked about.
is it always a specific cell or do you just want to append data to the next “free” row.
Excel writer can do this without any complicated logic
br
Thanks a million.
I need to update to the latest version of Knime.
I will keep you posted but I really appreciate the help.
1 Like
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.