• site map
• contact
• support
• articles
• order
• news

Weight data logger to a Microsoft Access database (data at a fixed position). Advanced Serial Data Logger tutorial.

Problem scenario:

I am taking a weight from a scale ever couple of seconds and I want the software to take that weight via the serial port and load it directly into a database with a date and time stamp on each entry. I want this to be software that will start with Windows and hands free to the
operator. The database is located on a network drive and it will not be open at the time, unless the software has the ability to open Access.

Fig.1. Serial data captured

Requirements:

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

It is assumed that:

You have configured all communication parameters (baud rate, number of data bits, flow control etc) in the data logger and you are able to receive all data without communication errors.

Solution:

The image (Fig.1) above shows a simple data flow. Each record has a fixed size and a fixed position for each item. As an example, for a single record, all non-printable characters with a code less than 20 Hex are selected for display. The indicated radio buttons are set as shown below.

Fig.2. Weight data logger. Serial data view setup

Click the "OK" button and attempt to receive data from a serial port. You should get something like the image below.

Fig.3. Weight data logger. 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) ends with #0D (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 to check the accuracy of the parser's configuration. The "ODBC database" plug-in will write the data to the Microsoft Access (Fig.4b, pos. #4).

Fig.4a. Weight data logger. The data parser plug-in setup.

Fig.4b. Weight data logger. Data export plug-ins setup.

Next, open the ASCII parser and query configuration window (click the "Setup" button near the drop-down box as indicated on pos.#1 on Fig.4a). A dialog window will appear on the screen (Fig.5) about the data packet.

Fig.5. Weight data logger. 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 the example, our data block does not contain a start marker. Therefore, this field is left empty. The values to be typed in here are as underlined in red in Fig.3 above.

Several records in the data flow 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 "O" or "T" characters and we should create corresponding filter rules (Fig.6).

You will be adding a new filter rule (Fig.6, pos. 1) by clicking the "Add item" button (Fig.6, pos. 2).

Fig.6. Weight data logger. Filter settings.

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 2 data items (see Fig.1) namely: first character and weight, 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 Access database.

Fig.7. Weight data logger. Parser items.

Any new items may be added by clicking the "Add item" button (Fig.7, 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 2 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 is being placed in fixed place, we can use the simple method - fixed position. This method allows the extraction of any number of characters from any fixed position of the data record;
3. Data type - is the data type of the characters extracted. For example, you can specify FLOAT 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, the "Call date and time" field has the DateTime data type, the "Call len" field has the Integer data type. All other fields have the String data type;
4. Default value - the value specified here is to be used when data cannot be extracted from a data record;
5. Fixed position - is the position of the first variable's character in our data record. 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 the same parameters, a position, a length and a data type of other.

In the next tab, you can specify basic format options as per (Fig.8). If you had specified the data type "String" in the item's parameters, then the first two options allow you to remove blank spaces from a value. Other options are unnecessary in our case, because all our items do not have the date or time data type.

Fig.8. Weight data logger. Format of items.

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 serial port. If the parser had been correctly set up, then you should see all the variables names and their values (Fig.9) in the DDE server window below.

Fig.9. Weight data logger. DDE server window.

We are now ready for configuring of the ODBC database plug-in, that will export data to Access. This configuration process is not complex and might not be difficult for the unskilled user.

It is assumed, that you have access to the database and can manage it. First of all, create a table in a database.

Next, select the "ODBC database" plug-in (Fig.4b, pos. #4) from a list and open the configuration window of the ODBC database plug-in by clicking the "Setup" button below the list. A dialog window will appear on the screen (Fig.10).

Fig.10. Weight data logger. ODBC database. Enabling connection.

The radio button at pos. #1 enables writing to the database and the tick-box at pos. #2 specifies that the database is to be kept open. This serves to minimize the writing time, at the same time locking out other users from writing.

On the second page "Connection" (Fig.11) you can specify the parameters of your database. Here select your ODBC alias name which you created above at pos. #1. Note that if you configured an ODBC alias after opening the configuration window, please click the "Refresh" button.

Fig.11. Weight data logger. ODBC database data logger. Connection options.

You may configure the connection with your database like that:

1. Create a fully configured ODBC alias (by clicking the "Setup" button at the position #3 on Fig.11);
2. Create an ODBC alias and specify a database type (Microsoft Access) for this alias only and specify all other connection attributes in the module (pos. #2 on Fig.11).

In this example, we've selected second method, because this is most quick and easy and no additional attributes are specified, except the database file.

Because an Access database is placed in a file, then you should select this file in the corresponding field (Fig.11, pos.2). You should specify the full path and name here.

A few words about a database. You should create a database and a table in the database yourself with help of Microsoft Access. If you don't have Microsoft Access on your PC or didn't create Access databases before, then you can download a database sample here.

We've created a database with the "database.sample.mdb" name for this tutorial. The database contains a table with the "SERIAL_DATA" name. The table contains following fields:

1. FVALUE (numeric);
2. FDATE (date);
3. FTIME (time).

For items #2 and #3 we had assigned functions Date() and Time() as default value, and we had got a date time stamp for an each new record.

On the third page "Errors handling" of "ODBC database" plug-in (Fig.12), specify how the software should react to errors which occur while writing to a database.

Fig.12. Weight data logger. ODBC database data logger. Error handling.

In this example, the last option (pos. #1) was checked as the data is important and we require the program to operate regardless of the error. The other variants are normally used when testing the plug-in module with a new database.

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

Fig.13. Weight data logger. ODBC database data logger. Binding.

Because we've created the table with a date time stamp, then we should bind one colun only: the "FVALUE" column in the table with the "VALUE" variable.

You can create item by item on this page by pressing the "Add item" button (Fig.13, pos.#6). In this case, before adding an item, the program will ask you for the table name (Fig.13, pos.#1) and then the column name (Fig.13, pos.#2). The name of a table column should be same as that specified while designing the database. Adding each item separately is a tedious and long process. It is simpler to add all items at once by clicking the "Import" button (Fig.13, pos.#7).

However, before clicking the "Import" you should configure a database connection as described above. In this case a dialog window will appear (Fig.14) and you should select the necessary table name (Fig.14, pos.#1) and click the "OK" button.

Fig.14. Weight data logger. ODBC database data logger. Selecting the table.

Each item on the binding page has a number of properties:

1. Parser item name - is a parser variable name, which has been created in the parser configuration. Select the variable name from a drop-down list, which appears while clicking on a link for this property;
2. Column data type - is a database column data type. The module will try to convert a variable's data type to a column data type, using standard system functions. If your variable has a data type other than "string", specify the variable data type here;
3. Default value - this value is to be used when data does not contain a value for a column or the variable is empty.

Click the "OK" button to close the ODBC database plug-in configuration window as well as the "OK" button in the options window.

Okay, all settings have been completed and we are ready to capture weight data from scales to the database.

If you configured the ODBC module correctly, then while accessing the database the data logger will display messages in the drop-down box, at the bottom of the main window.