#1651: Dealing with leading zeroes in spreadsheet data, removing ad tracking from ckbk
After having a holiday card returned as undeliverable due to an inadvertently truncated ZIP code, Adam Engst investigates the knotty problem of how spreadsheets handle data with leading zeroes. He also shares the story of how a conversation with a TidBITS-reading developer led to the company removing ad tracking from its iOS app. Notable Mac app releases this week include Carbon Copy Cloner 6.1.5, Cardhop 2.2.6, Agenda 17, Camo Studio 2.0.1, Tinderbox 9.5.2, and Lunar 6.0.
Helping ckbk Remove Ad Tracking
In this anonymous age of scale and target audience and eyeballs, it still pays to get personal. Last month, I received email from Matthew Cockerill, who introduced himself in a manner similar to many TidBITS readers writing to me for the first time.
As someone who started following TidBITS 30 years ago as a Mac-using PhD student in the early 90s, it was very cool to see one of your latest pieces beginning: “I love cookbooks” 🙂
Matt was referring to “Use Live Text to Digitize Your Cookbooks” (5 January 2023), and he wanted to tell me about his service ckbk, which provides the full text of roughly 700 cookbooks to subscribers. I looped Jeff Carlson, who wrote “Use the Web to Cook Your Books” (17 March 2022) for us, into the conversation, and if all goes well, we’ll have another article on the topic in a month or so. (A quick search of my email also revealed that Matt and I were both on a publishing world mailing list, where I learned that, after getting his PhD in molecular biology, he co-founded open-access science publisher BioMed Central and has gone on to consult on design and innovation.)
But that’s not what I wanted to write about today. After Matt reached out, I downloaded the ckbk app to my iPhone and was dismayed to discover that this compelling-sounding service from someone I’d probably enjoy a great deal in person seemingly wanted to track me.
Whenever I see that dialog, I immediately become suspicious of the company and its product, and I always tap Ask App Not to Track while simultaneously muttering obscenities under my breath. I then take a screenshot of the app so I can easily document which companies have dubious business models and troubling ethics with a search for the image text “Ask App Not to Track” in Photos. (The full list is in Settings > Privacy & Security >Tracking.)
I replied to Matt’s message with questions about the service and noted my disappointment in the app asking to track me. He replied:
When it comes to the fact that the app asks to “track”—I hear where you are coming from but it’s not as sinister as it sounds… Unless “tracking” is turned on, the app and app store can’t even offer the most basic information—there’s no way to know which of our promotional spending or partnerships have brought users to the ckbk app, and which have been a complete waste of money, for example. So in order to have some basic measure of the effectiveness of our marketing it seems we have no choice but to ask to track. It’s frustrating that there’s no distinction made by Apple between this and wider sorts of 3rd party tracking.
That somewhat mollified me. Not enough to allow full-fledged tracking, but I understand the need to match click-throughs from ads or partnerships to actions. We did that all the time with Take Control Books to learn where a customer had come from, though nothing else about them. But Matt’s next email made me feel even better:
Actually, thanks for the nudge on this—I’ve been doing some more digging and it turns out this is a rapidly moving space, and there are some new privacy-preserving ways that we can now track downloads which don’t necessarily require us to ask for tracking or for users to opt in. So we are going to get onto the case and switch things around so that we no longer need to prompt new users with the offputting “tracking” request…
Two weeks later, it was done:
Just wanted to let you know, we’ve taken your feedback on board, and have been able to eliminate “tracking” from the ckbk app. (Actually we already weren’t tracking, but we needed to update the metadata and remove certain libraries which cause tracking to be flagged.)
It turns out this was a *lot* more difficult than expected, as there seems to be a significant App Store bug which means that there is a Catch-22 and Apple will reject an app which no longer tracks because of ‘non matching metadata’ and there’s no way to make it match until your new app is live. Creating that Catch-22. The only way past it being an appeal to the App Store. But we managed it…
There’s the testament to making personal connections. Matt’s entirely appropriate introduction means we’re likely to write about ckbk, and my politely worded disappointment about tracking resulted in ckbk refactoring the app to remove the dialog, which will create a better experience for new ckbk users, possibly including TidBITS readers. Everyone wins.
I also learned that Apple’s App Tracking Transparency might be a bit more of a blunt instrument than I had previously thought. As you saw in the screenshot above, I still use a fair number of apps that have asked to track me, and perhaps they aren’t as evil as I had believed. But I’m still not going to give them permission. And don’t get me started about the likes of Facebook and Instagram, which I won’t let anywhere near my iPhone.
Dealing with Leading Zeroes in Spreadsheet Data
Call us old-fashioned, but Tonya and I still enjoy sending physical holiday cards to summarize the past year for family and friends. We’re not always on target with completing the task in December, and there have been several years when we delayed enough that our New Year’s roundup appeared in the guise of Valentine’s Day cards sent in mid-February. Amusingly, getting our cards out late has often been rewarded with more conversations with friends because Valentine’s Day cards stand out from those that arrive around Christmas or New Year’s.
Why am I writing about holiday cards now, in mid-March? Because two months after we sent out our batch, one of them just bounced back. Although we were surprised it took so long, we were more perturbed that we had gotten the address wrong. It took a few minutes of staring at the card before Tonya realized the problem. And then a light dawned.
Most Northeast states have ZIP codes that start with zeroes: Connecticut, Maine, Massachusetts, New Hampshire, New Jersey, New York, Rhode Island, and Vermont, not to mention Puerto Rico and the special delivery military/overseas APO/FPO European destinations. After some marital disagreement about our label-making process this year, Tonya took over the job and chose to switch from my approach—connecting Contacts to Belight Software’s Swift Publisher—to the Avery Design & Print Online tool. To move addresses from Contacts into Avery’s Web app, Tonya needed them in Excel (XLS or XLSX) or CSV (text-only comma-separated values) formats. Unfortunately, Contacts can’t export to CSV, just VCF (also known as vCard) and its own ABBU format (which it calls a Contacts archive).
Tonya transferred the data out of Contacts by selecting all the cards in her Holiday Cards group and dragging the selection into an empty Numbers sheet. She couldn’t figure out how to transform the data the way she wanted there, so she copied it into Microsoft Word. After several years of supporting Word at Microsoft in the early 1990s, it’s her go-to tool for just about everything. I can’t criticize; my default hammer is BBEdit and grep.
The problem likely arose due to her moving it from Word into Excel so she could export a file that Avery would accept. By default, Excel formats columns of pasted or imported data as General, a numeric format. Leading zeroes don’t make sense in numeric formats—007 is the same as 7, Mr. Bond—and Excel blithely discards them, turning a five-digit ZIP code into an incorrect one with only four digits. (ZIP codes can even be three digits: sorry, Holtsville, NY 00501!) Changing the ZIP code column format back to Text after the fact has no effect because Excel parses the text as a number and converts the stored value into a number. Apple’s Numbers treats most data—other than that copied or dragged from Contacts—the same way. (Thanks to Alan Forkosh for alerting us after initial publication that Excel actually has a ZIP Code format buried in Format > Cells > Number > Special. Changing to that format would have solved the problem.)
Since we knew we had entered the ZIP codes correctly in Contacts and haven’t memorized our friends’ ZIP codes, we missed the four-digit ZIPs while proofing the labels—who looks carefully at a ZIP code? Worse, we didn’t even figure it out for the first two cards that bounced back—sorry, Paul and Andy! (Interestingly, not all of our four-digit ZIP cards have bounced yet, which could suggest additional delays or that some post offices or mail scanners worked around our mistake. Printed and most handwritten addresses are managed entirely automatically.)
So, if you’re ever putting US addresses—or other data with leading zeroes, like UPC or SKU numbers—into a spreadsheet, it’s worth making sure that no leading zeroes have been dropped. I did some experimentation and found two approaches that preserve a ZIP code’s leading zeroes when moving the data out of Contacts. You’ll also find tips for dealing with other types of data.
Contacts > Numbers > CSV
When you stay within the Apple ecosystem, everything just works as long as you know that dragging or copying and pasting data from Contacts into Numbers brings over everything, complete with leading zeroes in ZIP codes. So:
- Select the desired people in Contacts. This is most easily done if they’re in a group, at which point Command-A is your friend.
- Press Command-C to copy them.
- Switch to Numbers, create a new blank spreadsheet, and paste with Command-V.
In other words, it’s just a couple of clicks and keystrokes. Then you can remove unnecessary columns, munge the data, and export to CSV.
Things become more complex if you want to bring data with leading zeroes into Numbers from some other source. In that case, the solution seems to be to enclose the data in double quotes and prefix it with an equal sign. Thus, if you had a ZIP code of 07470, you’d need to change it in the source data to ="07470"
before opening it in Numbers. (Previous advice to enclose numbers in single quotes no longer seems to work.)
I leave the exercise of editing an entire column of such ZIP codes in a CSV file to the reader, but it’s a breeze in a grep-capable editor like BBEdit or Nisus Writer Pro: search for ,([0-9\-]{5,10}),
and replace with ,="\1",
.
Contacts > VCF > CSV > Excel > CSV
Any Mac user should be able to copy from Contacts and paste into Numbers—it’s free to download from the Mac App Store. For the purposes of argument, however, let’s say that you need to go from Contacts to Excel without involving Numbers. Happily, converting an exported VCF file into CSV is easy.
- In Contacts, select the desired people.
- Choose File > Export > Export vCard and save the file somewhere convenient.
- Navigate to the vCard to LDIF/CSV Converter website, click Choose File, and select your file.
- Choose CSV from the Format menu, and click Convert. The converted file downloads automatically.
Once you have a CSV file of addresses or some other data to import, follow these steps to open it in Excel while retaining leading zeroes:
- In Excel, create a new file.
- Choose File > Import.
- In the Import dialog, select CSV file and click Import.
- Select your file in the Open dialog that appears next.
- In Step 1 of the Text Import Wizard, make sure Delimited is selected and click Next.
- In Step 2 of the wizard, deselect Tab and select Comma. Notice how your data lines up neatly in columns once you do. Click Next.
- Step 3 of the wizard is the key. While the first column remains selected (black), swipe (with two fingers) or scroll horizontally to the rightmost column (with a scroll wheel, hold down the Shift key to scroll horizontally), and then Shift-click that column to select all the columns in between, turning them all black.
- Click the Text radio button and notice how the column headers change from General to Text. Click Finish.
- In the final Import Data dialog, select the destination for the data and click Import.
If you plan to work with this file in the future, save it in XLSX format rather than CSV. CSV is a text format and doesn’t retain formatting, which means you would lose the leading zeroes if you imported it into Excel again without using the Text Import Wizard.
Note that the trick of enclosing data with leading zeroes in double quotes and prefixing it with an equal sign also works in Excel.
It’s embarrassing that, after 35 years for Excel and 16 for Numbers, neither handles imported leading zeroes without significant extra work. How hard would it be to notice that a file contains leading zeroes and ask the user whether or not to retain them? Even Google Sheets provides a simple checkbox for this in its Import dialog—deselect it to import ZIP codes perfectly.
Of course, none of this would have been necessary if Contacts had halfway decent label-printing capabilities. We only want two Miss Manners-approved label-formatting options to ensure that our cards are appropriately addressed to couples:
- For couples with the same last name, the first line of the label would be
First & Spouse
. For instance, that would pullAdam
from my contact card’s first name field and& Tonya Engst
from its Spouse field to createAdam & Tonya Engst
. - For couples with different last names, the first line would use
First Last & Spouse
instead. If Tonya were still using her maiden name, the result would beAdam Engst & Tonya Byard
.
In both cases, we ensure the Spouse field contains the spouse’s full name.
Swift Publisher can do this, but its connection with Contacts can be tenuous. I’m unaware of any other apps that offer such formatting options with data from Contacts, but if you know of any, please let us know in the comments!