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.