Auto saving of data during measurement

[Tera-Engineer]

I have an interesting project on my hands, I developed a program and since have deployed it as an .exe to production for acquiring data from an instrument tabulating it into a Data Table and saving that data once the work order is completed into a Excel Template using MS Office Report Generation Toolkit.

I was requested to look into what it would take to add in an Auto-Save feature of the data during mid data acquisition, as well as other features that I feel like I can get done.

I have thought about this task in two different ways:

1)  Every x samples save the data into the excel sheet, and then append that excel sheet (all using the MS Office RGT) for the length of the work order.  There are some pros and cons to this.  Pros:  data is in MS Excel readable format incase program crashes, user can see what the last data point is and start from there.  Cons:  reading/ writing to the excel template and formats can be process intensive and on older computers this isn't good, also I believe this will slow down the program overall and I don't want it to impact the user.

2)  Using an intermediate file format that i can use for the data taking and at the last step convert to the Excel Template using MS RGT.  I can add provisions to the program to save, load files, etc for this other file format.

For #2:

-What file format should I use?  LV Measurement file?

-Am I think about this the wrong way?

Over all is there a better way of doing this?

Thank you for the help,
~Dan

[aputman]

Couple questions:

1. How many samples are you thinking of writing each time?
2. What does the data look like?  Do you have a sample?
3. Is Excel available on the deployed station?  It will be required to write to an Excel file.
4. Have you thought about writing to an Access or MySQL database and creating a screen for viewing the data?  Writing data this way is very quick in my experience (may not be quick on an old machine if you are handling large amounts of data). 

I would throw the write to file portion of the code in it's own while loop using a Producer/Consumer type architecture so that it doesn't affect the performance of the rest of the code. 

[Tera-Engineer]

Thank you for the reply, the architechure of the program is an event driven producer/ consumer loop style.

Anwsers to your questions:

1)  The number of samples will depend on the work order, I was think every 5 or 10 samples.  The total number of samples will depend on the size of the work order.

2)  The data will be a series of String elements in an array, including:  Connector type, serial number, test 1 results, P/F, test 2 result, P/F  (I can provide a screen shot of the data tomorrow when I get back into the office).

3)  Excel is available on the deployed station.

4) I don't have alot of experience with Acess or MySQL databases, infact make that no experience.  I want to get all of the systems in production to report to a common database with the data so I am not going into each individual folder to get all of the data for one Work Order.

The data will ultimately get saved to a shared drive on the network.

Thank you again for the help,
~Dan

[Gregory]

I did this recently for a measurement that happens at ~10 second intervals and could be run overnight, so I wanted a backup if the program crashes or computer is restarted. I continued to save my main output using the report generation toolkit. During the test I kept a .csv file and added one point at every ~10 second interval. If the test completes correctly, and the .xls is saved without error, then the .csv is deleted. To the user it looks like a conversion, but in reality it is not.

[Dennis_Knutson]

If you want to save to a network drive and a common database, Excel is not the correct solution. A real database would be much better in my opinion. It is not that difficult and easier than native Excel in my opinion. If you have Access in your office suite, start experimenting with that and some of the tutorials. MySQL or the free version version of SQL Server would be more robust and have better performance in the long run.

[Tera-Engineer]

This is exactly what I would be looking to do.  Any tips or pointers?

Thanks,

~Dan

[Tera-Engineer]

---

@Dennis_Knutson wrote:
If you want to save to a network drive and a common database, Excel is not the correct solution. A real database would be much better in my opinion. It is not that difficult and easier than native Excel in my opinion. If you have Access in your office suite, start experimenting with that and some of the tutorials. MySQL or the free version version of SQL Server would be more robust and have better performance in the long run.

---

Excel is the correct solution for how I would like the data to be accessible outside of the program and database.  I do recongnize that a real database would be a better solution, but at this time we don't have the time, the server space, nor the hardware to set this up.

What I meant by I would like to start using a common drive and database is that, currently our products gets tested and worked on in multiple location on multiple systems.  Currently each system doesn't record the data (which I believe that it should), or it saves it to a different folder either locally or on a shared drive.  I would like to get all of these systems to save the data in the format that is best suited for displaying the data outside of LabView, maybe even create a full report of the data in Excel using RGT.  Then have all of the raw data as well saved to the same folder on a common network drive.

The data that I am currently working with is best suited to be displayed and analyzed using Excel (outside of LabView) and can be easily converted displayed for larger reports and presentations.

Thank you again for the help and interest,

~Dan

[aputman]

---

@Physics-Engineer wrote:

Over all is there a better way of doing this?

---

A relational database (MySQL, etc) is the better way of doing this, especially if you are testing the same product multiple times at different workstations.  If the workstations are networked together, you don't need a massive server to accomplish this.  I would even suggest using one of the workstations as the server if the number of connections and the amount of data is not too large.  Setting up MySQL server is not difficult at all.  In fact, it is mostly automated now-a-days and you can be up and running pretty quickly.  

You'll need to think about your table structure a little bit so that it is normalized, meaning that you don't have the same data stored multiple times in different tables.  I like to use stored procedures to send and receive data from the sql server because it makes some changes easy without having to recompile your VI code.  This also allows you to off-load some of the code to the server that is better handled in SQL (data formatting, combining strings, adding columns together, etc.)  

If you have zero experience with SQL, you may have to resort to using Excel in the short term but long term, I would suggest studying up on SQL, set up mySQL on your own PC and play with it.  Because as i said in the beginning, this is the better solution and one you will soon be led towards after you see the inadequacies with the Excel method.  My $0.02.

[Gregory]

This is exactly what I would be looking to do.  Any tips or pointers?

---

I'm not sure if you were responding to me or not. If you were, here is an example of how I used a functional global to save power over a certain amount of time. The person I wrote it for had some wishes, like the time is expressed in seconds, starting at zero. Open the example.vi to see how the VIs work together.

[Tera-Engineer]

Hello aputman,

I agree with you 100% right now I am unable to invest the time into learning MySQL or MS Access to set this up properly.  The short term goal is to get every system that we deam reasonable to record/ save the data.  Next step to make this save location common and every W.O. will have its own folder with all of the data.  Last step would be to do this in a database format so that you can enter the W.O. or some other search string to find all of the information on that W.O.

Thank you for the help,

~Dan