Need help exporting passwords to CSV for Excel

Boosh

I need to export a dozen or so 1Password entries for a family member. They want it to look tidy and readable. So, I've exported the data to comma delimited text and then import it into Excel.

But no matter how I configure the import, the data is all over the place in Excel. Account names get swapped with logins, URLs get moved to the "Type" column. It's all so random and requires a lot of work to clean up and then cross-reference with the 1Password entries to make sure nothing was missed. I've spent over an hour just to clean up sixteen entries imported into Excel.

It is, frankly, a terrible and infuriating experience.

The export options in 1Password, especially considering the price, are very limited, primitive, flaky, and imprecise. It would be nice if there was an export to html or PDF option.

Is there any way to export data from 1Password so that it looks tidy and organized when imported into Excel or some other text program?

---

1Password Version: Not Provided
Extension Version: Not Provided
OS Version: Not Provided

MrC

@Boosh

What version of 1Password are you (they) using?

How are you doing the export?

Boosh

Latest version:

1Password 7
Version 7.9.3 (70903004)
Mac App Store

As mentioned, I exported to comma delimited text. I also tried tab delimited. Both just result in a mess when imported into Excel. Data is in the wrong columns or even rows.

MrC

@Boosh

Sorry, just wanted to be clear, so I can give solid advice.

It appears that exporting of the Tags field breaks the CSV, so don't include that. Here's a quick export I just did without it, exporting to CSV (not Keychain csv):

I set the field order as you see above. Are you including fields other than the default common ones?

Here's a sample of the broken export when including the Tags field:

Boosh

I don't export tags. I only exported "Common Fields": First One-time Password,Notes,Password,Title,Type,URL,Username. I have no idea why they default to that order when exporting but it results in the categories being out of order in Excel.

In addition, entries get displaced to the wrong category. For example, some usernames got moved to the URL column, some secure notes and titles got moved to the Password column, URLs to Type column, etc. It's a jumble and that's just with 16 items. If I had exported dozens or hundreds of items, it would take an impossibly long time to fix.

MrC

Sorry, another question, @Boosh

Are you selecting just the Login entries, by selecting just those entries and exporting Selected items, or are you export everything and trying to clean that up?

I'm just trying to figure out why it isn't working for you (I'm not using the App store version, but I'm guessing that isn't an issue). Do you see one row where this issue occurs, and can you identify the cell that is causing the first breakage? It's possible there is some data that is triggering a bug in the 1Password CSV exporter, as the Tags field does due to the incorrect/non-standard CSV-quoting being done on its value.

I also don't know why the export fields default to that order; it's not generally something users need to care about (because generally when they export, they are moving to a different password manager). But I'm guessing you want to print out these entries, so a tabular order is important to you.

Boosh

I'm selecting certain items from different categories (logins, secure notes, wireless routers, etc) and then exporting them. A family member wants some of their password information in Excel format. I know it's not secure, but that's what they want.

The default export order for common fields does not match that of login entries. The preferred order should be Title, Username, Password, Website, Notes, etc, to reflect the default order of the fields of a brand new login item (before a person enters data). Why is "First One-Time Password" the first item?

There's something weird going on. If I re-order the fields and delete some (which I didn't realize you could do), I have better results. Still, I can export the exact same data twice but Excel will not consistently import them the same. Sometimes it works, sometimes it's badly broken.

I'm going to do some more testing to see if I can figure out if Excel is choking on certain fields.

Boosh

Alright, it appears as if the export fields are just organized alphabetically. I can sort of see why they did that for all fields, but not for common fields. I still think that the fields should be ordered to reflect the order in which you enter data in a login item. But it seems like something for the 1Password devs to debate.

MrC

Ah, that's the difference, @Boosh ,

Each of the Categories has different "templates". And those templates have different field names internally, as well as field counts. CSV was never designed to have varying numbers of columns. So the 1Password CSV exporter creates a column for every field you ask, and for certain fields, a value can be mapped into multiple columns.

So your best bet is to export one Category at a time.

There is a way to get your data in a nice, tabular format, but I'm thinking it is going to be more work than you are care to undertake. Sample:

or

So I'm trying to help find a way that's easy for you.

That said, you're right. It's rather pointless to use a secure password manager, and then store such data in an unencrypted plain text document.