Log weight from a scale to a Microsoft Excel spreadsheet (data at a enumerated position)

Task:

We are filling bags of powder on a 6 head filling machine and require to record each bag weight as it comes off the filling machine. At present the incoming data from the RS232 is viewed on a log file as "DD MON YYYY, HH:MM,HEAD NUMBER,HEAD WEIGHT" as it comes in. At the end of each hour, a brief 1 line summary of the production is sent to the log file.

We would like to have this data sent directly into a Microsoft Excel spreadsheet. An each weight recorded to be in a row. Each log file for each day to be a new spreadsheet. Each new sheet is to be labeled as the day of the data recording. Typically there will be approximately 1000 recorded data entries for the production for the day. We only require the "date" and "head weight" figure to be inserted into the spreadsheet.

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 flow is very simple. Each record has a fixed size (the time field value leading with spaces and the weight field has only three decimal digits) and a fixed position of each item. We need one - the end of an each data record. We need analyse it. We'll enable a display output for non-printable characters with a code below that 20 Hex. Please, set following options.

Pic.2. Serial data view setup

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

Pic.3. Data logger. Data received.

It's an other view of data received. All non-printable characters were replaced with their code like #0D. Now, it is clear, that the data block (in a green rectangle) ends with #0D (underlined by red).

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

Pic.4. 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.4). A dialog window will appear on a screen (pic.5).

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.3). 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 (this field is empty) and the field #2 is the end of him. On the pic.3 a value, which you should type have been underlined by red color. Because our data flow contains data blocks, which should not be being analyzed (one summary record per day, see pic.1), 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 4 data items, which should be separated to different variables. Later, these variables (we need 2 variables only: date and weight) will be used in the data publication and will be placed in different columns of our Excel 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 4 variables with corresponding descriptions.

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 fixed place and separated by comma, we can use one simple method - enum(erating) value. This method allows to extract a data from any position of the data block separated by any character(s);
3. Data type - the data type of characters extracted. You can specify a real type of your data or specify the STRING data type, and then data will have been converted to an 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. Our "Weight" field has the Float data type, the "Number" field - the Integer data type, the "Date" field - the String data type (because the plugin can't convert our string date to the date data type);
4. Default value - this value is being used when data can't be extracted from a data block;
5. Enum(erating) separator - In our case we needs numbers, which divided by comma character;
6. Enum(erating) position - all our values divided by comma character, but just a position of each item is variable. Please, specify an entrance position here.

All other items have same parameters, just the data type and an entrance position is variable, from 1 to 4.

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. The second option allows you convert the time string to a field with the time data type. Because our time string leading with spaces (not zeros) we need specify the single H as a hour format (detailed description of formatting characters, please, see in a help file).

Pic.7. Data logger. Format of items.

Other options are unnecessary in our case, because all our items don't have the date and datetime 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 device 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 simple. 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" (pic.10) you can specify a representation of your data.

Pic.10. Excel data logger. Format options.

Because one our variable has the float data type, then we need specify a necessary decimal separator in the field #1. Other variable has the time type, therefore specify time separator in the field #2 and the option in the field #3, which helps to add zero before an hour digit. All other options here is unnecessary in our case.

On the last page "Columns order" (pic.11) you can specify, how our variables will be being placed in a spreadsheet. Because we need 2 variables only, therefore, please, create 2 items on this page by pressing the "Add item" button (pic.11, pos.5).

Pic.11. Excel data logger. Column order options.

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.

Each parser item has few properties:

1. Parser item name - it's a parser variable name, which had been created in the parser configuration. You need select a variable name from a drop-down box, which appears while clicking on a link of this property;
2. Column order - it's a position of a variable in a spreadsheet. A minimum value is 1;
3. Column length - it's a column width in a spreadsheet, you can change this width in Microsoft Excel later.

Click the "OK" button and close the local database plugin configuration window and the "OK" button in the options window.

Ok. All settings had been done and we are ready for capturing weights from scales.

 

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