Serial port data aggregation. Combine data from two different COM ports
How can data of two different com-ports be combined in 1 file?
I have an application that requires the use of two devices.
The first device is a barcode scanner connected. It will scan bar codes (EAN-13) using an RS232 port. When read, the scanner sends its identification number (four digits) and bar code information (thirteen 13 digits).
And the other is a Mettler Toledo scale that will output an ASCII string (Continuous Output - scales continually sends updates which are read from the port).
We need to take the scan and weight data and parse it and log all the data into a MySQL database. The data needs to be stored as a single record with time and date
Fig.1. Serial port data aggregation. Bar code scanner data.
Fig.2. Serial port data aggregation. Mettler Toledo scale data.
SD - deflection data; S - stable data.
It is assumed that:
You've created two different configurations in the program by clicking the "Green Plus" button twice (fig.3 pos.1). Each configuration will read data from a different COM port and a device. After this action you should get the main program window like on the figure below (fig.3 pos.2).
Fig.3. Serial port data aggregation. Added configurations.
You may manage your configurations with the help of the "Options →
Manage configurations" menu
You've configured communication parameters (baud rate, the number of data
bits, flow control, etc.) in the data logger and can receive the data from both ports without communication
The Mettler Toledo scale is configured for the "S.Cont" mode, and data transmission occurs continuously
after each load change.
Figures above show that data flows are elementary. Each record has a
fixed size and a fixed position of each item. We need to recognize the ending characters of each data record. Please, enable display output for non-printable characters with a character code below than 0x20h in both configurations (for each port). Please,
set the following options.
Fig.5. Serial port data aggregation. Data view setup.
Then click the "OK" button and try to receive data from
ports. You should receive the data like on figures below.
Fig.6. Serial port data aggregation. COM1 (barcode scanner). Data received.
Fig.7. Serial port data aggregation. COM2 (scale). Data received.
The combo-box at the pos.1 on fig.6 and 7 allows you to switch between ports.
It is another view of the received data. All non-printable characters
were replaced with their code like #0D. Now, it is clear, that a data packets
(in a green rectangle) end with #0D and #0D#0A (underlined
Now, we are ready for configuring modules. First, please,
select the "ASCII data parser and query" plugin (fig.8, pos.1) from
a drop-down list. Then, enable a parsing option for data received (fig. 8, pos.
2). Please, note that you should activate the parser module in both configurations, and each parser will have fully different settings.
Fig.8. Serial port data aggregation. The data parser plugin.
Now, please, open a configuration window
of the ASCII parser and query plugin (click the "Setup" button near a drop-down box fig.8, pos.1). The
dialog window will appear on the desktop (fig.9 and 10).
Fig.9. COM1 (barcode scanner). The configuration window of the parser.
Fig.10. COM2 (scale). The configuration window of the parser.
The configuration process will become effortless if you've examined your data flow in the data logger window (fig.6 and 7). 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 field #2 marks the end. In the example, our data block does not contain a start marker. You should type value from the fig.3, which is underlined by red. The options #3 is activated because we need to append our date/time stamp to each record.
The parser should ignore some records in the scale's data flow because they contain unstable weight data. Therefore we should configure the filter to remove these records from parsing. These records contain "SD" characters, and we should create a corresponding filter rule (Fig.11).
You will be adding a new filter rule (Fig.11, pos. 1) by clicking the "Add item" button (Fig.11, pos. 2).
Fig.11. COM2 (scale). Filer rules.
OK. We are going to the next page. It is a significant part of the parser configuration. The parser uses this information to extract parser items (variables) from a data packet. The barcode scanner's data packet contains 2 data items, which should be placed on different variables. Later, you can use these variables in the data export and data filter plugins. In our case, values from these variables will be placed in different columns of a target MySQL table.
Fig.12. COM1 (barcode scanner). Parser items.
Fig.13. COM2 (scale). Parser items.
You should add two and one new items by clicking the "Add item" button
(fig.12, 13, 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. We've added all variables with corresponding descriptions on the fig.12, 13.
Each parser item has few properties:
- Item name. This name will be bound to a column in the data export plugin. It is a limited text description and cannot contain spaces and a few other characters;
- Parser's type. It is a method which the program will
use for data extraction. Our parser has few methods from simple to the most powerful.
In our case, where data is placed in a fixed place,
we can use the simple method - "fixed position".
This method allows extracting any number of characters from any fixed
position of the data packet;
- Data type. If you specify a data type other than the "STRING" data type, then the module will try to convert the string value to the selected data type. The module allows you to configure several options that will be used at the conversion (see the "Data format" tab);
- Default value. This value will be used when data can't
be extracted from a data packet (or can't be converted to the specified data type);
- Fixed position. It is the position of the first variable's character in our data block. The minimum value is 1;
- Fixed length. It is the number of characters, which will be transferred to a variable. The minimum value is 1.
The second item has the same parameters, just the position and the length are different.
On the next page (fig.14), you can specify basic format options. Because we've defined the "String" data type for two parser variables, then the first two options allow removing blank spaces from a
value. Other options are not applicable in our case.
Fig.14. Serial port data aggregation. Format options.
Please, click the "OK" button and close the configuration window of the parser.
Now, we should select a data export method for COM2 (scale). This configuration will redirect all parsed variables to other configurations and, therefore, use the data filter and data export plugins of that configuration too. This data redirection method allows you to aggregate data from multiple heterogeneous data sources and export data to one target. Please, set the following options. Please, note that you should create both configurations before this step.
Fig.15. COM2 (scale). Redirection.
OK. We are going to the next stage. It is another important part of this tutorial. We need to activate and configure the Aggregator plugin to combine data from both configurations to one database record (fig.16 pos.1). Please, note that this plugin should be activated in one configuration only (COM1 in this example).
Fig.16. COM1 (barcode scanner). Aggregator plugin.
Please, open the configuration window of the Aggregator plugin (fig.17) by selecting the module in the list and clicking the "Setup" button on the "Query Parse Filter" tab.
Fig.17. COM1 (barcode scanner). The Aggregator plugin window.
You should specify one variable from each configuration that should be aggregated. We have defined these names in fig.12 and 13. Please, note that the "WEIGHT" variable doesn't exist in the drop-down list with variables, and you should type this name manually.
Please, click the "OK" button and close the configuration window of the Aggregator plugin.
Now, we should select the necessary data export plugins for COM1 (barcode scanner). The DDE server (fig.18, pos.1) will help us to check that the data packet is parsed and exported. The "SQL Database Professional" plugin will write data to a MySQL database.
Fig.18. COM1 (barcode scanner). Data export plugins.
Then click the "OK" button again in the options window.
Now, our parsers are ready, and we can test it. Connect your
device and/or power it on and try to receive data packets from both ports.
If parsers are correctly configured, the DDE server window in the configuration #1 (barcode scanner) will display our variable's name and value from both configurations (fig.19).
Fig.19. COM1 (barcode scanner). The DDE server window.
All parser items are now ready for export to the MySQL database.
You can read about it in the second part.