Serial port data and Excel. Real time charting

Problem scenario:

I want to connect an RS-2322 port to MS Excel. The port will provide a stream of ASCII numeric characters to Excel. I want Excel to automatically receive and graph the ASCII data, sort of like an oscilloscope. I can control the format of the incoming ASCII data.

Fig.1. Serial port data and Excel. Data captured

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 Advanced Serial Data Logger (optional);
• Direct Excel Connection plug-in for the data logger software.

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.

You can work in Microsoft Excel, specifically create charts in an Excel file.

Solution:

The image above shows that the data flow is very simple, but we should extract data variables from a report with multiple rows. At the same time the figure doesn't contain non-printable characters and doesn't show ending characters of a data packet. 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. You need to create a port configuration. Please, click the "Plus" button in the main window if you didn't make it before and set the following options.

Fig.2. Serial port data and Excel. Serial data view setup

Then click the "OK" button and try to receive the data from a port. You should receive the data like on the figure below.

Fig.3. Serial port data and Excel. Data received.

This is the other view of the data received. Notice that all non-printable characters were replaced with their code i.e. #0D. It is clear from the data screen above, that the data block (within the green rectangle) starts with TLFLOW and ends with three pairs of CR+LF characters (#0D#0A#0D#0A#0D#0A) (underlined by red).

We are now ready for configuring of the modules. First, select the "ASCII data query and parser" plug-in (fig.4a, pos.1) from a drop-down list. Then, enable a parsing option for data received (fig.4a, pos.2) and select the necessary data export plug-ins. The DDE server (fig.4b, pos.3) will help us to check that the data packet is parsed and exported. The "Direct Excel Connection" plug-in will write data directly to a Microsoft Excel spreadsheet.

Fig.4a. Serial port data and Excel. Data parser plug-in.

Fig.4b. Serial port data and Excel. 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.4a, pos.1). The dialog window below will appear on the desktop (fig.5).

Fig.5. Serial port data and Excel. 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 report in this example is very long, then we should increase the "Timeout" value (fig.5 pos.#3) 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 28 data items (see fig.1) namely: DDATE, DTIME, TLFLOW, TLVOLM and so on, which 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 and load it on the "Data parsing" page (fig.6 pos.#8).

Fig.6. Serial port data and Excel. Parser items.

Any new items may be added by clicking the "Add item" button (fig.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. For this example all 28 variables with their corresponding descriptions have been added.

Each parser item has a number of 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 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 expression. 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;
3. 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 number, the DATE type for date and the TIME type for time values;
4. 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;
5. 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: TLVOLM: ([\-\d\.]+). Here, the expression in parenthesis is our number, which we should extract to a variable. Please, search in Google for a detailed description of regular expressions;
6. 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.

In the next tab, you can specify basic format options as per (fig.7). In this example two variables have "date" and "time" data types. Therefore the first option allows you to convert the date string to a field with the "date" data type. We specified MM"."DD"."YY and HH"."NN"."SS here, according to the specification above. For a detailed description of formatting characters, please, see the help file. The second option allows you to convert the time string to a field with the "time" data type.

Fig.7. Serial port data and Excel. Format of items.

Other options are unnecessary in our case, because all our items do not have the "datetime" or "string" data type.

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 the variables names and their values (fig.8) in the DDE server window below.

Fig.8. Serial port data and Excel. DDE server window.

All the variables are now ready for export to "Direct Excel Connection" plug-in. Please, open the configuration window of the "Direct Excel Connection" plug-in (pic.9) by selecting the module in a list and clicking the "Setup" button on the "Data export" page.

Fig.9. Serial port data and Excel. Excel connection.

Just select options in the field #1 and the field #2. These options will allow you to start Excel and display it on your desktop. Excel will be started automatically on program start.

Because we want to draw a chart in a real-time environment, then we should prepare an Excel file before the next step. In this example we've created a diagram with 3 plots. Each plot is based on a data from columns A, B or C. We'll place FLOW1, VOLW1 and TEMP1 to columns and the chart will use these values and automatically redraw every time. We'll use last 30 values on the diagram only.

Fig.10. Serial port data and Excel. Excel file.

The figure above contains following elements:

1. Data source for plots in this example;
2. Diagram;
3. Excel worksheet.

The Excel file, which we've created you can download here and use it in your work.

In the next tab, you should specify your workbook options as per your requirements (real-time charting).

Fig.11. Serial port data and Excel. Workbook options.

You should select the "Use a workbook from a file" option (fig.11 pos.1) that allows you to use an existing file and write data to this file. The path and name of this file, which you created before (fig.10), you should specify in the field #2. The option at the pos. 3 allows you to save all the written data when the program exits.

Fig.12. Serial port data and Excel. Worksheet options.

In the next tab, with help of the option at the pos. 3, you should specify your worksheet number in the workbook. We've create the workbook, where the worksheet is first (fig.10 pos.3). Therefore we've specified this number on the ""Worksheet" page.

The last page "Binding" (Fig.13) is very important. On this page the column description and column position that you require to be bound to the variables names are specified.

Fig.13. Serial port data and Excel. Binding.

Any new items may be added by clicking the "Add item" button (Fig.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. For this example 3 variables with their corresponding descriptions have been added.

Each data export item has a number of properties:

1. Parser item name - is a parser variable name, which you have created in the parser configuration. You may select a variable name from a drop-down box or type this name manually;
2. Filling mode - data is sent to Microsoft Excel in this mode in such a way that if the number of cells for filling is exceeded, data is moved upwards (if it is filled by columns) or to the left (if it is filled by rows) and new data is written to the position that becomes unoccupied after data is moved. Thus, the FIFO filling method is used. In this example we need last 30 values, therefore we've selected the "Move" method;
3. Filling orientation - In this example we need to send data to Microsoft Excel, by columns. Therefore we've selected the "Vertically" method;
4. Left top cell – here you should specify the coordinates of the upper-left cell starting from which data will be sent to Microsoft Excel. The format the coordinates are specified in should comply with the standard accepted in Microsoft Excel. For example, a record like A1 will mean the coordinates of the top left cell on the worksheet;
5. Cells to fill count – here you can specify the maximum number of cells in a column or in a row that will be filled when data is sent to Microsoft Excel.

Click the "OK" button and close the Direct Excel Connection plug-in configuration window and the "OK" button in the options window.

Okay, all settings have been completed and we are ready to capture data to an Excel file.

If the data export module and the parser had been correctly set up, then you should see real-time drawing in Microsoft Excel (fig.14).

Fig.14. Serial port data and Excel. Drawing.

The figure above contains following elements:

1. Last 30 values of the parsed data ;
2. Diagram with plots.

The Excel file with all values and plots you can download here.

Related articles:

Capture a serial data to a Microsoft Excel spreadsheet (features: ASCII data parser with regular expressions, Excel export);

GPS data logger (features: ASCII data parser, data at fixed positions);

Weight data logger to a Microsoft Access database (features: ASCII data parser, data filter, data at fixed positions, export to Microsoft Access via ODBC);

Barcode data logger. Capture a barcode data from a serial port (features: ASCII data parser, data at fixed positions, export to a CSV file);

Barcode data logger. Capture barcodes to a log file (features: ASCII data parser, splitting by timeout, date/time stamps, advanced log file hints);

PBX data logger. Capture a serial data from PBX systems (features: ASCII data parser, data at fixed positions, export to Microsoft SQLServer 2000 via ODBC);

Log weight from a scale to a Microsoft Excel spreadsheet (features: ASCII data parser, data filter, data is separated by comma, export to a Microsoft Excel file);

Pressure data logger. Log data from an intellectual pressure measuring device (features: data query, ASCII data parser, at fixed positions);

Serial data acquisition in the Checkweigher Management Information System (features: ASCII data parser with regular expressions);

Serial port data aggregation. Combine a data of two different com-ports to one MySQL table row (features: ASCII data parser, aggregation, MySQL);

Serial port data and Excel. Real time charting. (features: ASCII data parser with regular expressions, direct excel export, charting);

Send serial port data to Excel via DDE. (features: dde server, charting);

Aircraft engine monitor (example of an usage, custom data parser module is developed for a customer);

RS232 to Excel. Four methods to send data from RS232 to Excel.

Read more about:

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