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
