Import Software Serial Numbers into 1Password from FileMaker Pro or text file

I love 1Password! It is a really great Password manager, and in a recent version they added support for storing all your software serial numbers & registrations. I’ve been using a simple FileMaker Pro database for that, so I want to import all of it into 1Password.

The problem begins when you notice that 1Password can import from a text file, but it can only import a text file into Secure Notes or Logins. Aw really? :-(  Hey, these are software registrations, and they belong in that section. It is easy to import your software registrations into 1Password as “Secure Notes” but that seems wrong, especially with a special “Software” section right next to it.

As it turns out there is a trick, and it sounds more difficult than it really is. I’m going to show you how to import your software serial numbers into 1Password so they go into the right section all at once, from any software that can export to a text file. Then I’ll show you some tricks you can use to get your data smoothly from FileMaker Pro to a text file to import into 1Password.

Begin;
Export your software registrations from the other software as a text file; either CSV (Comma Separated Values) or Tab-delimited. If your data might include commas, then export as tab-delimited, then find/replace the commas with something else, then find/replace the tabs to be commas. I used the awesome & free TextWrangler for Find/Replacing, it was great. I replaced commas with double dots (..) .

Ok, so Export your data, or export & rearrange the columns in Excel so that you have them in this order:
title,version,serial number,registered name,registered email,registered company,publisher,website,support email,purchase date,currency,price,order number,notes

It MUST be comma delimited to import as we need to .
Do NOT allow a space after the commas because that will add a space to the beginning of the next field. If you don’t have all of those fields, it is ok to just have the right number of commas, even if there is nothing between them.

You MUST use Windows line endings. So open the file with TextWrangler and pull the Edit menu to Document Options. Choose Windows (CRLF), & OK.

For FileMaker Pro Users:

If your original data has different fields than needed, or less fields, you can make a new calculated field to concatenate several fields into 1 fmp field for export. In my case I had only 6 Filemaker fields (Title, Version, Registration Number, Number of Users, Date, Notes), but 2 of them (Notes & Number of Users) needed to be combined into the 1Password Notes field. So something like this will work in Filemaker Pro;

Make new calculated field “LicenseKeeperExport” of type “text”. Here’s the formula =

Title &”,”&
Version &”,”&
Registration Number &”,”&
“,”&
“,”&
“,”&
“,”&
“,”&
“,”&
DateToText(Date) &”,”&
“,”&
“,”&
“,”&
Notes & If(Number of Users >0, ” | Number of users = ” & Number of Users,””)

Then from FileMaker, export just the 1 field (I called it “LicenseKeeperExport”) that includes all of the above in this order, with commas. It won’t matter if this export is tab-delimited or CSV, because we are only using 1 field per record, so there won’t be any more delimiters added. Only what we defined in that calculated field, and a CR ending each record.

OK! So we’ve exported a text file! Hooray. But we are not done.There is some important formatting or it will not work out. You’ll need to use Windows CRLF line endings as (as explained above).

If your exported data is surrounded by Quotes (“) you want to remove those. Find/Replace the quotes with nothing, starting from top.

Note: depending on your data, you might also need to remove control characters from your data. Also easy. Pull TextWrangler’s Text menu -> Zap Gremlins. You probably only need to zap the control characters, replacing with a bullet.

OK! So that’s the difficult part. Save & close the file. We are finished with FileMaker & TextWrangler.

Let’s Launch 1Password and pull the File Menu to Import.
Choose the file type of LicenseKeeper. Surprise! You might expect to choose CSV or Text file, but that will NOT work – because those don’t allow import into the Software section. Remember? That is why we are doing all this! :-) Apparently LicenseKeeper also uses a text format, but with those differences that we did find/replaces for.

Select your CSV file, and select the Character encoding. Mac Roman worked the best for me, but ascii was mostly pretty good too.

If you get to this next screen where it shows a list of your data in nice columns you’re doing well & it will probably work. :-) Keep going!

Give a quick Shout! Hooray! It worked & we are FINISHED! :-)