Painless LabVIEW Test Data Management with XML & Excel

This is a guest post by Dan Shangraw, P.E., Certified LabVIEW Developer, and Owner of Automated Software Technology (AST), a Certified National Instruments Alliance Partner in Michigan with over 10 years’ experience developing custom measurement systems. If you need a small or short-term LabVIEW project done well, contact them!

Saving Test Data In XML Using LabVIEW
by Dan Shangraw, P.E.
Owner, Automated Software Technology

Introduction

Most significant LabVIEW software projects involve acquiring and saving data in some way. After all, what’s the use of all the data we acquire unless we do something with it later? However, in my experience flexible and effective data storage is an afterthought in many projects.  We focus on what sensors to buy, what cool new hardware we get to play with, and which new LabVIEW features we get to use. None of this matters to the client who’s paying us to develop this awesome new measurement system; they care about the DATA!   They need data that solves their measurement problem, and they need that data to be compatible with the analysis tool they are proficient in (Excel, DIAdem, MATLAB, Origin, etc).  They also need the data to be in a format that is well-known, mature, and will still be supported in ten or twenty or more years. A file format that meets all of these requirements is XML.

XML (Extensible Markup Language) is a markup language that defines rules for encoding documents in a format that is both human-readable and machine-readable.  In wide use since 1998, it is governed by an open standard and is supported by virtually every popular analytical program. XML-based formats have even become the default for many programs including Microsoft Office, OpenOffice.org, and Apple’s  iWork. And because XML files are plain ASCII text, your data is future-proof; you’ll still be able to read it 10, 20, or even 50 years from now.

Since this article is about data and LabVIEW, I’ll focus on how to save data acquired by LabVIEW in an XML format. I will also show you how to easily view and analyze that XML data using Microsoft Excel.

The XML Format

In the following example, let’s assume we have a LabVIEW program that monitors temperature and pressure.  We want to save the temperature and pressure data to a file along with some header information.  The header information will be Test Operator, Test Comments, and Sample Rate.

Below is an example of the sample data in an XML format.

<Test Operator="Dan" Comments="Test Passed" Sample_Rate_Hz="10">
   <Data>
     <Temperature_C>1</Temperature_C>
     <Pressure_psi>2</Pressure_psi>
   </Data>
   <Data>
     <Temperature_C>3</Temperature_C>
     <Pressure_psi>4</Pressure_psi>
   </Data>
   <Data>
     <Temperature_C>5</Temperature_C>
     <Pressure_psi>6</Pressure_psi>
   </Data>
 </Test>

If you look closely at the XML above, you can see XML’s simple, self-describing syntax. The first line contains the “root” of the XML structure, in our case <Test>.   This first line also contains attributes of the root: Operator, Comments, and Sample Rate. The next lines describe child elements of the root, in our case data samples which each have a temperature and a pressure element.  The values for an element are between each tag e.g. <Temperature_C>Test1</ Temperature_C >.  A tag is what identifies an element and has the following syntax:

<Name>Value</Name>

Creating XML Files in LabVIEW

The easiest way to create XML files in LabVIEW is to use JKI’s EasyXML add-on for LabVIEW. There are other ways to do the same thing, such as by using LabVIEW’s built-in XML functions; however these built-in functions add data that only pertains to LabVIEW and they generate XML that can’t be easily loaded by tools like Excel.  This defeats the purpose an open file format that can be read with any analysis tool.  EasyXML, by contrast, creates XML data that follows the standard XML format.

EasyXML is also much easier to use than LabVIEW’s native XML functions. All I need to do to generate a properly formatted XML file containing my data is to create a Cluster containing the data I want to save and use EasyXML to turn it into standard XML:

Writing LabVIEW data to disk the easy way...

In this example I am using Easy Generate XML.vi.  This VI converts a LabVIEW Cluster into an XML formatted string.   Then I just save the XML formatted string to a text file. I could make this code even simpler by using Easy Write XML File.vi.

...and the even easier way.

Note: Both these images are VI Snippets. To use this code yourself, just drag the image into a LabVIEW Block Diagram.

The cluster that is wired into Easy Generate XML.vi contains the attributes and acquired data I want to store.  The attributes are in a cluster named “#attributes,” which EasyXML automatically converts into XML attributes.  The data is an array of cluster elements named “Data.”  Each array element contains a single sample for temperature and pressure.

Reading XML Data With Excel

Data is useful only if you can read it with a tool and do something with it. For this example we’ll use Microsoft Excel because almost everybody has Excel, and because Excel makes working with XML really easy. Below is a screen shot of our sample XML file, viewed in Excel.

Importing XML data into Excel.

As you can see, Excel displays the XML data in a very familiar and easy-to-use table. Now we can use Excel’s native functions to analyze the data.

Filtering XML Data With Excel

In the image above, Excel has automatically used our XML tags as column headers .  We can filter data by clicking the column header dropdown as seen below. The built-in filtering tools allow us to easily inspect portions of our data, and we can also use custom filters to build complex drill-downs.

Using Excel's built-in filters with XML data.

Creating a simple numeric filter.

The above 2 screenshots show how I specified a filter to only show data greater than 4.

Our data after applying the filter.

The above screenshot shows what the data looks like filtered.  Our sample file only has 3 datapoints in it, but these same features allow us to inspect vastly larger datasets easily.

Conclusion

Saving data in an XML format can be very powerful because XML is a popular, well-supported, and open standard for data exchange, and what I’ve shown above is just the tip of the iceberg. We’ve seen how easily Excel reads and manipulates XML data, but you can also use many other powerful tools like National Instruments DIAdem.

How do you use XML to store and manipulate your data? Leave a comment and tell us!

 

This entry was posted in Community, EasyXML, JKI and tagged , , , , , , . Bookmark the permalink.

10 Responses to Painless LabVIEW Test Data Management with XML & Excel

  1. Interesting thought. The disadvantage of XML, though, is the rather low signal-to-noise ratio inside the file. Files become exceedingly large for a given amount of data, compared to, say, CSV, or especially TDMS. With the TDMS viewer for Excel (and TDMS is natively supported by DIAdem, naturally), TDMS becomes a very viable option, and since it’s a binary format, size is nearly optimal.

  2. Jim Kring Jim Kring says:

    Adrian: Great point that XML isn’t super-optimal in terms of file size (and there are some attempts to improve this via binary XML, JSON, and binary JSON [BSON], or even zip-compressing the XML data). I think it’s interesting, though, that as disk, RAM, and CPU performance continue to increase, readability and portability of data seems, in most cases, to trump file/storage optimizations. That said, many folks in the LabVIEW community are on the bleeding edge of technology and squeezing every bit of performance out of their applications is critical, and file formats like TDMS are a perfect solution — again, you make a great point.

  3. Jim:

    Ah, but if you “improve” XML by compression or resorting to binary, haven’t you discarded one of its advantages, namely, human readability?

    I hear you about the file/storage optimizations, but what also tends to happen is that as disk, RAM, and CPU performance continues to increase, data rates and channel count also increases. So you might end up canceling out the effect of the increased hardware performance.

    For me, the great thing about TDMS files is that I can take a LabVIEW waveform read from a standard DAQmx function, and wire it straight into the file write function, sometimes with no alteration.

    For me, what you use XML for is configuration variables in some gigantic cluster, although I’ve also resorted to .ini files with an action engine for access. That seems to work particularly well on RT targets where space is at a premium.

  4. Jim Kring Jim Kring says:

    Adrian: Yes, I agree that moving to binary XML removes the human-readability benefit. And, I agree with you that as performance increases, so does what we try to do with that extra performance — we always seem to be pushing the limits of our computers. That’s why computers (and even iPhones) always seem to be a little too slow after a couple years of new OS/App updates — we just ask them to do more stuff while we juggle usability performance right on the edge of “good enough”.

  5. Dan Shangraw says:

    As Jim as stated the file size issue is becoming less and less of an issue due to increasing computer performance. I have seen this problem with a data file that was created 10 years ago. Nobody knows what it is, how to read it etc. Does it matter at that point how big the file is if nobody can use it?

    I also concede that if taking very large amounts of data file size becomes more of an issue. However in my experience you can make reasonable reductions in file size by a lower sample rate, signal processing, etc.

    The point I hope to make in the article is to save data in some open standard that can be read by the largest about of tools possible. I also believe that the data format needs to be human readable. Human readable will insure the data file will not become obsolete. XML is inefficient, however each data point is clearly labeled. I think there is value there.

    I hope there is more discussion on this article. I am curious what other people’s opinions are.

  6. David Staab says:

    I’ve done a lot of work on automated test systems, and I’ve always struggled with the decision to manage test configuration data using XML. It’s a great format for lots of applications, but it’s very difficult to manipulate directly when using it to represent heavily nested “mother clusters” in LabVIEW. Writing and editing test configurations requires either lots of manual effort or the development and maintenance of a utility program that reads and writes the XML files. Ideally, I’m able to manipulate test config data using a standard tool that presents the information naturally and provides an easy-to-use interface. For INI files, that’s Notepad.exe. For CSV files, that’s Excel. And now I know that Excel can be the tool for XML data too! It’s not perfect for all schemas, but I think working within the bounds of an existing tool is easier than trying to maintain your own.

    So thanks for the tip regarding MS Excel and XML data! Now I won’t be as nervous about managing configuration data using XML.

  7. Brian Fischer says:

    Thanks for the article Dan. I have used Microsoft’s XML Notepad 2007 to edit and view XML files. Based upon this article, I may be switching to Excel.

  8. Brian Fischer says:

    So it seems that Excel is good as a viewer only. Saving in XML does not maintain the source XML format. Any tips on how to maintain the original XML schema when saving from Excel?

  9. David Staab says:

    Brian, what do you mean by “format”? An XML schema cannot enforce the order of sibling tags in a file, so it’s completely legal to read a set of tags in one order and write them back in a completely different order.

  10. Dan Shangraw says:

    I haven’t tried saving data from Excel in an xml format.

Leave a Reply

Your email address will not be published. Required fields are marked *