WinSPC Knowledgebase Home | Glossary | Favorites | Contact | Login WinSPC Knowledgebase Home | Glossary | Favorites | Contact | Login
Search the WinSPC Knowledgebase Browse by Category
How do I setup a Microsoft Excel file device?
Article Details

Last Updated
21st of June, 2013

Applies to
WinSPC 8.0, WinSPC 8.1, WinSPC 8.2, WinSPC 8.3

     WinSPC 8.0 has a new device type called ‘Microsoft Excel File’ in the device type pull down from the device setup screen. Data stored in simple Excel files (.xls files) can be used to automatically populate variables and tags in WinSPC using an ‘Excel file’ device. As the Excel file gets updated with new data, WinSPC can pull the new data into variables and tags. Setting up a Microsoft Excel file device involves selecting the Microsoft Excel file device type and then setting up the device's connection and setting up the device's mask.

Selecting the Microsoft Excel File Device Type

  1. Log into WinSPC as the Admin user or a user that has permissions to create Devices.
  2. Create a new (or edit an existing) Device.  To create a new Device use one of the following methods:
    • Right click on the Devices folder located in the left hand window of the Admin screen, and select New à Device.
    • Alternately you can click on the New Device icon located in the toolbar.
    • Or, from the menu bar select File à New à Device.
  3. Type in a name for the Device and hit the Enter key.
  4. Double click on the newly created Device name to bring up the properties.  Alternately you can right click on the newly created Device name, and select properties.


Setting up the Device's Connection Figure 1

 

  1. excel_device.jpgOn the Connection tab, in the Device Setup properties window, select Microsoft Excel File from the Device Type dropdown box.
  2. Under File to read section box, specify the location of the Excel file from which data or tag values are to be collected. Do this by clicking on one of the two Browse buttons and selecting the Excel file or folder.
    • This file: Select this option if you know the name of the Excel file and that name is not expected to change.
    • Any file in this folder: Select this option if you want all Excel files in the folder to be processed.
  3. Under After processing section box, specify what WinSPC is to do with the Excel file once it has been processed. The options are:
    • ...close the file: Select this option if you want the file closed without being deleted or moved.
    • ...delete this file: Select this option if you want the file deleted.
    • ...move the file to here:  Select this option if you want the file moved. Then, click the Browse button and specify the folder to which you want the file moved.
  4. Under Worksheet section box, specify which worksheet in the Excel file contains the data or tag values to be collected. Do this by choosing one of the two options.
    • Sheet Number Select the worksheet number.  Use this option if you don't know the sheet name or the name is subject to change.
    • Sheet Name Select the worksheet name. Use this option if you know the sheet name and it isn't subject to change.
  5. Check the Channel checkbox if the Excel file contains a channel number and you want to use this channel numbers to sort incoming data.
  6. If the Use Channel checkbox was selected, then select Numeric if the channel number consists of only numbers or Alphanumeric if the channel will contain one or more letters or other special characters.

 

Setting up the Device's Mask
     Mask (or Masking) is a way to identify the locations of the data values, tag values and channel number within a file and to ignore (masking off) the rest of the file that is unnecessary information. Before we start masking for location, just a brief description on how to make selections in the Mask Data section of the Mask tab.

Selection Type

Mouse section

Keyboard entry

Masking for a single location
(cell)

Click on a single cell

Enter Column, Start Row, End Row
(i.e.  B,  2,  2)

Masking for an entire column
(unbounded)

Click on the column labels
(i.e. click on column B to select all of column B)

Enter Column, 1, *
(i.e.  B,  1,  *)

Masking for partial column
(bounded)

Click on the start cell, hold down the shift key and click on the end cell
(i.e. click on B, 2, hold down the shift key and click on B,6)

Enter Column, Start Row, End Row
(i.e. B,  2,  6)


 

  1. excel_masking.jpgStart by clicking on the Mask tab.
  2. Click the Get Reading button. This populates the Mask Data section of the tab (i.e. the top section), with the content of the Excel worksheet.
  3. If the Use Channel check box was checked on the Connection tab, identify the location of the channel number in the worksheet. To do this:
    1. Under Locations in the bottom part of the tab, locate the Channel row.
    2. In that row, click the Location cell and enter a name for the channel number. Commonly, the channel number itself is used as the location name.
    3. In the top section of the tab, click the cell(s) of the channel number. This populates the Channel row with the Column, Start Row and End Row of the channel number.
  4. Define a masking section (Column/Cell(s)) for each Variable or Tag you want to collect from the worksheet. To do this:
    1. Click the green + button to add a New Location line (or row) in the Locations section.  Alternately by clicking on the red button will remove a line (or row) from the Locations section.
    2. Under header column labeled Type, select Data if the values for which you are defining a location are Variable values (numerical data only), or Tag if the values are Tag values (alpha-numeric data).
    3. In the row's Location cell, click and enter a name for the location.
    4. In the top portion of the tab's Mask Data section, click the first occurrence of the Variable or Tag value. This populates the Column, Start Row and End Row cells in the Locations section with the value's column and row numbers. (The End Row value will be overwritten in one of the next two sub-steps.)
      1. If there is no limit to the number of rows to be included (unbounded) when WinSPC collects values from the worksheet, in the Location section's End Row cell, enter an asterisk (*).
      2. If there is a limit, meaning that WinSPC is to read only a defined range of rows (bounded), in the Mask Data section, while holding the Shift key down, scroll to and click the value in the last row to be included. This changes the Location section's End Row cell to the last value's row number.
    5. In the row's Process cell, for Data locations, accept the New default if you want WinSPC to process only new values each time the Excel worksheet is prompted for data, or select All if you want WinSPC to process all values--previously processed or not--each time the worksheet is prompted.  For Tag locations, only All is a valid option.
    6. Repeat this step for any additional Variables or Tags you want to collect from the worksheet.
  5. Click the Test button. This populates the Value cell for each location you've masked with a value.
  6. Compare the Value populated for each location with the first value of that location's Variable or Tag value in the cell. If they do not match, repeat the preceding two sub-steps until they do.
  7. Click OK. The Excel file device is now setup and collection plans can be setup to use it.


Common Questions:

In what order is Excel file locations processed?
     During data collection, there is a defined order in which the various locations in an Excel file are processed. First, the tag locations in the first row are processed. Second, if there are data locations in the first row with a Process setting of All (i.e. configured to be processed each time the Excel file is processed regardless of whether or not they were previously processed) or data locations with a Process setting of New (i.e. configured to be processed only once) which haven't been previously processed, these are processed. The second row is then processed in the same manner and so on.
     If the same worksheet cell serves as both a tag location and a data location--which is a legitimate scenario--the tag location will be processed first. If the worksheet has one or more tag locations which are unbounded (i.e. those with an asterisk in their End Row cell), processing of all locations is terminated when the first blank tag value is reached.

How does a WinSPC Excel Device deal with blank cells?
     The Excel device, introduced in WinSPC 8.0, handles blank (empty) cells in the Excel worksheet depending on the features enabled in the device setup for the specific device location that will be processing the blank cell. A location in the Excel device can either be bounded (meaning it has a specific beginning and end row) or unbounded (meaning it has no specific end row - the user has specified the "End Row"' to be a "*"). In the screenshot below, the "Diameter Location" is a bounded location, while the "Weight Location" and "Shift" are unbounded.
     The exact rules for dealing with blank cells are as follows:

Data Locations

  • If a bounded data location encounters a blank cell:
    • If the "Process" option is set to "New", then processing stops for that location.
    • If the "Process" option is set to "All", then that row is skipped.
  • If an unbounded data location encounters a blank cell, then processing stops for that location.

Tag Locations

  • If a bounded tag location encounters a blank cell, a blank tag value is then used.
  • If an unbounded tag location encounters a blank cell, then processing stops for that location.

 

Can WinSPC read an Excel 2007 or Excel 2010 formatted file?
     The file format of .XLSX is not supported in WinSPC at this time. This feature is being review to be incorporate in a future release. At the time of this publication, no scheduled release date has been set for this new feature. Excel 2007 and Excel 2010 have a Save As feature to save the file as an older format of Excel 97-2002, which WinSPC is capable of reading.

Can WinSPC parse a cell to extract the data?
     For example if you have a cell that has data for a variable called weight that has the entry of “453.59 grams”. WinSPC only accepts numerical values for Variables, and previous example is of the type Alpha-Numeric. WinSPC was designed to read simple Excel files, and at this time does not have the feature to parse cells. If you are able to separate the data from the units in to two separate cells, then WinSPC will be able to read the data.

Why is no data read in to the Collection Plan when I re-read an Excel file?
     Most likely cause is that the variable Process in the Locations area in the Excel Device is set to New. WinSPC will remember where in the file that the last data was read. To reset the location pointer, click the Reset Locations button on the Mask tab. If you do not desire to have it remember where in the file it last read, and prefer to have the entire file read each time, change the Variable Process to All.

 

 

How does WinSPC handle Merged cells?
     WinSPC was designed to read a simple Excel file using the format of Column, Row, Row. Merged cells span multiple columns, multiple rows, or both. Merged cells are not supported in WinSPC at the time of this publication.

 

     The Connection tab in the Excel File Device Setup determines where WinSPC looks for Excel files, what worksheet in the excel file to use, what to do with the file after being processed, and specify channel numbers. The following settings determine where the data file(s) originate, and what to do with them after they are processed:

 

Attachments
No attachments were found.
Related Articles
Visitor Comments
No visitor comments posted. Post a comment
Post Comment for "How do I setup a Microsoft Excel file device?"
To post a comment for this article, simply complete the form below. Fields marked with an asterisk are required.
   Your Name:
   Email Address:
* Your Comment:
* Enter the code below:
 

All Content 2012 DataNet Quality Systems. All Rights Reserved.