Hello team,
I am reading data using 3 queries in 3 different Query reader. I have one excel file with raw data as output. Everyday, this file has to be updated with new data from prior day.
I have output from 3 DB Query Reader. I used “Excel Shee Appender” but it wipes out existing tab and creates new one (basically replaces a tab), instead of “Append” to the last row…
I saw several posts that says, there is no “APPEND” feature with existing data.
I have to write to 3 different tabs.
New to Knime… so not much experience.
But was looking at options
Can I read existing EXCEL file somewhere ( 3 tabs separately ) and merge corresponding data and then write back? ( May be an overhead )
I saw some example pointers of using Python … Again no experience there either…
Thanks @mlauber71 …
I was trying that… and got some success using ‘concate’… See the screen grab… Few cons of the process.
Overhead - This is extra work on reading the file and rewriting the entire data.
Loss of format - What I originally had in those tabs, as formatting, is completely gone. Luckily, I convince the user ( as he is just using these tabs as a source of pivot )
Look at the date format. Somehow when it writes, it appends the “Time format”…
No good…
But I guess, I got this to work for now… Still looking for better options
---- Another question for the same process
Q: As you see, I have 3 Query Reader. (showing only 2 in above image). Each query has multiple UNION with DATE. It can be single day, multiple days in a row or a range of dates… How do I set up variables such that I can supply the values from outside to this workflow and each query would run for the same date parameter… ( sort of Stored Proc type )…
To actuality append data to an existing Excel sheet I see this options:
have the sheet as a data source and the pivot, charts and formats on another sheet. You would have to make sure that there is a new calculation when the Excel file is opened
use the Continental KNIME nodes to format the data afterwards. I think they are there for this purpose
export to a CSV or sqlite file and have a database import in Excel but then the file would have external dependencies
If you want just to add a few lines to an existing Excel structure there would be python. But that is not entirely straightforward.
So if you absolutely must provide format the Continental nodes might be your best option.
Date and time formats across different data bases and files is notoriously tricky. You will have to experiment on that. Excel has its own format.
Concerning the database query. I have seen examples in big data environments where it was necessary to always explicitly CAST the columns to the desired format since there were unwanted variations within the large imported files.
Regarding the other question. Yes to read the data from Excel is additional process time question is how long. The other method would be to store the existing data in a table and concatenate that with the new data from the new query.
And a side note: always keep backups of your files ready like in all professional environments since things might go wrong and you would have to revert to a former status. If you have enough space you could work with timestamp extensions to keep older versions.
@mlauber71 already provided you a lot of options and possibilities and I would definitely explore Continental nodes more in your case.
Apart from that regarding bottom question.
For this I would build a Component. Component would be built in a way that requires a user input (regarding dates and optionally additional parameters) and logic inside would be that based on input appropriate queries are created as flow variables. These flow variables are then used in each of 3 DB Query Reader nodes.
If you will have any questions/problems feel free to ask and I can create an example to see if what I just explained would work for you.
Hi Ivan,
Yes… I have adapted my process. I read in excel first ( each tab in different reader node ) and then rewrite the tab… I am going to explore reading entire excel in local knime table / or / temporary sql table and updating that table through process first…
But coming to your suggestion.
Essentially, the process I built in excel using vba is… if I supply any dates in custom text boxes, then the query uses those date range, but if I don’t supply anything through date fields, process automatically checks if it is Tue-Fri, then it does Day-1, and if it is Monday , it picks range from Fri-Sun dates…
This does not work on holidays… That’s when I have to put specific dates knowing the gaps…
So that’s what I want to replicate in knime.
If you can help me get started, I’d appreciate it
You could load a list of dates that are holidays or special days and use that. For most countries there are lists of these days and if it runs for a whole year it might be possible to provide the list in advance and use it.
but then I can’t do anything…
It has message “Error: Node “Table Row to Variable” not available from extension “KNIME Core””…
I see “table row to variable” in Node Repository…
I clicked on the Date Input and I think i see the form input process.
Since I am doing on my work computer, I can’t upgrade any versions …
I am using 4.0.2
if that is the case go inside Component (ctrl + double click or Right click on it and go Component --> Open). In there you can replace existing Table Row to Variable nodes with one from your Node Repository. Then it should work.
