Handheld Barcode Scanner & Data Entry with AHK’s Part 1

Initial code setup for plant monitoring here. There is a more updated script in the next article here.

Timekeeping original code

There is a really nifty script (by yevolcn on GitHub) that allows you to quickly add in time in a pop-up menu triggered by Alt+3 hotkey. It writes the information to a file and you tell it where the file is to be. Link here

I think this is really neat and useful, thankyou yevolcn.

A recording process over time

The idea of this process, is for “monitoring” & “analysis”. I want to collect data on a process,. the task planned is my plants, their feeding and growth rates.

I would like to scan the ID of the plant, the time I water/feed it, and its condition at the time. Maybe I could measure height & number of leaves or leaf length as well.

Possibly I could hook that in with weather conditions of the day and other information about time of watering.

So I collect the data on a regular basis. When I have enough data points I can study performance over time.

This is a passive use of bar-scanner, I’m recording information. A further process is using it with an inventory, for my Freezer, to add and remove items and to see current inventory. That is a different process that will need something like a Glide App so that you can see what is stored and what needs to be added. You can also plan meals based on what is available.

A further process for freezer inventory (another project)

That will require that you identify bulk items (bread rolls in quantities of 6 say) that you will use on an individual basis. So you need the scanner to do different recording processes.

Once you have inventory loaded you can then use scanner in a passive way of just recording what you have used. I wonder if you can write a AHK script to send data to google sheets.

There is an article aboutsending to GSheets with api and Aoth etc here. An alternative is to use Google Forms, that may be the method to use as I’ve already played with that process and you don’t even need to open the form. So make a script to gather info and send off to form.

Initial process idea, using Excel/Notepad

I’ve been thinking about how to get scans from a handheld barcode scanner onto an excel spreadsheet or something similar. This is how my thinking was going:

1. Scan a barcode that will trigger a AHK script.
2. The AHK script will open a programme, eg Excel , notepad++ or other
3. Then open a file, from a specific location
4. Then go to a specific column (in Excel ) and go to the bottom to find the last row so that new data can be appended), or in Notepad, just go to end to append.
5. Then scan activities with Scanner, this gets fed into programme, then close the programme down until called again.

Modified process idea just using AHK script

I was playing with the RecordTime script above and thought , why not just send it to AHK script and let it do the work. I am rather slow, old age and senility I suppose.

1. Scan a barcode that will trigger a AHK script.
2. Send barcode data to GUI in script
3. Script sends to file (.Txt or .CSV)
4. These can be appended to existing data files in Excel or other programmes

There are fewer steps, 3 in all to scan to a file. You then have to use that data somewhere, but that is another process. I’m focusing of capturing data at present.

Prototype AHK script

I looked at adapting the above RecordTime script into something I could use. It solves a number of simple things straight away. It gives you time and date & user from the computer, whoever’s logged in. So that’s some work already solved. It also pushes data into a specific file. It is setup for a .Txt file but i adapted it so that it sends to a .CSV file as well.

So I started developing the process and got a prototype working where I could:

1. Scan a barcode to open the Script & popup the GUI
2. Put the cursor into the first input box (I move the GUI lines around so first item in rows was input box)
3. After populating first box with data, to tab to the next input box to be ready to receive another bit of information
4. After completing input tab to OK button
5. I need to scan a Carriage Return and send another QR code via scanner for it to press the OK button and accept the data.

Issues with process

The barcode scanning to start AHK script works fine.

I then have to set scanner to be in ADD TAB Mode, so I need to scan a specific barcode for the scanner to do this. This will allow me, as my cursor is already in input box 1, ready to receive barcode data, to then TAB to the next place after Data is streamed from Barcode.

I may need to put a Barcode Scanner code in before this. To ensure it’s a blank setup that will let barcode reader then accept ADD TAB qr code. I’ve yet to give this a rigorous test.

Now after each input from Barcode or QR code, it tabs out of that entry to the next item, generally to another input box but finally to OK button, which will then take the input data and when enter pressed, push data to file and close GUI.

So you have to change configuration of Barcode scanner to ADD CARRIAGE RETURN instead of ADD TAB, because CARRIAGE RETURN= {Enter}. Then you can scan another input barcode and the Enter will trigger the pushing of the button.

I did find article no stack overflow of alternative way to close GUI here that I may need to explore.

Sound to signify script ran

After input and enter, I used SOUND to make a few different beeps to indicate that the data had eeen received successfully and sent to file.

This is useful, as you re just scanning “Things”, and are not looking at PC and input information, so you need something to tell you that your scan has been successful.

Hand Scanner Presets

I was making a spreadsheet with all the Barcode Scanner codes in so that I could create my own QR , barcode or MatrixCodes for them, they have a %%SpecCode93 structure, with the numbers at the end changing to other variables. Unfortunately Google Sheets will not generate QR codes from this , I don’t think it likes the % sign. You can generate QR codes with online QR code Generators, just takes a bit more time.

I had a G Sheet for this but I couldn’t find it so I have to start from scratch. You need to use the phone scanner to scan the QR codes in the book then take that info and put it in a spreadsheet. A long and tedious task, I think I’ve lost about a days work from that GSheet disappearing. Cest la vie.

What this means in terms of scanning is that you have to make sure that the scanner pre-sets are correctly set.

1. That you are in correct mode (continuous/on trigger/light change
2. Scan on wifi 2.4 frequency
3. Scan to Dongle /bluetooth, store
4. and default action after barcode scan, ADD TAB, Add LineBreak, Add Carriage Return (Enter).

So if you need to change default action after scan (item 4 above) then you need to scan a specific QR code that will reset the scanner.

This data only affects scanner, so you cannot incorporate it into other QR codes that you are sending to input fields, so you have to add another scanning step to your process, and if its missed out it can cause issues of the data not being sent correctly or even at all.

Prefix & suffix

I was thinking about using prefix to put the barcode scanner code in before the main data code, but I don’t think that will work. Maybe worth exploring though. I did see it mentioned in an article about a different barcode scanner.

It might mean a more dense QR code, but there will only be one of them.

Tray Tip from code not displaying

Now, in the app, when the GUI is filled out, on pressing the button you get a TrayTip/Toast popup in bottom left of screen. Or at least I did on one PC, but not on another. So I did a bit of goggling and there seemed to be a few AHK users who’ve had the issue. I found an answer on the AutoIt forum here.

winkey + I -> System -> Notifications & actions -> enable notifications

So you have to make sure you have notifications for apps turned on in Win10.

Data Structure for Barcodes

I’m still exploring this, but a couple of things stand out.

First, what type of Barcode? QR code, 1D or other types such as DataMatrix or Aztec?

This is based on convenience of generating codes and also Label maker in can it create that type of code? I need labelmaker to see what can be done there. If one for QR or 1D Barcode is not best, then I think I can use image instead. To be tested.

QR codes seem the easiest way to go as GSheets can generate a lot of them (no spaces in text (so need to use underscore (note AHK script can do StringReplace, Thing1, Thing1, rn,{TAB} , All to take underscores out and replace them with spaces later))

The Dymo label-maker will also do qr codes, and the scanner finds them easy to read too.

DataMatrix codes I want to try, as well as Aztec codes and will need to find a use-case for them.

As GSheets doesn’t like the Scanner preset codes I’ll have to generate them via a different method. I need to find some way of generating a lot at a time, that is why GSheets is so good. This website looks good for generating multiple QR codes all at once & sending zip file

Second, what type of data coding? Simple numbers, a number letter combo, uppper case or Mixed case?

I started to convert QR codes to 1D codes, and strings of text ended up being very long barcodes that were difficult to scan, being too long.

So brevity is the key, then , even if you convert to a different type, they still work. Also you want enough variability so that you can hopefully get an indication by the code as to what it is, B01 for bread, O01 for orange, a bit simplistic but maybe useful if you need to type in a search.

Being short means that you can make codes smaller in size as a QR code will not be carrying much data so will not be very granular.

The code is a pointer to data, all it needs to be is unique in the database, so its only looking up one unique item, although you may have variences, such as sausage type 1, type 2 etc. The codes do not need to be too elaborate as you’ll be doing a lookup on the code as Key.

As long as the data lands in the right fielsd it can be as simple as 1,2,3. As an ID column, as long as that does not end up in a quantity column instead.

Data structure for File

The file format is a .txt file, I wanted it to be .csv (Comma deliminated values) so that you can easily import it into spreadsheet programmes. So I needed comma’s between fields and a `r`n at lines end (`n on its own doesn’t necessarily work). So I set up data to be in this format and it tests fine for uploading to Excel & GSheets.

Importing Data into Google Sheets or Excel for analysis

A Csv file is a single file, so in a spreadsheet it is a single tab/sheet in a spreadsheet workbook. Importing a CSV will usually mean just populating a single tab. But you want to append the data, so one process is to load to a blank tab and then move it to the bottom of another tab. The challenge is to automate the task and , at the same time, not over-bloat the spreadsheet file (keeping original data in separate tab also). So in Excel you can use VBA to write a macro,GAS in GSheets, or else, in GSheets there is IMPORTDATA (there may be an equivalent in Excel) so I need to explore the options.

This is an article about getting csv into Excel- have to think about appending. This is an article about google sheets, an Add-in for GSheets and also =IMPORTDATA()

End comment

At this point I have a prototype process, a method of using scanner to push data into a file. Its not tested to any great degree, but the process works.

The scan for scanner setup and scan for data is a bit of a nuisance , and I need to see if there is a more refined method for doing this. Its basically converting to carriage return and then back to tab.

I want to be able to setup my Surface 3 with dock in the kitchen and use that as an interface, so after setting up on PC’s I want to transfer to that so that it is handy and accessible. It is a good PC but no keyboard at the moment, although I should setup the cheap Bluetooth one to it for the moment.

I’ve spent the day re-processing the QR codes from the booklet for the Handheld barcode scanner so that I can use them. Using the scanner itself to read the codes so that I can create new QR codes in a spreadsheet. I’ve tested alternative endings such as CR ( I thought Carriage return) and LF (linefeed) but they did not do what I expected, so will have to use the original code for the moment.

I’ve got the label maker but lost the charger, so a bit of a challenge at the moment. Not a particularly productive day.