Date - Smartlux SARL

For this article we assume that your scale is equipped with an interface and that it sends the date, time and weight in the following format:

Date 10/30/2018
Time 11:58:45 AM
Gross 376.5 g

We present two methods for transferring these values to Excel.

1. Use a keyboard simulation (virtual keyboard wedge)

You can use our software 232key to enter the values into Excel (or any other application) as if you had typed them on your keyboard. However, by default, 232key captures the first number in each line, which means it would only type the values shown in blue:

While the weight has been captured correctly, the date and time are incomplete. This can be fixed in 232key Plus as follows.

1. In the Input tab, set the device to “Barcode alphanumeric extended*:

This enables 232key to type all printable ASCII characters including the slash and colon we need for the date and time.

2. Modify the regular expression 232key uses to capture data by clicking on the Customize button. Replace the existing expression with the following one:

\s*(\d+/\d+/\d+|\d+:\d+:\d+\s[AP]M|[-+]?\s*\d*.?\d+)

This expression will capture the date, time (in 12h AM/PM format) or weight. This only works if the values are transferred in individual lines, as in our example.

3. In the Output tab, instruct 232key to send “TAB” after the two first values and “Enter” after the third one:

232key will then write the date, time and weight in separate columns and jump to the next row:

2. Write the weight to a file (with date and time) and later open it in Excel

As an alternative to the “keyboard simulation” method described above, you can use our software Simple Data Logger (SDL) to write the weight to a file. This file can later be opened in Excel (or other applications).

This approach has two advantages:

Other keyboard operations can’t interfere with data acquisition and
Excel does not have to be open and running in the foreground (it does not even have to be installed on the same computer).

It is therefore particularly suitable for long-running unattended operations.

Concerning the date and time, you have two options when using SDL: Use the computer’s date and time or use the date and time sent by your scale.

a) Use the computer’s date and time

You’d usually choose this option if your scale only sends the weight or if the date and time format used by your scale does not match your requirements.

1. Choose a file in the Output tab in SDL. Click on the Set values button to set the default date and time formats (you can also directly modify the format strings, e.g. to use a 24h time format):

2. To make sure that only the weight is captured (and not the date and time sent from the scale), go to the Input tab, click on the Customize… button and add “\s*g” to the regular expression:

Now, only values expressed in grams [g] will be captured:

3. Even though they’re not captured, the other two lines sent from the scale (date and time) will still be written to the file as empty lines. In SDL Plus, we can combine all 3 lines into one row in the Process tab:

The CSV file generated by SDL can later be opened in Excel by simply double-clicking on it:

As mentioned above, the two empty columns preceding the weight appear due to the date and time values which were sent from the scale, but were not captured. You can simply delete them.

b) Using the date and time sent by your scale

The procedure is very similar to what we did with 232key above.

1. In the Input tab, set the device to “Generic text device” to prevent SDL from treating the captured values as numbers:

2. Replace the regular expression by clicking on the Customize… button:

\s*(\d+/\d+/\d+|\d+:\d+:\d+\s[AP]M|[-+]?\s*\d*.?\d+)

This will ensure that the date, time and weight values sent by the scale are all captured.

3. In the Process tab, combine the 3 lines sent from the scale into one row (functionality only available in SDL Plus):

4. In the Output tab, make sure that the date and time are set to “None” (unless you also want to add the computer’s date and time to each row):

The event log in SDL shows that all three values were captured:
The resulting file contains the date, time and weight sent from the scale neatly written into 3 columns:

Links and further information

Our software 232key was originally designed to make it as easy as possible to capture the weight sent by a scale or balance (and then transmit it to another application as simulated keystrokes). As most weighing instruments also include the unit and possibly other characters, 232key assumes that the first (decimal) number it encounters is the weight. If you also want to capture the date and time sent from a scale, this can lead to unwanted results.

Capturing the net weight

Let’s look at a simple example first. Below you’ll see the data received from an Ohaus Scout STX scale configured to transmit only the net weight (as it appears in the event log in 232key):

Net: 302.4 g ? N<13><10>

The blue characters were captured by 232key using the “Ohaus” device profile. In this example, 232key managed to successfully capture the weight.

Capturing the net and tare weight

If we change the “Print Content” of the Ohaus STX scale so that it sends both the net and tare weight, the event log shows that 232key still captures both values:

Net: 302.4 g ? N<13><10>
Tare: 7.9 g T<13><10>

To have them appear in separate columns in spreadsheet applications like Excel, OpenOffice Calc or Google Sheets, we can go the output tab and modify the end with settings as shown below:

Alternating the “TAB” with the “Enter” key every 2 value will produce the desired result (shown in Google Sheets):

Capturing date and time

Things get significantly more complicated if we configure the scale to also send the date and time. The event log now looks as follows:

11/17/2017 15:04:21<13><10>
Net: 302.4 g N<13><10>
Tare: 7.9 g T<13><10>

Using its default strategy of capturing the first number in each line, 232key only captures “11” and ignores the rest of the combined date/time line (as the backslash cannot be a part of a decimal number).

We can try to fix this by going to the device tab and setting the device to “Barcode alphanumeric extended” (available in the Plus version). This device appears in italics, which means that it is a “text” device instead of a “numeric” device. Note: Do not click the set defaults button after making this change.

As the scale is now sending three lines of data, we should also enter the number “3” in the end with settings in the output tab:

If we press the print button on the scale again, the event log will show that all data has been captured by 232key:

11/17/2017 15:09:51<13><10>
Net: 302.2 g N<13><10>
Tare: 7.9 g T<13><10>

The output in Google Sheets looks as follows:

If we simply wanted to record all data sent from the scale, this would be fine. However, it is not ideal for further processing for two reasons:

Date and time appear together in one column.
The measurement values are not recognized as numbers because the cells contain additional text.

232key does not support extracting multiple values from one line of data, so there’s nothing we can do about the first issue (although you could use 232key Pro with a custom script instead). Unfortunately, our Ohaus Scout STX scale cannot send the date and time in separate lines.

The second issue can be fixed using custom regular expressions. To do this, go to the device tab, click on the customize button and replace the existing regular expression with the following one:

(\d{2}/\d{2}/\d{4}\s+\d{2}:\d{2}:\d{2}|-?\d*\.\d+)

Note: This regular expressions assumes the scale is using the 24 hour time format.

The event log and the output in the spreadsheet now look as follows:

15:38:40 11/17/2017 15:39:01<13><10>
15:38:40 Net: 302.2 g N<13><10>
15:38:40 Tare: 7.9 g T<13><10>

The cells containing the net and tare weights are recognized as numbers and can be used for further calculations.

If we just wanted to capture the time (and maybe add a “date” column manually), we could use the following regular expression:

(\d{2}:\d{2}:\d{2}|-?\d*\.\d+)

Please note:

The regular expressions above have been customized to work with the Ohaus Scout STX scale (configured to send the date, time and weight). They might not work with other scales and balances.
As we’re now using a “text” device, the computation and rounding settings in the process tab have been disabled (you can’t round non-numeric characters).
The option to change the decimal separator from a dot to a comma is also no longer available (and our custom regular expression does not capture decimal numbers using a comma as the separator).
For data logging purposes, consider using our new Simple Data Logger software instead of 232key. It has the ability to add the date and time to each weight. The CSV files it produces can be easily opened in Excel and other spreadsheet apps.

Tag: Date

How to write the date, time and weight from your scale to Excel