Skip to content
Thoughtful, detailed coverage of everything Apple for 33 years
and the TidBITS Content Network for Apple professionals
Show excerpts

#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.

Adam Engst 5 comments

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.

App Tracking dialog

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.)

App Tracking settings and dialogs

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.

Adam Engst 36 comments

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:

  1. 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.
  2. Press Command-C to copy them.
  3. 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.

  1. In Contacts, select the desired people.
  2. Choose File > Export > Export vCard and save the file somewhere convenient.
  3. Navigate to the vCard to LDIF/CSV Converter website, click Choose File, and select your file.
  4. Choose CSV from the Format menu, and click Convert. The converted file downloads automatically.
    VCF to CSV converter

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:

  1. In Excel, create a new file.
  2. Choose File > Import.
  3. In the Import dialog, select CSV file and click Import.
    Excel Import dialog
  4. Select your file in the Open dialog that appears next.
  5. In Step 1 of the Text Import Wizard, make sure Delimited is selected and click Next.
    Excel Text Import Wizard Step 1
  6. 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.
    Excel Text Import Wizard Step 2
  7. 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.
    Excel Text Import Wizard Step 3
  8. Click the Text radio button and notice how the column headers change from General to Text. Click Finish.
  9. In the final Import Data dialog, select the destination for the data and click Import.
    Excel Import dialog

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.

Google Sheets import dialog

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 pull Adam from my contact card’s first name field and & Tonya Engst from its Spouse field to create Adam & 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 be Adam 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!

Watchlist

Agenda 17 Agen Schmitz 2 comments

Agenda 17

Momenta has issued version 17 of Agenda, adding multi-window capabilities to the date-focused note-taking app and a new purchasing model for its Premium features. The release now enables Premium subscribers to open notes, projects, or overviews in a new window in both the macOS and iPadOS editions; create an instant focus mode by taking a note into fullscreen mode; and keep a window floating above all other windows for easy note taking during conference calls. (Momenta has more details about the new features.)

Agenda 17 also adds a Save as File option in the Share menu, improves the behavior of pasting as plain text, adds the option to click the app’s Dock icon again to bring the main window forward, improves the look and feel of sidebar and gear menus, displays the auto-completion menu above the text if there is not enough space below, removes the request to leave a positive review in the App Store while typing in a note, resolves an issue where the auto-completion menu would gain a white background after showing a submenu, and fixes a bug where the search field would become unresponsive when typing into the table of contents popover.

Momenta has also simplified its Premium subscription model, now offering a single auto-renewing annual subscription that unlocks Agenda on all your Apple devices and a new Lifetime Premium one-time purchase option. (Free with a $34.99 annual Premium subscription or $119.99 one-time Premium purchase, free update for subscribers, 71.7 MB, release notes, macOS 10.14+)

Carbon Copy Cloner 6.1.5 Agen Schmitz 4 comments

Carbon Copy Cloner 6.1.5

Bombich Software has released Carbon Copy Cloner 6.1.5 (CCC) with improvements and bug fixes for the drive cloning and backup utility. The update now presents more context in cases where a task fails due to a stall at the source or destination, provides more specific advice when errors occur due to a disagreement between the source and (typically NAS) destination, lists network change events in the Activity tab of the CCC Dashboard, adjusts the behavior of the Task Filter window when working with a Remote Macintosh source, and fixes a layout issue in the task plan when hiding and then revealing the sidebar (particularly in macOS 13 Ventura). ($49.99 new, free update, 22.3 MB, release notes, macOS 10.15+)

Cardhop 2.2.6 Agen Schmitz No comments

Cardhop 2.2.6

Flexibits has released Cardhop 2.2.6 with a handful of improvements and bug fixes for the contact management app. The update improves adding multiple fields to a contact with an option to add all fields from the template to the contact view, improves the menu structure of the Add Fields menu, switches YouTube links from the legacy username URLs to the new YouTube Handle URL, adds keyboard shortcuts for switching between tabs (Command-1, -2, -3, and -4), resolves a bug that caused Cardhop to become unresponsive for some users, and fixes direct actions when a group is selected. ($56.99 annual subscription from Flexibits and the Mac App Store, free update, 31.4 MB, release notes, macOS 11+)

Camo Studio 2.0.1 Agen Schmitz No comments

Camo Studio 2.0.1

Reincubate has released Camo Studio 2.0, a major upgrade for the virtual-camera system that now supports any webcam, connected pro camera, Continuity Camera device, or action cam. Previously focused on using an iOS device camera as the video source for many Mac video streaming apps, Camo Studio 2 now supports other devices, such as monitors with built-in cameras, DSLR and mirrorless cameras, inputs from capture cards and HDMI dongles, software virtual cameras, and more.

Camo Studio 2 introduces a configurable Portrait bokeh depth effect for getting just the right depth, Spotlight mode for brightening you against your backdrop, a Privacy blur effect with a diffused background, and a Replace option for swapping in a background image. The update also adds native LUT support (including a pack of 18 presets), enables Camo Studio to be used for Center Stage controls, and makes it easier to control Camo Studio with Shortcuts. Version 2.0.1 came out shortly afterward to improve the quality of Portrait, Privacy, and Replace modes; fix a bug that led to the video unpausing when changing resolution; and resolve several issues with Camo’s Nvidia add-on.

Camo 2 is a free upgrade for all existing users, including those on a monthly or annual subscription and those who purchased a lifetime license. ($39.99 annual subscription or $79.99 lifetime license, free update, 42.1 MB, release notes, macOS 10.13+)

Tinderbox 9.5.2 Agen Schmitz No comments

Tinderbox 9.5.2

Eastgate Systems has published Tinderbox 9.5.2, a maintenance update with a lengthy list of improvements and bug fixes for the note-taking assistant (release notes available in Tinderbox Help). The update enhances code fields to record changes automatically (negating the need to press Return); updates flags to permit several new expressions; converts dragged files with .md, .mmd, and .markdown extensions to styled text; addresses a problem in parsing comments that included quotation marks, apostrophes, semicolons, or braces; resolves a hang that could occur when accessing the $Path of a note in different queues; corrects the layout of the Attribute Browser’s column picker; and better recognizes what is and is not a word for word counts. ($249 new with a 25% discount for TidBITS members, free update, 35.7 MB, macOS 10.13+)

Lunar 6.0 Agen Schmitz No comments

Lunar 6.0

Alin Panaitiu has released version 6.0 of Lunar with several new features for the display brightness control utility. The upgraded app can now read the real brightness value in nits from Apple displays and sync it more accurately to other monitors (only with M-series Macs), allows Sync Mode to be used on all setups (even where there’s no Apple display to sync from), improves the speed and efficiency of the adaptive algorithm for a responsive auto-learning curve, ensures that DIY ambient light sensors connect faster to Lunar, updates the API used by the Blackout feature on M-series Macs running macOS 13 Ventura, adds a keyboard shortcut for connecting a Sidecar device, and improves support for brightness/volume control using DDC for the integrated HDMI port of the M1 and M2 chips. ($23 new, free update, 20.7 MB, release notes, macOS 11+)