News

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

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:
Date and time partially captured, weight captured completely

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*:
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:
Write values in three columns

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

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):
Time and date 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:
Capture weight in grams

Now, only values expressed in grams [g]  will be captured:
Weight in grams captured (shown in blue)

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:
Combine 3 lines into one row

The CSV file generated by SDL can later be opened in Excel by simply double-clicking on it:
Excel with computer date/time and weight

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:
Generic text device

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):
Combine 3 lines into one row

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):
No date and time added

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

Links and further information