Counting and Recording Numbers in Excel using a sensor

[bwolfgang]

Hi, I'm not sure if this is the right place for this post, so if it isn't let me know and I can move it.
This is a project for a potato farm. We have test plots that we eventually plan on harvesting a collecting data on. We have 10 conveyor belts that we will run these potatoes on. What we will need is to count and weight them. I already have the weighing part figure out, but the counting part is giving me trouble. Basically what I'd like to do is have an infrared sensor on each belt that counts the number of potatoes in a belt and puts that number in an Excel spreadsheet. After doing some searching it looks like this might be able to be accomplished using an Arduino, but I'm not sure as I've never used one. If anyone knows if this can be done and/or how to do it that would be helpful.

 

[Doug3004]

To write directly into an Excel spreadsheet (or a Word doc, or an Access database, or an Openoffice/Libreoffice document, etc) from another program, you need to use a COM library. A COM library is basically an interface file that lets one program "use" another program directly in real-time; either on the same PC or over a network connection.

If you were writing this in Microsoft's Visual Studio, you would need to install the desired Office Primary Interop Assemblies you wanted to use:
https://msdn.microsoft.com/en-us/library/15s06t57.aspx

That is rather complicated, but isn't necessary since there are existing COM libraries around that others have already written. They may not let you use 100% of the Excel functions, but they can at least generate basic spreadsheets directly in Excel format.

This page talks about using an Arduino to do it directly:
http://www.instructables.com/id/Sending-data-from-Arduino-to-Excel-and-plotting-it/

This page talks about doing it with Python:
https://stackoverflow.com/questions/13437727/python-write-to-excel-spreadsheet
Note that some people here suggest using a tsv file format, instead of a COM library.
,,,,,,
A tsv file is a plain text file that represents a spreadsheet. It uses the [tab] key to represent moving to the next-rightward column, and the "enter" key (CR/LF) represents moving downward in lines.
https://en.wikipedia.org/wiki/Tab-separated_values
A tsv file does not write directly into any spreadsheet format, but it is a format that any spreadsheet program can easily import.
It does not require that the final program (Excel) be present, and it is a lot simpler to write than using a COM library.

 

[bwolfgang]

Thanks for the reply. So if I understand what you're saying, I have multiple options on how to get the information into excel. If I had 10 separate sensors could I have each of those connected to one Arduino board (or something similar) and have that send information to the PC?

 

[Doug3004]

It would be a lot easier to just use one Arduino board for all 10 sensors, I think.
The Indestructibles page above is probably closest to what you would want to do.

If you wanted to connect 10 Arduinos at once, then your PC software would need some way to tell which Arduino was which. That's not impossible, but it is a significant hassle for not much benefit here.
And anyway--one Arduino can easily watch 10 different sensors. It just depends on how frequently you want them checked.

How many potatoes per second do you expect to be counting, for all 10 conveyor belts?

The specs claim a much higher number----but those who have tested it say that the Uno can only really do about 10,000 analog inputs (reads) per second at the most. The digital rate is much higher but would require a bit different input circuit.

An arduino doesn't have 10 analog input lines, but you don't need 10 lines. You just use 1 analog line, and 3 digital lines to control a 4051 multiplexer board ($2).
https://playground.arduino.cc/Learning/4051
This lets you connect up to 16 inputs to 1 single Arduino pin, and it can 'connect' to any of the 16 different inputs when needed. It can switch inputs way faster than the Arduino can read them, so it isn't going to slow the Arduino down at all.
http://hackaday.com/2017/05/17/a-few-of-our-favorite-chips-4051-analog-mux/

 

[bwolfgang]

I don't think the number of potatoes would be an issue, as they wont be going through at a fast rate. If I had 10 sensor hooked up to 1 board, wold it be able to count each sensor separately? Would I be able to tell each sensor when that particular run is completed and then have it send that number to the PC? Also I know that coding would be involved to get this set up. Is it something I would be able to do with little to no coding knowledge / is there online tutorials on how to do this specific task?

 

[Doug3004]

If I had 10 sensor hooked up to 1 board, wold it be able to count each sensor separately?

Yes.

Would I be able to tell each sensor when that particular run is completed and then have it send that number to the PC?

I would just send a signal to the PC for each potato detected, so the PC would be keeping track of the total.

Also I know that coding would be involved to get this set up. Is it something I would be able to do with little to no coding knowledge / is there online tutorials on how to do this specific task?

I don't know if using a COM library is practical if you don't have anyone around who is at least familiar with computer programming. And someone who knows anything about electronics would be a help too.

I'm only somewhat familiar with using COM libraries, but I don't have MS Office anymore to even try with that.
And the Parallax page says their COM library is written for Win98, and was only tested on Excel 2000-2003.

I could write a Visual Basic program to produce a tsv file pretty easily I think, depending on exactly what you want it to do. You would just need to install Visual Studio Community (it's free) and then build the project to get the executable file.

I'm assuming that you have [multiple batches] of potatoes to count, and you also have 10 different conveyor belts that they travel on? So you would want something that could definitely produce a multi-line tsv file, and not just something that can count 10 different values?

 

[Doug3004]

It looks like he's gone...
Anyway, for anyone else still watching:

(...Assuming that you have nobody around who can write a COM library...) I think a TSV file is way more practical in the long run than using a COM library. The TSV file is a bit more hassle to use but it isn't dependent on any particular spreadsheet program, and the TSV file format won't ever need updating. It worked 20 years ago, and it's going to work 20 years from now.

Also maybe Visual Basic is not the best choice, as it is proprietary.
Visual Studio is "free" right now, but it wasn't always that way in the past and may not continue to be that way in the future. Java is a better choice overall since it is free to use and is likely to remain that way. (and it's cross-platform too)