• contact
• support
• articles
• order
• news
• Forum

Serial port data aggregation. Combine a data of two different com-ports to one MySQL table row. Tutorial.

Problem scenario:

How can data of two different com-ports be combined in 1 file?

I have an application that requires the use of two devices. One will scan bar codes (EAN-13) using an RS232 port. We have some bar code scanner's connected, each one send when read, 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 stamp.

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.

Requirements:

• Advanced Serial Data Logger (ASDL) Professional or a trial version;
• ASCII data parser and query plug-in for the data logger;
• DDE server plug-in for the data logger (optional);
• Aggregator plug-in for the data logger;
• ODBC database plug-in for Advanced Serial Data Logger.

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 a 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 help of the menu from the fig.4 pos.1, 2.

Fig.4. Serial port data aggregation. Manage configurations.

You've configured communication parameters (baud rate, number of data bits, flow control etc) in the data logger and can receive the data from both port without communication errors.

The Mettler Toledo scale is configured for the “S.Cont” mode and data transmission occurs either continuously after each load change.

Solution:

Figures above show that data flows are very simple. Each record has a fixed size and a fixed position of each item. We need to recognize ending characters of an 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.

This 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 by red).

Now, we are ready for configuring modules. First, please, select the "ASCII data parser and query" plug-in (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 plug-in.

Now, please, open a configuration window of the ASCII parser and query plug-in (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 very simple 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 the field #2 marks the end. In the example, our data block does not contain a start marker. You should type a 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.

Several records in the data flow from the scale should not be parsed, because contains an 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. This is a very important part of the parser configuration. The parser uses this information to extract parser items (variables) from a data packet. The data packet from the barcode scanner contains 2 data items, which should be placed to different variables. Later, these variables will be used in the data export and data filter plug-ins. In our case values from these variables will be placed to 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:

1. 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;
2. Parser's type - is a method, which the program will use for data extraction. Our parser has few methods from simple to most powerful. In our case, where data is placed in a fixed place, we can use the simple method - "fixed position". This method allows to extract any number of characters from any fixed position of the data packet;
3. Data type - the data type. If you'll specify a data type other than the "STRING" data type, then the module will try to convert the string value to the specified data type. The module allows to configure several options that will be used at the conversion (see the "Data format" tab);
4. 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);
5. Fixed position - is the position of the first variable's character in our data block. The minimum value is 1;
6. Fixed length - is the number of characters, which will be transferred to a variable. The minimum value is 1.

The second item has 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 specified the "String" data type for two parser variables, then 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 configuration and therefore will use data filter and data export plug-ins of this configuration. This method allows you to aggregate data from multiple heterogeneous data sources and exports it 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. This is a very important part of this tutorial. We need to activate and configure the Aggregator plug-in that will combine data from both configurations to one database record (fig.16 pos.1). Please, note that this plug-in should be activated in one configuration only (COM1 in this example).

Fig.16. COM1 (barcode scanner). Aggregator plug-in.

Please, open the configuration window of the Aggregator plug-in (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 plug-in window.

You should specify one variable from each configuration that should be aggregated. These names we are defined on 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 plug-in.

Now, we should select necessary data export plug-ins 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" plug-in will write data to a MySQL database.

Fig.18. COM1 (barcode scanner). Data export plug-ins.

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 configured properly, then the DDE server window in the configuration #1 (barcode scanner) will display our variables 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.

To export data to the necessary format, please, click on the corresponding link below:

• RS232 to Excel. Four methods to send data from RS232 to Excel;
• Writing data to a CSV text file with help of "Local database" plug-in;
• Writing data to an XLS file without Excel with help of "Local database" plug-in;
• Sending data to Excel through the DDE interface with help of "DDE Server";
• Exporting data and charting in Excel in real-time with help of "Direct Excel Connection" plug-in;
• Writing data to an XLS file without Excel with help of "Local database" plug-in (variant #2);
• Export data to Microsoft SQL Server 2000 with help of "ODBC Database" plug-in;
• Export data to the MySQL database with help of "SQL Database Professional" plug-in;
• Write data to Microsoft Access with help of "ODBC Database" plug-in.