Processing data from HMI scale indicator
I’m doing a Project for a pecan farm here in New Mexico that wants to capture the weight of the bags of pecans as they are processed and save that data to an excel file.
I’m using a Rice Lake 920i HMI scale indicator with a WLAN card and I’m sending the data to the network each time the user presses the “print” button on the indicator. My goal is to transfer this data to Excel, such that each time the user hits “print” a new row is appended to an existing excel file that contains the data.
Currently, when the user presses “print,” the following data shows up in the Advanced Serial Data Logger main window:
Largest Pecan Farm
1 HMI 1, La Scala, NM 88111
Superbag ID: 12312
Gross Weight: 2103lbs
Grade: No. 1
I would like this data to appear in Excel as follows:
DATE BAG ID WEIGHT VARIETY GRADE FARM
01/24/14 12312 2103 Wichita No. 1 Murano
01/24/14 12313 1994 Wichita No. 1 Murano
01/24/14 12314 2068 Wichita No. 1 Murano
- Advanced Serial Data Logger Professional, Enterprise or Trial version
- ASCII Data Query and Parser, DDE Server, Excel Export Pro
It is assumed that:
You've configured communication parameters (baud rate, number of data bits, flow control etc) in the data logger and can receive any data without communication errors.
The image above shows that the data flow is very simple, but we should extract data variables from a report with multiple rows.
It is clear from the data example above, that the data block starts with "Date/Time:" and ends with "Farm: " with some letters.
We are now ready for configuring of the modules. First, select the "ASCII data query and parser" plug-in (fig.1, pos.1) from a drop-down list. Then, enable a parsing option for data received (fig.1, pos.2) and select the necessary data export plug-ins. The DDE server (fig.2) will help us to check that the data packet is parsed and exported. The "Excel Export Pro" plug-in
will write data directly to XLS file without Microsoft Excel installed.
Fig.1.Data parser plug-in.
Fig.2.Data export plug-ins.
Now, please, open the ASCII parser and query configuration window by clicking the "Setup" button near a drop-down box (fig.1, pos.1). The dialog window below will appear on the desktop (fig.3).
Fig.3.The parser configuration window.
The configuration process should be very simple if you have examined your data flow in the data logger window (fig.3). You should type in the same as in the data logger window in fields 1 and 2. Field #1 marks the beginning of the data block and the field #2 marks the end. In this example, our data block contains both markers. Therefore, all fields are not empty. The values to be typed in here are as underlined in red in fig.3 above. Because the end marked is not constant then we'll use a regular expression as a the end marker. The expression "\bFarm: [A-Za-z0-9]+[\x0D\x0A]+" means any string that begins with the "Farm" word and following by any characters from the specified range. The "RE" checkbox near the expression field specifies that this expression is the regular expression (not plain text).
Because the report in this example is rather short, then we should set the "Timeout" value (fig.3 pos.#3) to a corresponding value to prevent losing of a data. The option at pos.4 allows you to use the start marker in the parsing process.
The next tab is a very important part of the parser configuration. The data parser uses this information for data extraction from the data block. In the example, the data block contains 6 data items (look at the data example above) namely: DATE, BAGID, WEIGHT, VARIETY and so on, that should be separated out to different variables. Later, these variables will be used in the data export and will be placed in different columns of our Excel file. The file with all variables you can download
here (right-click -› Save As...) and load it on the "Data parsing" page (fig.4 pos.#8).
Fig.4. Parser items.
Any new items may be added by clicking the "Add item" button (fig.4, pos.7). Before adding an item the program will ask you about an item description. You can type any characters here, which will help you to remember a variable's content. For this example all 6 variables with their corresponding descriptions have been added.
Each parser item has a number of properties:
- Item name - this name will be bound to a column in the data export plug-in. It is a limited text description and cannot contain spaces and a few other characters;
- Parser's type - is a method, which the program will use for data extraction. Our parser has a few methods from simple to most powerful. In this example, where data items are placed in a variable place, we should use the most powerful method - regular expressions. This method allows you to extract a data from any position of the report by mask. This mask can be widely transformed and adapted to characters in the report;
- Data type - is the data type of the characters extracted. For example, you can specify FLOAT, DATE, TIME or STRING data types. The data will be converted to the conversion type later, in the data export module. The data type here is being used for formatting of the value, which will be specified on the "Data formats" tab later. In the example, we'll use the FLOAT data type for all numbers, the DATETIME type for date and time, the STRING for other parser items;
- Default value - the value specified here is to be used when data cannot be extracted from a data block. In this example, some numbers may not exist in the reports, therefore we should define a default value for all variables;
- Regular expression - in this example we need numbers or strings, which follow by dot or the ":" character in several places of the report. For example, a regular expression will as following: Date\/Time: ([\d\.\:]+). Here, the expression in parenthesis is our date and time, which we should extract to a variable. Please, search in Google for a detailed description of regular expressions;
- Entrance position - all our values are present in the report once only, therefore an entrance position for each variable is 1.
All other items have identical parameters, except a data type and a regular expression.
||sequence of any combination of digits, dots and colons
||Superbag ID: (\d+)
||any characters before a line ending characters (\x0D and \x0A mean ASCII codes for CR and LF)
||any digits (the units will not be included)
||any characters before a line ending characters
||any characters before a line ending characters
In the next tab, you can specify basic format options as per (fig.5). In this example one variable has the "datetime" data type. Therefore the first option (fig. 5, pos. #1) allows you to convert the date string to a field with the "datetime" data type. We specified MM"."DD"."YY":"HH":"NN"."SS here, according to the data example above. For a detailed description of formatting characters, please, see the help file.
Fig.5. Date/time foramt.
Therefore the second option (fig. 5, pos. #2) allows you to trim unnecessary spaces from left and right from "string" values.
Click the "OK" button and close the parser configuration window. Then click the "OK" button in the options window.
Now that our parser is ready it is time for testing it. Connect your device and power it on if necessary. Check to see if you are able to receive a data block from the specified port. If the parser had been correctly set up, then you should see all parser items names and their values (fig.6) in the DDE server window below.
Fig.6. Testing parser. DDE server window.
All parser variables are now ready for export to an Excel spreadsheet. You can read about it in the second part.