Notes on exporting to CSV format - Mac

MSimon
MSimon
Community Member

Hi there,

The specific reason I export the 'logins' is to compare them to the logins stored by Google Password Manager. Below are my notes and suggestions related to the 1Password export process (File - Export - All items) and working with the resulting CSV file in Microsoft Excel for Mac (I do not use Numbers for data manipulation).

  1. I realize that 1Password exports 'data' while I talk about 'logins'.
    It would be useful to have an option to export only logins as I may not want to import my credit card data and secure notes, etc. to e.g. Google Password Manager.

  2. When setting up the export parameters for CSV, the 'common fields' selection offers the following fields in this order:
    notes, password, title, type, url, username.
    For me this order is useless and commands that I spend time rearranging them to a plausible order like: title, username, URL, etc...
    It is very nice though, that the order can be arranged before export.
    I suggest to change the default order of fields to follow the structure of the 'login' items.

  3. No header row.
    When exporting logins via File - Export - All items, the default resulting CSV file will not have a header row.
    The 'Include Column Labels' has a mixed-up wording for me: 1Password does have labels but does not have columns, it visually has rows. When opening in a spreadsheet app, there are columns, but columns have headers and not labels.
    A different wording would be more transparent, and I suggest to have this selected by default.
    Header rows are particularly useful if one is planning to import them into another app, and the subtitle of the related help note ('You can export your 1Password data if you want to move it to a different app.' (https://support.1password.com/export/) would be honoured.
    I suggest to insert a header row.

  4. The resulting CSV file has a curious structure.
    It seems to group logins and is definitely not in alphabetical order.
    The grouping (if it is) must have a logic I am unable to grasp.
    I suggest an export that is not grouped but is in alphabetical order of login titles, just as in the app itself. Any other order or grouping is distracting.

3a. The CSV file once imported in Microsoft Excel (and Numbers) starts with a cell containing 'üéâ Welcome to 1Password!', with curious characters in the beginning. Why is that? I think it comes from the welcome note provided by 1Password.

3b. If a Note text contains comma (,), and they tend to do, it will be handled by Excel as a separator. This destroys the CSV document structure. So I had to first import it in Numbers, and export from Numbers for Excel to go around this problem with Excel.

  1. When exporting 'all items', the content of Trash is also exported.
    Also, none of the Trash items are marked as 'trash' and they mingle with non-trash elements.
    I suggest that Trash items are not exported or treated separately.

-


1Password Version: 1Password 7
Extension Version: Version 7.8.1 (70801004)
OS Version: 10.15.7
Sync Type: Not Provided

Comments

  • ag_ana
    ag_ana
    1Password Alumni
    edited April 2021

    Hi @msimon87!

    I realize that 1Password exports 'data' while I talk about 'logins'.
    It would be useful to have an option to export only logins as I may not want to import my credit card data and secure notes, etc. to e.g. Google Password Manager.

    If you select the Login category in the sidebar, and select all items there, when you go to export your data from File > Export, you will be asked if you want to export all items in the vault, or just the selected items. This should help you export just the Logins, if those are the only ones that are selected :+1:

    When setting up the export parameters for CSV, the 'common fields' selection offers the following fields in this order:
    notes, password, title, type, url, username. For me this order is useless and commands that I spend time rearranging them to a plausible order like: title, username, URL, etc... It is very nice though, that the order can be arranged before export. I suggest to change the default order of fields to follow the structure of the 'login' items.

    No header row.

    3b. If a Note text contains comma (,), and they tend to do, it will be handled by Excel as a separator. This destroys the CSV document structure. So I had to first import it in Numbers, and export from Numbers for Excel to go around this problem with Excel.

    When exporting 'all items', the content of Trash is also exported.
    Also, none of the Trash items are marked as 'trash' and they mingle with non-trash elements. I suggest that Trash items are not exported or treated separately.

    Thank you for all the suggestions!

    The resulting CSV file has a curious structure.
    It seems to group logins and is definitely not in alphabetical order. The grouping (if it is) must have a logic I am unable to grasp. I suggest an export that is not grouped but is in alphabetical order of login titles, just as in the app itself. Any other order or grouping is distracting.

    I have just tested it here and my export was in alphabetical order (the same order I have in my list view in the 1Password for Mac app). If you select just two items as a test, are those exported in the order you expect?

    3a. The CSV file once imported in Microsoft Excel (and Numbers) starts with a cell containing 'üéâ Welcome to 1Password!', with curious characters in the beginning. Why is that? I think it comes from the welcome note provided by 1Password.

    I have seen this in the past in Excel, when the encoding of the file was different than what Excel was expecting. If you open the CSV file with a text editor, do you see those characters there? If it's not, it means that Excel is adding those. I do not remember exactly what I did when I saw this, as I am not really an Excel expert, but I remember only seeing this in Excel, not in other apps.

  • MrC
    MrC
    Volunteer Moderator
    edited April 2021

    3a. The CSV file once imported in Microsoft Excel (and Numbers) starts with a cell containing 'üéâ Welcome to 1Password!', with curious characters in the beginning. Why is that? I think it comes from the welcome note provided by 1Password.

    I'd imagine those "curious characters" are the BOM characters. 1Password for Mac exports as UTF8, but doesn't include a BOM. It's possible that Welcome note entry includes that preamble. Check in Terminal using hexdump and file. Example;

    Or maybe you opened the file in a text editor and saved it (as UTF-8 w/BOM).

  • MrC
    MrC
    Volunteer Moderator

    It seems to group logins and is definitely not in alphabetical order. The grouping (if it is) must have a logic I am unable to grasp. I suggest an export that is not grouped but is in alphabetical order of login titles, just as in the app itself. Any other order or grouping is distracting.

    Most likely these are exported per-category, and within a category based on the order the items were inserted into the database, or as returned by the record iterator, or sorted by UUID. Sorting is expensive, and just exporting records in the order returned is cheap.

  • ag_ana
    ag_ana
    1Password Alumni

    With regards to sorting, the workaround would be to export the single category: it looks like in that case items are exported alphabetically (probably since they are already sorted like that in the list view).

This discussion has been closed.