Compare values of two columns of a large excel file using Node-Red

#1

Hello,

I have played around Node-Red for getting excel file from the disk and displaying its values in the HTTP response object. The source Excel file contained only 10 to 15 records. I have an excel file which contains sensor values of different types (like one column contains heart rate and other contains activity etc.) in 30 different columns and each column contains more than 100,000 values. So, I used this new file which is sized about 35 Mb to display the specific column values of it and when I press deploy, the Node-Red goes on deploying for a while. When I check my ram memory, it is getting filled in task manager of windows which in turn crashes Node-Red.

What I understand and assume is Node-Red is loading all values of that specific column into RAM memory and because of it RAM memory gets filled and node red is forced to close which results in the crash of Node-Red. What am I trying to achieve actually is that I want to select two different columns of this excel file and each of these columns contains sensor values from different sensors. I, then, want to repeatedly compare this each pair from every row of excel values and display result if the value of one column is greater/smaller than the other (some threshold I will set).

In short, loop through all pair of values of two columns of excel, compare each pair and show result based on the comparison. I will be very relieved if someone could help me out. I am using it for learning purposes and want to work on a real project if I get success on it.

#2

Which is why excel isn’t a database…

What you could do is go via a database such as mysql.
There are tools to import excel files into mysql. You can then use an SQL query to get the data you want from the db into Node-RED

#3

Ok @ukmoose Thanks for your response. I will try to convert it into MySQL Database but still the main thing that i want to achieve is still pending. I will put query to pick columns from database but how do i compare each pair of values and display result from them?

#4

If you are trying to learn, give it a go and see how far you get.

When you have got the data from the database how to compare it should be fairly easy (or you can do it in your sql query (try googling to find out how))

#5

This is, of course, true. However, assuming you are using a reasonably up-to-date version, you should be able to very easily do this in Excel itself. Check out PowerQuery and if you can't quite work it out, knock up an example workbook and post a question to Stack Overflow.

While putting the data into a db may make processing quicker, this is usually because you dedicate more memory and CPU to the job. The overheads of a db can make this kind of thing a lot slower if the device you are using is memory constrained.