Serial Numbers, barcodes and QR codes with Affinity Publisher

[ferreirex]

Hi everyone,

Is it possible to create an automation with Affinity Publisher to generate Serial numbers, barcodes and QR codes?

Exemple:

I need to print from 0 to 900 serial numbers cards with Number and Barcode and QR Code, and I have created an template to be the maximum size of A4 page, but I don't see any way to generate that.

There is any way to do that?

[v_kyr]

No barcodes and QR codes with APub itself not, as it hasn't any feature for creating these itself. But you can use some third party tool for this, which then does generate barcode/QR codes as images, or SVG and then import these as needed.

For creating a QR code via a Python script see also ...

... the essential code portion for creating a QR code are in that script. - You can use that code and add a FOR loop which generates/creates 900 incremented etc. serials and QR codes inside a CLI Python script then, so you can import the results into APub afterwards.

[David in Яuislip]

Inkscape/Extensions/Render/Barcode/QR code on the left
v_kyr's on the right

Both decode with
https://products.aspose.app/barcode/recognize/qr#
to ABC00000001

It's times like these that I realise that I know nothing
Nice one @v_kyr

[ferreirex]

This mean I will need to create everything in independent platform (QR and Barcode) and after that on AP I need to create the spaces for QR and barcode automatically pick and place?

[v_kyr]

2 hours ago, ferreirex said:

This mean I will need to create everything in independent platform (QR and Barcode) ...

Yes that does it mean. - You would here then make use of the APub data merge features, in order to fit the right associated "SerialNrs & QRs and/or Barcode" peaces together. - See related:

• Data merge
• Data Merge Layout Tool
• ... etc.

[ferreirex]

Thank you very much guys.

Need to look if I create a request the ability to do this with some update and to dont need to to use others tools.

[Paul Mc]

Is this the kind of thing you are looking for?

 

I wrote a Windows application to generate QR codes as PNGs and also a CSV file that is then used with the data merge function in Affinity Publisher. It requires a start value and a quantity and then dumps all the images files and CSV file into a folder. It is not very polished but was adequate for this job. I'm probably violating a forum rule or two if I were to post the exe here so please message me if anyone wants a copy.

 

[ferreirex]

On 4/25/2023 at 10:17 AM, Paul Mc said:

Is this the kind of thing you are looking for?

 

I wrote a Windows application to generate QR codes as PNGs and also a CSV file that is then used with the data merge function in Affinity Publisher. It requires a start value and a quantity and then dumps all the images files and CSV file into a folder. It is not very polished but was adequate for this job. I'm probably violating a forum rule or two if I were to post the exe here so please message me if anyone wants a copy.

 

Is it exactly like that I'm looking.

It's yours Windows app can export as SVG (I'm just concern about the quality when print)?

At the moment my process to have this process work isn't easy, maybe because the software I'm currently use isn't the easiest one.

Do you need to create the pages individually or as soon you tell the value the AP will create everything for you?

[Paul Mc]

@ferreirex The program currently generates PNG images. I could modify it to output SVG files instead but I'd need a few days as I'm busy with other work at the moment. How quickly would you need it?

The program creates a folder of images and also the source data file in CSV format. You then need to create a "template" file in Publisher and use the Data Merge Layout Tool to create your grid. You then insert the text and image placeholders into the first element of the grid. Then connect the template to the CSV file using the Data Merge Manager and once that is done then the Field panel allows you to assign field values to the placeholders. After that you click Generate and this then creates a new document with all the data previously generated. It adds new pages as required until all the data has been processed. It sounds complicated but after you've done it a couple of times it makes sense.

[v_kyr]

32 minutes ago, BofG said:

...the second one is set to medium.

Yes, I used the default (ERROR_CORRECT_M parameter) here from the Python qrcode module. - Though if wanted one can easily (pre)adjust that too in Python code for the qrcode generation (also via some items list as done for the factory choice). - Here's a short py example how to (pre)define the to be used error correction level then ...

import qrcode
qr = qrcode.QRCode(
    version=1,
    error_correction=qrcode.constants.ERROR_CORRECT_Q,
    box_size=10,
    border=4,
)
qr.add_data('Some data')
qr.make(fit=True)

img = qr.make_image(fill_color="black", back_color="white")

... where the "error_correction=qrcode.constants.ERROR_CORRECT_L" constant could be set up to be one of ...

• ERROR_CORRECT_L                  -- About 7% or less errors can be corrected.
• ERROR_CORRECT_M (default)  -- About 15% or less errors can be corrected.
• ERROR_CORRECT_Q                  -- About 25% or less errors can be corrected.
• ERROR_CORRECT_H                  -- About 30% or less errors can be corrected.

 

[David in Яuislip]

1 hour ago, v_kyr said:

Yes, I used the default (ERROR_CORRECT_M parameter)

Well I didn't even notice the Error correction level dropdown in Inkscape but that hardly makes me a bad person...
Anyway, I had another thought about this and as a csv file is needed for the Data Merge I decided to start with that and write some code to read it and make the svg's
So, QRcodes.csv looks like this

Qrcode,file
ABC00000001,ABC00000001.svg
ABC00000002,ABC00000002.svg
.. 
ABC00001000,ABC00001000.svg

Place the attached Python file in the same folder and run it

[Windows] Open a Powershell window, run it with .\SVGqrcodes_from_csv.py and with a bit of luck you'll see something like this

PS D:\ProgramsD\PythonMine\QRcode> .\SVGqrcodes_from_csv.py
1000 files in 4.217762 s

The svgs will be written to a sub-folder

SVGqrcodes_from_csv.py

[v_kyr]

4 hours ago, David in Яuislip said:

Anyway, I had another thought about this and as a csv file is needed for the Data Merge I decided to start with that and write some code to read it and make the svg's
So, QRcodes.csv looks like this

Well, at least a somehow good start into the right direction.

Though for me (as being a lazy dev) it would be boring to predefine the CSV file manually here at all. - So why not let Python do the boring work of fully generating the CSV file and related SVGs here?  Thus I personally would also let a Python script generate the whole CSV file too here.

Here's a tiny example how to deal with generating the (ABCxxxxxxxx) label strings then, which can also be reused for the SVG filenames then ...

def getNextSeqNo(startval, endval):
    for x in range(startval, endval):
        labelstr = "ABC" + format(x, '08d')
        print(labelstr)

getNextSeqNo(0, 11)

... when the above is run via Py it will output ...

ABC00000000
ABC00000001
ABC00000002
ABC00000003
ABC00000004
ABC00000005
ABC00000006
ABC00000007
ABC00000008
ABC00000009
ABC00000010

The label string generation can be controlled via the start/end values passed over to a "for x in range(from, to)" loop, where range here in Py means from (x to n-1), aka not including n.

Update: so all in all something like this Py script here ...

'''
generate QR codes as svg and a csv file
needs qrcode
pip install qrcode
'''
import os
import csv
import qrcode.image.svg
from datetime import datetime
start = datetime.now()
kount = 0
factory = qrcode.image.svg.SvgPathImage
dirname = "svgs"

if not os.path.exists("svgs"):
  os.mkdir(dirname)

with open(dirname + os.path.sep + "qrcodes.csv", "w", newline="") as csvfile:
  csvwriter = csv.writer(csvfile, delimiter=',',
                            quotechar='|', quoting=csv.QUOTE_MINIMAL)

  for x in range(1, 11):
    labelstr = "ABC" + format(x, '08d')
    img = qrcode.make(labelstr, image_factory = factory)
    svgfilename = labelstr + ".svg"
    img.save(dirname + os.path.sep + svgfilename)
    csvwriter.writerow([labelstr] + [svgfilename])
    kount += 1


duration = (datetime.now() - start).total_seconds()
print(kount, "files in", duration, "s")

• SVGqrcodes_to_csv.py

... which also generates the CSV file in the same directory the SVGs will be generated. - Note however that actually this sample code just generates 10 SVGs, one has to change/alter the "range values of the for loop" to generate more entries. For what the OP wanted then to ...

--> for x in range(1, 901):  ... etc., or even better pass those over as CLI script args here then (which is easy when reusing the Python argparse modul. It's left here as an exercise for those who want to adjust them that way).

 

 

[David in Яuislip]

4 hours ago, v_kyr said:

So why not let Python do the boring work of fully generating the CSV file and related SVGs here?  Thus I personally would also let a Python script generate the whole CSV file too here

That's all well and fine but you need to be clever
I don't expect Publisher users to be capable of coding but I think it's reasonable that they have a fair command of a spreadsheet
Knocking out a 2 column, 1000 row csv takes less than a minute in Excel

However, if the OP wants each label to be different rather than 4 identical labels per page then my csv example was a bit daft and should've been
Qrcode1,Qrcode2,Qrcode3,Qrcode4,file1,file2,file3,file4
ABC00000001,ABC00000002,ABC00000003,ABC00000004,ABC00000001.svg,ABC00000002.svg,ABC00000003.svg,ABC00000004.svg
ABC00000005,ABC00000006,ABC00000007,ABC00000008,ABC00000005.svg,ABC00000006.svg,ABC00000007.svg,ABC00000008.svg

Anyway, Publisher took 59.87s to generate 250 pages which I think is far from shabby

No GUI niceties, all defaults, lots of scope for improvement but it gets the job done 🙂

[v_kyr]

6 hours ago, David in Яuislip said:

Knocking out a 2 column, 1000 row csv takes less than a minute in Excel

I usually don't use Excel, just if I'm really forced to by maybe some customer related projects and then they have to provide some preinstalled Office/Excel copy on the handed over working machine and their appropriate company templates. - However, if it is easier for the OP to use Excel and some Qrcode-Generator for such tasks, then he might use that instead.

Basically one can use whatever prog-/script language for such SVG+CSV generation tasks.

Related to Python here, of course the whole could be scripted overall much better and more flexible in Python as done so far, also GUI based, but that all would need more time here and some different coding. - So we could for example even generate/build a ready generated SVG which would then have all needed things already included together, aka some outer wanted shape form, inside that a customer logo, the colored labeling number and the qrcode ... etc. For that as a generated result then just one CSV file column entry is needed. - We could also build it up as a Py GUI app instead, which then would offer to customize via appropriate UI elements all needed/wanted parameters & colors etc. - But as already said, that needs more than ~30 lines of Py code and also more time to implement it that way.

[David in Яuislip]

I've always liked Excel, it was a massive improvement over Lotus123 which itself was a ginormous improvement over Visicalc which had replaced the sliderule. A quick google search reveals that there are Excel addins that can do qrcodes including one here
https://www.keepautomation.com/purchase/
which works with my ancient Excel 2010 and costs $169
I suspect that any output would be raster which may be good enough but in the extremely unlikely event of my needing to do this in the future then I'll stick with Python

[v_kyr]

1 hour ago, David in Яuislip said:

I've always liked Excel, it was a massive improvement over Lotus123 which itself was a ginormous improvement over Visicalc which had replaced the sliderule.

I've mostly used Lotus Improv and Quantrix in the pasts.

1 hour ago, David in Яuislip said:

A quick google search reveals that there are Excel addins that can do qrcodes ...

Yes and some QR-Code Generator services too.

• Excel QR Code Generator: How To Create & Use Excel QR Codes
• How to Use a Bulk QR Code Generator

1 hour ago, David in Яuislip said:

I suspect that any output would be raster...

That Beaconstac service referenced above there, names as supported ourput formats PNG, JPG, SVG, PDF, and EPS.

If your daily job/work is on a permanant basis to work with Office related tools like Excel etc. and always generating a bunch of Qrcode based labels, stickers ... and so on, then it might be useful to use such add-on plugins for them. Or to write some own customized (or reuse some already available) C#/Basic add-on for it.

But a mere mortal user who uses such things (qrcodes etc.) only occasionally from time to time probably won't buy an extra expensive Qrcode plugin for Excel here. Instead he will look more after some free no cost way to do such things. And there are a lot of possibilities to do such things via scripting & programming.

The advantage of using Python etc. for such things is, that you can very quickly in a rapid prototyping manner get some working script solutions for such individual tasks.

 

[Paul Mc]

@ferreirex Here's a link to the Windows application I mentioned earlier. I'm hoping it is fairly self-explanatory but if not then please post here or message me if you have any questions.

https://www.nothinghereisreal.com/downloads/QRLab.zip