Art & Data Design Guide

Data Tutorial

Here we’ve collected some tutorials on how to prepare some basic data files for variable data printing.

We’ve created a number of files for you to use as examples or starting points. All the sample tables used here are available for download from our website at fiske.com.au/support. These will show you how we set up our data, and by following on from that, we can ensure that the personalisation process is as straight forward as possible.

Consecutive Numbering

Overview

By itself, a plain consecutive number is the easiest of data, and is usually sufficient for most applications. Easy to create, easy to set-up, the main thing to watch for is that you’ve allowed enough room in the artwork for the number. This is also good practice to start getting used to doing formulas and the moving on to doing some more complicated data in the future.

Step 1

Open up your favourite spreadsheet package. Feel free to use Excel, OpenOffice, Numbers, whatever. At the end of all this, we’re going to require only a tab delimited text file. We also try to make sure that we can open pretty much any format you send us.

Once you’ve got a need spreadsheet, enter cardNum in the first cell (A1), as above. This is our field header. In your artwork, you’ll also have a line of text with cardNum. This lets us know where you want your data to go.

Step 2

Select the next cell below (A2), and enter your starting number. In this case we’re going to start with 501, but it can be any number. 1 is a popular choice here.

Step 3

Down to the next cell, and this time enter =A2+1 into it. This is a simple equation that takes cell A2 and adds 1 to it, giving you your sequential number.

Once you move out of this cell (hit TAB), it will look like below.

Step 4

Now to populate the rest of the numbers. There’s two options here, one works well for small ranges (<1,000 numbers) and one works well for larger ranges (>1,000).

For smaller ranges, select the last cell (A3) that you entered the formulae into. You’ll notice that the cell has a thick border all the way around, except for the bottom right hand corner where there is a small square. Click-and-hold on that small square and drag it down the spreadsheet. As you do, you’ll notice the number increments as you go. Keep going until you’re up to the number you want it to finish at. This might take a bit of practice to sort out how fast it all goes.

For larger ranges, select the last cell (A3) that you entered the formulae into and hit Ctrl+C (for Windows) or ⌘+C (for Mac OS) to copy that cell. Select the next cell underneath (A4). Now for the tricky(ish) bit. Towards the top of the screen, just to the left of the formulae bar, you’ll notice a small box that will have your current selected cell in it. It should read A4. Select that box and replace what’s in it with A4:A2001 where the last number is the total number of numbers you need, plus one (makes up for the field header that plus one does). Now, type Ctrl+V (for Windows) or ⌘+V (for Mac OS) to paste that formulae into all those cells. Done.

Step 5

You’ve got your numbers, so now it’s time to save it in a format for us.

Ideally, we’d like a tab-delimited text file along with the data file in it’s native format. First up, just save the spreadsheet in it’s native format, *.xls for Excel, *.ods for OpenOffice. Give the filename something that we’ll understand: 3cs6ehsnw.xls isn’t too helpful to us. If your project is the Whroo Public Library membership cards, then WhrooLibrary.xls is a good filename to use.

If you can, also send us the file in a tab-delimited text format, too. The way to do this changes from package to package, version to version. If the words “tab-delimited text format” leave you a bit blank, then don’t worry too much, we should be able to open the native format file.

All to do now is to send us the data, usually along with the artwork if you can. That way, we can make sure the data fits into the artwork before we get too far along.

Variable Image Data

Overview

Let’s say we’re doing a run of 5 ID cards. How do we set up the data and the files?

The first thing is to work out what we’re going to use to name the files. It needs to be unique and ideally it needs to mean something. Usually with ID cards we’re lucky in that there’s a unique number for each card. We might also be able to use the name of person that the ID is for. Watch out for large runs though; it’s not uncommon to have multiple IDs for different people of the same name.

Step 1

Let’s start with the data first:

In this case, you’ll notice we have two John Smiths (row 3 & 6). This means that we can’t just use the employee’s name as the filename – one of them will end up with the wrong photo. Looking at the data, we can see that the employeeID field is unique across the data. In this instance, we would recommend using that as the filename for the photo.

Step 2

To save typing, we add a little bit of formula to this table, one that copies the employeeID to filename and adds the file extension as well. In this instance we’re going to use a concatenate to join the file extension to the employeeID field.

To do this, click in cell D2 and type =CONCATENATE(A2,".jpg"). Concatenate takes two values and joins them together into the one string.

When we exit this cell now, you’ll see that it now has the value 51235.jpg. 

Step 3

Drag this down to fill all the cells and you’re data is done. You can also use concatenate to merge the nameFirst and nameLast fields if you were going to use that as your filename instead. In that case the formula would be =CONCATENATE(B2,C2,".jpg") to get JefferySmythe.jpg as the filename field. 

Step 4

The next thing to do is to ensure that your actual filenames match those that are in the data. Ideally this should be done when the images are first created and this is one of those things that are best thought of when specifying the ID card project to begin with.

« Data Specifications