A Bit

I been recently experimenting in using a different tool besides bitwarden for my password management needs. Nothing wrong with bitwarden, but it is always a good idea to look at other products to get a feel of what could be improved or what you can do to find ways around certain features.

I been using 1password for a while at work and with the 1Password Linux having some nice integrations, thought why not try it out as an every day driver. Not to mention that a business plan gives a free family plan for every account on its plan. So I started to venture forth. And of course, immediately I hit a snag. Bitwarden isn't supported as a 1password direct conversion. Although 1password do offer a way for you to use csv import, you still need to format the csv correctly.

Bitwarden graciously offers the ability to export as a csv and json (and encrypted json if that's your thing). The exported raw csv doesn't exactly format as is to 1password. Looked around online and found some tools that could do the job, but they kind of suck in setting it up how I would like. So of course I want to make my own. Thing is, I wanted to do this as quick as I could.

The tool would have to take the exported csv file and truncate to only have login type rows and only columns related to such. One of the endless interesting features sqlite has is the ability to import and export a csv. You also get a tool to mutate a relational data file with a file based relation database. Thus, sqlite was my preferred choice to get the conversion out of the way.

So first's thing's first: figure out what is the converted format to use on 1password. When you export from bitwarden as an csv, the export column outputs as follows:

folder,favorite,type,name,notes,fields,login_uri,login_username,login_password,login_totp

I only care for the conversion to get a few columns set up:

name,notes,url,username,password,one-time password

Knowing the format, go right ahead and make a database on sqlite:

sqlite3 convert.db

Next, set the import mode to csv and import the exported csv to a raw table

sqlite> .mode csv
sqlite> .import raw.csv raw

Due to not giving the schema here nor needing to, all the columns will be imported as TEXT type. The imported type works to the need here. So let's make a new table called 'output' with the columns desired. The table created will be the same as raw but with the selected columns only and their names. To do so, simply run a select for said columns:

CREATE TABLE output AS SELECT type, name, notes, login_uri as url, login_username as username, login_password as password, login_totp as 'one-time password' from raw;

Next, remember the ask here was only to have login types from 1password for this conversion. Running a delete for everything but the login type:

delete from output where type != 'login';

Then drop the column for 'type' as it's not needed:

ALTER TABLE output DROP COLUMN type; 

Now the output table should be ready to exporting to import in 1password. Let's go ahead and set sqlite headers on and set output file:

.headers on
.output converted.csv

Lastly, get that output table exported. Since we know we want all the fields let's go ahead and export with a select all:

select * from output;

You're done with the file. It should now work in importing on 1password. Now before you go and say, “hey fool, you could do this much simpler”. You are right, I could. So here's the lines for everything above using relation database correctly:

.mode csv
.import raw.csv raw
.headers on
.output converted.csv

SELECT 
  name, notes, login_uri as url, login_username as username, login_password as password, login_totp as 'one-time password' 
FROM raw
WHERE type = 'login';

The output is exactly what is desired here and it also gives a baseline of what I want my csv output to be. You can go ahead and import the csv to 1password now. Just make sure to remove the first row and correlate the column names with those that would be for 1password. The one last piece is that one time password isn't available as a type you can choose. So you need to make a custom field. Then you can apply by editing the field after import. I know, annoying.