@Trying_to_learn i don't mind. I mean is to share, ask and learn. Sorry to answer a little late i was trying. But about the spreadsheet it changes sometimes i will have maybe 100 different hours and commands to send. This picture is what i have to test but as i said the format on the spreadsheet is not important, I just need that this works.
And about the https://flows.nodered.org/node/node-red-contrib-spreadsheet-inactually i have like a month testing Node-RED so i didn't know much about nodes. Also I'm using it with Raspberry so i have some issues with the vnc conection and i don't have to much control to download the spreadsheet or create one. So basically I'm trying to do everything from the dashboard. And use the SSH from my laptop.
You can save the file as a csv from excel and than use a ftp client like filezilla https://filezilla-project.org/ to put it on the raspberry. This would make working with it a lot easier for you.
Are column B & C both commands?
Johannes
Automation involving Excel spreadsheets is always a rubbish idea. People will move thing, merge cells, change sheet name, add rows, delete columns, you name it. It becomes a headache and when it breaks, it's always your fault.
The situation you're in - I've seen many times.
Often, using Excel is because people don't know any better or are forced due to internal business inflexibility.
I've had to do similar automation in the past where I couldn't get a mainframe download of data or a direct connection to a database.
My first approach is always "where does the Excel data come from, can we get that to transfer directly into a TCP endpoint?database?restAPI? Can we have that data auto dumped to XML/JSON/CSV (note how CSV is last choice ever. CSV is the worst format for future extensibility). (PS I'm scratching the surface here)
If there is no possibility of automation, I begin exploring the front-end approach...
E.g. is the data manually entered into the spreadsheet? If so, can people change to inputting the data into a web page instead? If so, build a node red dashboard or other web page & have users enter this data into it. Then you can send that data anywhere (even auto populate a spreadsheet for the Excel lovers)
Lastly, I would probably do Excel macros to have to data transmitted from the spreadsheet to a database using macros & ADO.
An alternative to that is...
- Create a HTTP endpoint in node red that will receive data
- In Excel, Using VBA, create a macro that gathers the data and POSTs it to the node red endpoint (hint -
Set hReq = CreateObject("MSXML2.XMLHTTP"))
As much as what @Steve-Mcl said is true and the problems with people renaming sheets, or adding columns/rows (or what ever)....
(Sorry if it has been already mentioned)
As a last resort what would be wrong with having a mere text file with CSV values?
But, I think @JGKK has a very good solution/option.
What you could do is go a bit further with it and put in the option to export the entries so they are saved in case of a crash.
Not too difficult. Though I get it may be a bit above your skill set just now: It would be a good idea to have a bigger picture of what the end is to be.
As a last resort - absolutely nothing. In fact, if CSV is your only option, then its the best option!
As a first choice, CSV is a terrible idea.
If dumping to file is your only choice, chose an extensible format if you can (like XML or JSON or other before resorting to CSV - you will thank yourself the day the CSV data changes & your application still works)
As i recently replied to someone privately (tweaked for public forum)...
The info wasnt just for xxx. It was for you and every future viewer
Anyone can clearly see xxx is struggling but that doesnt mean we shouldnt arm him with information - even if he doesnt get it. For example, he could take this to a developer, or speak to IS team about automation possibilities, but without knowing possibilities, he would continue down this path. As an obviously capable developer yourself, I'm sure you can appreciate not many people are able to break problems down into small, achievable chunks. Even fewer people are inventors - they are a minuscule portion of the world population - everyone else uses ideas from what has been done before (including me) so withholding information doesnt really help anyone - it hinders them.
(mostly my option of course
)
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
