Capture a serial data to Excel with regular expressions

Task:

I have a piece of equipment that transmits serial data to a terminal program (data showed below), in this data is the position of the data on the screen, followed by the actual data. I want to only save the data (marked by red color) and not the positional information, can this be done (data blocks separated by green color)? Also can the data be sent directly to excel?

Pic.1. Serial data captured

Requirement:

• Advanced Serial Data Logger (ASDL) Professional or a trial version;
• ASCII data parser and query plugin for Advanced Serial Data Logger;
• DDE server plugin for Advanced Serial Data Logger (optional);
• Local database plugin for Advanced Serial Data Logger.

It is meant:

You had configured communication parameters (baud rate, number of data bits, flow control etc) in ASDL and can receive any data without communication errors.

Solution:

The image above shows, that a data flowcontains non-printable characters (squares on the image above) and doesn't show end of a data block. We need analyse it. We'll enable a display output for non-printable charactes with a code below that 20 Hex. Please, set following options.

Pic.2. Serial data view setup

And enable the "Wrap words" option, because a data block is very large and doesn't place in the program window.

Pic.3. Data logger window view

Click the "OK" button and try to receive data from a serial port. You'll get same as on an image below.

Pic.4. Data logger. Data received.

It's an other view of data received. All non-printable characters was replaced with their code like #1B. Now, it is clear, that the data block (in green brackets) begins with :sr#1B and ends with #0D#0A (underlined by red).

Now, we are ready for configuring modules. First, please, select the "ASCII data parser and query" plugin (pic.5, pos. 1) from a drop-down list. Then, enable a parsing option for data received (pic.5, pos. 2) and select necessary data publication plugins. The DDE server (pic.5, pos. 3) will help to check an accuracy of the parser's configuration. The "Local database" plugin will create Microsoft Excel files.

Pic.5. Data logger. Plugins setup.

Now, please, open the ASCII parser and query configuration window (click the "Setup" button near a drop-down box pos.1 on pic.5). A dialog window will appear on a screen (pic.6).

Pic.5. Data logger. Parser configuration window.

A configuration process will very simple if you had examined your data flow in the data logger window (pic.4). You should type same as in the data logger window in fields 1 and 2. The field #1 is the beginning of the data block and the field #2 is the end of him. On the pic.4 values, which you should type have been underlined by red color. Because our data flow contains data blocks, which should not be being analysed, then we are typing a part of the invalid data block in the filed #3.

Ok. We are going to a next tab. It's the very important part of the parser configuration. Parser uses this information for data extracting from a data block. Our data block contains 12 data items, which should be separated to different variables. Later, these variables will be used in the data publication and will be placed in different columns of our Exñel spreadsheet.

Pic.6. Data logger. Parser items.

You will be adding a new item by clicking the "Add item" button (pic.6, 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 had added all 12 variables with descriptions from Data 1 to Data 12.

Each parser item has few properties:

1. Item name - this name you'll bind with a column in the data publication plugin. It's a short representation of description and can't contain spaces or few other characters;
2. Parser's type - it's 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 being placed in variable place, we can ise only the most powerful method - regular expression. This method allows to extract a data from any position of the data block by mask. This mask can be widely transformed and adapted to characters in the data block;
3. Data type - the data type of characters extracted. You can specify a real type of your data or specify the STRING datatype, then data will have been converted to a other type later, in the data publication module. The data type here is being used on formatting of the value, by the way is being specified on "Data formats" tab;
4. Default value - this value is being used when data can't be extracted from a data block;
5. Regular expression - In our case we needs numbers, which follows characters #1B[XX;XXH, where XX may be one or two numbers. Then our regular exression will as following: \[\d+\;\d+H[\ \+]+([\d\.]+). Here \d+ is one or more numbers. The expression in parenthesis is our number, which we needs;
6. Entrance position - all our values can be found by one mask, but just a searching position of each item is variable.

All other items has same parameters, just entrance position is variable, from 1 to 12.

Ok. We are going to a next tab. You can specify basic format options here (pic.7). If you had specified the data type "String" in item's parameters, then first two options allows you to remove blank spaces from a value.

Pic.7. Data logger. Format of items.

Other options is unnecessary in our case, because all our items has the string data type.

Click the "OK" button and close the parser configuration window and the "OK" button in the options window.

Now, our parser is ready. And now, it's a time for testing it. Connect your devices and/or power it on and try to receive a data block from a serial port. If the parser had been correctly tuned up, then in the DDE server window you'll see our variables' names and their value (pic.8).

Pic.8. Data logger. DDE server window.

Now we are ready for configuring the Local database plugin. It's very simpe. Please, open the configuration window of the Local database plugin (pic.9).

Pic.9. Data logger. Selecting a database.

Just select a database type at the field #1, input a path, where new files will be being created in the field #2 and specify a file name prefix in the field #3.

It's all other options are optional. On the second page "Data formats" you can specify a representation of your data. Because all our variables is with the "string"data type data format, therefore it is unnecessary in our case. The last page "Columns order" you can specify, how our variables will be being placed in a spreadsheet. If you will not specify the columns order, then the program will be writing all variables in the following order: internal variables (FULL_DATA_PACKET, DATA_PACKET, DATE_TIME_STAMP), parser variables, in the same order, as specified in the parser configuration.

Related articles:

Capture a serial data to a Microsoft Excel spreadsheet with regular expressions;
GPS data logger;
Weight data logger to a Microsoft Access database (data at a fixed position);
Barcode scanner data logger. Capture a barcode data from a serial port;
PBX data logger. Capture a serial data from PBX systems;
Log weight from a scale to a Microsoft Excel spreadsheet (data at a enumerated position);
Pressure data logger. Log data from an intellectual pressure measuring device;
Serial data acquisition in the Checkweigher Management Information System.
[NEW] Aircraft engine monitor (example of an usage).

Read more about:

Serial port interface RS232 pinout and signals Cables and signals Data monitor cables