Temperature control/data logging with excel

I am building a room ambient air temperature controller and I need to log some data.

I have the minute average temperature (to control temperature spikes), the actual temperature, and the humidity which I would like to store on my SD card every minute and then graph with excel.

But to my knowledge excel doesnt support multi variable lines (so I can't write all of the above variables to one line). So my options are:

1.) Write to multiple files, one for each temp/avg temp/humidity and import the data seperately. (Maybe not the best approach to write to 3 files a minute?)

2.) Use another program to strip the one data line file into 3 seperate files (time consuming)

3.) Find a way for excel to import multiple data from one line (assign columns to different values in a 'string')

##########################################################################################################################

PS I would also like to record if the heater or the fan is on, im thinking ~ symbol for the fan and # for the heater. But then do I use a symbol (or not a symbol of nothings on) on every line? Or could I get away with say:

temp: 19 # //heater on
temp: 20
temp : 24 # //heater off
temp: 26 ~ //fan on

etc..

Also if anyone is an excel wiz is there a way I can e.g. set the line color (of the graph) to red when the heater is on, blue when the fan is on, and black if neither are on based on whether a line of data contains a # or ~?

Any advice or direction would be great! Thanks in advance.

Your items 1 & 2 are nonsense. You only need address item 3.

I think the simplest approach is to use PLX-DAQ, which easily adapted for use with Arduino. It is an Excel macro and therefore enables you to enter data direct into Excel - on screen and in real time. All you need to do is format the output with comma separation as you would for any export in CSV. No need for a clock, date and time stamping is done at the PC end. PLX-DAQ can also be used to display real time graphs.

You can also send the identical material to to a terminal programme like RealTerm, which saves it to CSV for ultimate transfer to Excel. Again, the clockstamp can be done at the receiving end. I don't know how either of the above can be used long term but broken into short term files. They probably can but I use an on-board SD card instead, and change the file name daily. Needless to say, this procedure does require an on-board clock.

If you parse the data just like a .csv you can import the data as a text file. It will give you the option to separate the data using camas. You can make a header in the text file, and have the data stream below it. What are you logging the data to, an sd card?

I use HID and Excel for realtime input. One could use an Arduino Leonardo for HID and edit my Excel template to do similar:
http://forum.arduino.cc/index.php?topic=135623.msg1027748#msg1027748

The formulas in the Excel do all the parsing.

Ray

Thanks for all the answers guys, since I am using an SD card I dont need realtime logging. I just saved as .csv and the data seems to align perfectly so thanks for that I don't know why I didnt think of it.

However I need to store some information temporarily on the arduino while the SD card is not present, and somehow detect if the SD card is present (Doesn't have a card detect pin). What size of an array can I store for 3 ints and comma seperators?

So a data line of 16,19,58 is 8 bytes correct? 1024 bytes of RAM is 128 8-byte entries. So at most I can have 2.13 hours of logging once per minute? Is an array size of 128 strings ok? Or should I try for a more conservative 2 hours at 120 entries for 960 bytes?

Just wanting to verify my memory calculations are reasonable? Also can I just check if the file opens to detect the SD? Is this a reliable way? Is there a better way? Remember I don't have a card detect pin.

Hi

You don't need to keep the commas in the array, just the int values. Also you have the EEPROM available to you too so that would be my choice over a array.

Geoff

Ok if they aren't commas then they would have to be spaces anyway, otherwise how would excel know to seperate them? I'm glad I found out I can just save as .csv for excel and it puts the data into different cells already. I dont want to have to write another program to run the data through just to prettymuch add commas/spaces :confused:

Also I thought of an array because I can limit the size at declaration, and the sram has 2kb whereas the EEPROM only has 1kb. But I admit I don't know how much space my other variables take up yet, but they shoulden't change size too drastically.

Is writing to the EEPROM just like writing to a notepad? Or I see you have to write each byte, in which you would probably have to create an array to temporarily store them anway for use in a for loop wouldn't you? Sorry I am inexperienced.

Sorry you misunderstand my suggestion there. If you want to store the most data in the space available to you, keep the values only and reconstruct the text file by expanding it with spaces, or commas, only once the SD card becomes available again. Each value as an int takes 2 bytes of RAM (regardless if in an array or EEPROM) however as text a value will take up a 1 byte for every character it comprises. So a 2 digit value followed by a comma will be 3 bytes rather than the two you'd have just storing the value.

On your 2nd point, SRAM might be larger, but remember your program is running in it. EEPROM on the other hand is all there for you to write data to, and you'd be able to store up to 341 of your 3 integer value sets before you ran out.

Writing to the EEPROM is quite different to writing to the notepad. Of needing an array anyway, you'd only need one to store 3 values so that's no more overhead than your 3 values anyway. If you load it into a data structure containing those 3 integers you could use this method on the Playground which simplifies things some. You will need to keep track of how many records there are to read back from EEPROM, but another way is to write the whole EEPROM with a known value your records can never contain before hand, and check when you read back that this value is not what you've found. When you hit a record that's just those filler characters you know to stop reading back, and erase the EEPROM again for next time.

Hope this helped. In the first instance though aim to get it going without the queue for dealing with the lack of SD before adding that complexity and you should be fine.

Cheers ! Geoff

May be this program can be an option:

I didn't use it myself, but used some other similar products from Aggsoft. They can work as you described.

Thanks for the help! I already know each integer takes 2 bytes. Thats 6 bytes for the ints and 2 bytes for the commas, 8 bytes.

strykeroz:
On your 2nd point, SRAM might be larger, but remember your program is running in it. EEPROM on the other hand is all there for you to write data to, and you'd be able to store up to 341 of your 3 integer value sets before you ran out.

The EEPROM is 1024 bytes right? So 1024/8=128 value sets. I dont understand how you get 341, thats 1024/341=3.002 bytes? And I thought the SRAM because yes it has more space and you have to declare the size of an array at initialization anyway, so I can just see how much memory my sketch takes (slightly more than half) and then assign some (not all) of whats left for my array? Or would that slow down the processing or something?

Also as for recombining the integer values and adding the commas when writing to the SD... How would I temporarily store the information without commas? Woulden't using a two dimensional array use >= the memory of storing it as a string anyway for example? Or do you mean store it like 282355 then split it to 28,23,55 for writing to the SD?

Finally, you said create an array with filler characters presumably so that if it starts writing to the SD while writing to the EEPROM that the EEPROM data is saved to the SD card and then deleted up to the filler characters? Why not just check the data entry string length to see if its greater than 0? Since an empty string will be "", then it wont take up a character byte for an empty position in the array?

Sorry if I am misunderstanding you again, thanks for the input though.

edit: Is the playground link for writing the data bytewise? So thats how you can store the integer values without a comma? Does that mean its being written like this?

28 23 55 27 22 54 ? for two 3 int entries? Only in bytes?

syphex:
Thanks for the help! I already know each integer takes 2 bytes. Thats 6 bytes for the ints and 2 bytes for the commas, 8 bytes.

The EEPROM is 1024 bytes right? So 1024/8=128 value sets. I dont understand how you get 341, thats 1024/341=3.002 bytes?

My maths was based on your values never being negative, and also never being more than 127...ie one byte each, or 3 bytes the set - 1024 / 3 = 341.
If you use integers, it's 1024/6 not 1024/8...and you get half that or 170. In my suggestion you'd not be saving the commas to the array, or EEPROM.

syphex:
And I thought the SRAM because yes it has more space and you have to declare the size of an array at initialization anyway, so I can just see how much memory my sketch takes (slightly more than half) and then assign some (not all) of whats left for my array? Or would that slow down the processing or something?

What you're saying sounds reasonable, but it's not nearly as easy as that. As your program runs there are some things like your global variables, including this array if it's declared at global scope, that remain in RAM however each time a function is called those variables in the scope of the function are created, then the memory deallocated after the function exits. You also have RAM used by the execution of the code itself which is difficult to estimate. The image size of the compiled code is not how much space it takes when running, but how much space it takes when stored on the FLASH RAM of the microcontroller.

syphex:
edit: Is the playground link for writing the data bytewise? So thats how you can store the integer values without a comma? Does that mean its being written like this?

28 23 55 27 22 54 ? for two 3 int entries? Only in bytes?

Yes, and that's just how the array would store it too. No comma delimiters are needed for the machine to read its own RAM.

Hope that helps,
Geoff

Is it possible that during the process, one can change the sheet of excel?

I believe you can do this by keyboard shortcut within Excel, therefore you may be able to do this from Ardujino by sending the appropriate string.