Weight data logger to a Microsoft Access database (data at a fixed position)

Task:

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.

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);
• ODBC 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 (weight field has only two decimal digits) and a fixed position of each item. We need one - the end of an each data record. We need analyze 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 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 "ODBC database" plugin will write data to the Microsoft Access database.

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 3 data items, which should be separated to different variables. Later, these variables (we need 2 variables only: first character and weight) will be used in the data publication and will be placed in different columns of our Access database.

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 2 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, we can use one simple method - fixed position. This method allows to extract any number of characters from any fixed position of 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 data type, and then data will have been converted to 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. Fixed position - It's a position of first variable's character in our data block. A minimum value is 1;
6. Fixed length - It's a number of characters, which will be copied to a variable. A minimum value is 1.

The second item has same parameters, just a position and a length is other.

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 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 ODBC database plugin. The configuration process isn't complex and clear for unskilled users. Please, open the configuration window of the ODBC database plugin (pic.9).

Pic.9. ODBC database data logger. Enabling connection.

An option at the position #1 enables writing to a database and an option at the position #2 specifies to keep a database in an opened state. Last option minimizes a writing time, but blockades writing to a database for other users.

On the second page "Connection" (pic.10) you can specify parameters of your database.

Pic.10. ODBC database data logger. Connection options.

Two ways exists here:

1. You can create a fully configured ODBC alias (by clicking the "Setup" button at the position #3 on pic.10);
2. You can select the database type only and configure a connection with additional connection attributes (at the position #2 on pic.10).

We had selected the second way, because it's most quick in this case.

In any case, select your alias name or the database type in the field #1 on pic.10 (if you had configured an ODBC alias after opening the configuration window, then, you should close and open it again).

In our case we should specify a full database path and name in additional attributes. We should use the "DBQ" attribute for it (see pic.10, pos.2). A full list of supported attributes you can find in a help file.

Few words about a database. Yes, of course you should create a database and a table in the database by hands. It's simple, if you are using Microsoft Access permanently. If you don't have Microsoft Access or don't know it, then you can download a database sample here.

We had created a database with a name "database.sample.mdb". In this database we had created a table with a name "SERIAL_DATA". This table has 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" (pic.11) you can specify a reaction of the software to errors, which can occurs while writing to a database.

Pic.11. ODBC database data logger. Errors handling.

We had selected the last option because our data is important and program should operate in any case. Other variants are using when are testing the plugin module with a new database.

The last page "Binding" (pic.12) is very important. On this page you are specifying a table name and its columns, which you are binding with variables names.

Pic.11. ODBC database data logger. Binding.

Because we had created the table with a date time stamp, then we need just one - bind FVALUE column in the table with VALUE variable. Please, create one item on this page by pressing the "Add item" button (pic.11, pos.6).

Before adding an item the program will ask you about a table name (pic.11, pos.1), and then, a table column name (pic.11, pos.2). The name of a table column should be same, which you had specified while designing a database.

You can add all items simply by clicking the "Import" button (pic.11, pos.7). Before clicking the "Import" you should configure a database connection as described above. In this case a dialog window will appear (pic.12) and you should select a necessary table name (pic.12, pos.1) and click the "OK" button.

Pic.12. ODBC database data logger. Selecting the table.

Each item on the binding page 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 data type - it's a database column data type. The module will try to convert a variable's data type to a column data type, with using standard system functions;
3. Default value - this value is being used when data doesn't contain value for a column or variable is empty.

Click the "OK" button and close the ODBC 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.

If you have configured the ODBC module correctly, then while accessing a database the Advanced Serial Data Logger software will display messages in the drop-down box, at the bottom of the main window.

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