Best way to output data points for use in spread sheet?


#1

I have an application where I am recording 32 data points that are stored in variables (both inputs and calculated) when certain target conditions are met. There are a total of 11 combinations of various target conditions that make up one test. I have 32 tables (one for each data point) with 11 elements (one for each target conditions) to hold the variables. When the test is complete, all data from the test run is displayed on a dedicated window in PacDisplay then sent to a printer.

The data is not a continuous flow of data and the test cycle is started by the user and my total 1 or 2 cycles per hour. There is no need for the controller to maintain any historical test cycles so the previously sent data can be cleared at the beginning of the next cycle.

What would be the simplest way to get this data into an excel spread sheet?

I have played around with Ben’s email chart, is there a way to generate the data as an attachment then have it send it?


#2

We sort of do this in the opposite direction with FTP, but it should work if you :

Set your communication handle using SetCommunicationHandleValue(“file:w,filename.csv”,comm_handle_tag);

Open the communication with OpenOutgoingCommunication(comm_handle_tag);

Make sure it will be comma-separated with SetEndOfMessageTerminator(comm_handle_tag,’,’);

Then I think you should be able to add the table contents with TransmitNumTable(table_length,0,table_name,comm_handle_tag)

Then close communication using CloseCommunication(comm_handle_tag);

And finally, you can send the file using FTP or TCP by opening a separate comm handle. It may be necessary to use strings instead of floats, but I’m not sure on that.


#3

Hi Guys,

A couple of options/other posts to ponder:

How to get data into a google doc spreadsheet via IFTTT (click here)
How to re-format trend data for your spreadsheet.

Also, the PAC Control “Send Email with Attachments” command might be of interest here too. Here’s another example that might have way more stuff than you need, but hopefully I put enough comments in the code that it’ll all make sense, especially the email part.

This Comm Handle Overview might be helpful, especially [URL=“http://www.opto22.com/site/documents/doc_drilldown.aspx?aid=2979”]this link to 1642: File Management & FTP Tutorial (even if you’re not using FTP, check out the file section)

Hope that helps!

-OptoMary

p.s. On the TransmitNumTable command mention above, keep in mind that will send [I]binary[/I] data, which is not what you normally have in a csv (comma-separated variable) format, which is normally ASCII human-readable text. I touch on this concept a bit in the Comm Handle Overview mentioned above, but I’d be happy to dive into that more if that would help… The TransmitStringTable might be more appropriate. And like SeanT mentions, be sure to use the SetEndOfMessageTerminator after the comm handle is Open and before you transmit!


#4

Thanks for the help guys. Mary, since the TransmitNumTable command sends data in binary form, would the best practice be to convert the number table to a string table then transmit that?

I prefer the idea of building the file on the microSD card then sending the completed file as an email attachment or accessing it through the network rather than constantly opening and closing the connection while the file is being built. It seems more stable and reliable. Am I correct in thinking this?


#5

Good questions!

[B]FIRST QUESTION: [/B]binary vs. text/string:

[B]Short answer:[/B] It depends on your situation/preference.

[B]Longer answer:[/B] Some of the considerations:

[B][I]The Byte Budget[/I] vs. [I]Readability[/I] [/B]
“Binary” format usually requires fewer bytes of storage. An Integer 32 or Float variable uses 4 bytes. Integer 64s use 8 bytes.

The string representation of that same data would normally take more bytes, since 1 character = 1 byte.

For example, let’s suppose you want to send some floating-point values. If you send/save just the values, that’s 4 bytes each. If you’re sending that file via a cell modem and the cell phone bill comes out of your budget, just sending those raw/binary bytes might be the best way to go.

However, we were talking about spreadsheets and csv and humans looking at this data, in which case it might be easier to troubleshoot (and import into Excel), if you build your file with strings maybe something like:

22.22,22.22,22.22,

Since each of those characters takes 1 byte, that would mean each data point shown above would be 2 characters before the decimal point, 2 after, plus the decimal and the comma. That’s 6 bytes per value vs. the 4 mentioned above.

[B][I]Accuracy[/I][/B]
If you are using floats, when you convert them to a string, you might have some rounding error depending on your data.

If you do go with binary, I’d recommend downloading a hex viewer like HexEdit. If you try to look at your file with other tools, you’ll see weird characters. Firefox makes looking at your [I]text [/I]file easy, since it’s built-in ftp client is nice (binary on left, text on right):


For my 3-value example of 22.22, the 12 bytes in the file for “binary” (built using TransmitNumTable) would be:

[INDENT]8f c2 b1 41 8f 8f c2 b1 41 8f 8f c2 b1 41 8f [/INDENT]

Vs. 18 byes of string representation. Note:
32 hex = ASCII character[B] 2[/B]
2e hex = ASCII character [B].[/B] (period)
2c hex = ASCII character[B] , /B

[INDENT]32 32 2e 32 32 2c 32 32 2e 32 32 2c 32 32 2e 32 32 2c [/INDENT]

If the values were integer 32s, set at 22 (decimal) they would’ve been:
[INDENT]16 00 00 00 16 00 00 00 16 00 00 00[/INDENT]
(Forgive me if I’ve totally confused you, I’d be happy to elaborate further if needed. This [B]form 1755[/B] will tell you even more detail than you ever wanted to know about the bits/bytes in your float variables.)

whew

[B]SECOND QUESTION: [/B]
I think it’s a good idea to make sure you’ve finished writing the file (close the comm handle) then send it–keep track of the order so you’re not accessing/sending before you’re done recording.

However, I’m not sure why you mention the microSD card since you also mentioned sending the file via email or accessing it over the network. Why not just use the on-board file space rather than a card that could get removed? Check out this doc, [B]form 1646[/B] for details on how much space you have where.

But it sounded to me like you’d have 32 x 11 values. Even if you have a fancier string than my example with 10 characters per value, that’s only 3520 bytes–so I wouldn’t think you’d need the extra space on a microSD card. Did I miss something there?

-OptoMary


#6

Thanks for taking the time to go into detail for me. Everything you mentioned makes sense.

Let me go into more detail of what I am wanting to do.
The project is a system for preforming thermal transfer testing. There are is a total of 11 specific points that are tested for each test run. Before advancing to the next target point, an automatically calculated balance must be within a user defined range for a certain period of time. When these conditions are met, the current values are recorded to tables before advancing to the next point. After all 11 points are recorded it will go out of the “auto cycle” mode I have setup and user can print the “data” page which displays all 32 variables for each of the 11 points (32 tables with 11 elements). Along with this there are various other strings, floats, and integers displayed on the same page that give a full description and configuration of the test specimen. I would also like to have all the above information transmitted into the file.

This will be a one shot deal, When the run is complete,I would like to set it so that the user can define the name of the file to be created (I know about the 8.3 format), create the file, all necessary data will be converted to stings, the communication will be opened, transmitted, then communication closed. It is not something that will need to be continuously running. My reason for doing this is to save the time and avoid the human error of moving the data from my printed ‘data’ page to an excel spread sheet. My reason for wanting to use the sd card is to be able to create a new file for each consecutive test.

The reason I mentioned email was not to transmit the data via email, but to transmit generated file as an attachment via email. I have since played around with using FTP to access the sd card in the controller and that seems to be easy enough.

As far as the data usage/storage capacity, I quickly counted the characters in each of the 11 target points and they total to 137 (I could add more for to avoid the rounding error) so that is 1507 ( say 2500 if I add some for accuracy) and another 500 should more that cover all the text for the test specimen description 3 times over. So that is 3000 bytes. The controller has a wired Ethernet connection so I have no worries of charges. Is there a reason to use the on board space over the sd card when I already have a 512mb card installed and visible?


#7

Excellent! Sounds like you’ve got a handle on it.

If your sd card is already installed and you’re not worried about it wandering off, (and you already have the 8.3 filename format figured out), I’d stick with that. I’m guessing the card is just going to stay there “permanently” so you don’t have to worry about the “moving parts,” yes? (I tend to drop/lose/miss the slot with those tiny microSD cards, sometimes they even cause me to say bad words, but maybe that’s just me.)

On the upside, besides having more room, since the sd card IS flash memory, files written to it are automatically good to go if there’s a power cycle. You don’t have to “Save Files to Permanent Storage” (it’s a separate step when using the on-board memory, and you have to be careful not to accidentally call that command in a loop or something because you only get X gazillion writes before flash memory wears out).


#8

Yes, the card will be a permanent fixture an I am the only one that has access to the cabinet it is installed in. That is a good point about it being flash memory vs having to save it in an additional step, I hadn’t thought of that.

The whole process will be in its own chart that the user will have to start via Pac Display and will not loop. It will be a one shot operation that will only be allowed to take place once a successful cycle has been completed.


#9

Update on the 8.3 and 2gig file limitation(s), check out this [B]KB83929[/B] and make sure you’re subscribed to [B][URL=“http://www.opto22.com/site/register/optonews_subscribe.aspx”]OptoNews[/B] for details on how we’ve improved the microSD support! Whoo-hoo!