CSV Import not working

hbouk

I have created a csv file from scratch following the format in the page https://support.1password.com/create-csv-files/ and can't seem to import the logins from my csv file.

I have checked several times to make sure I have adhered to the correct format and have made sure any of my notes with commas are surrounded by quotes. I should mention that I was trying to import the csv file into a Teams vault but wondered if that wasn't supported so I created a new local vault and get the same result. I select the import option from the file menu, then choose the Login item type, select my vault, then select the csv file. I don't see any thing after I choose my csv file. I hear a system sound but don't see any dialog box showing progress or errors.

I am running 6.3.1 Beta 4 on my Mac (version 10.11.5).

Suggestions would be greatly appreciated, hoping to avoid creating these passwords manually.

MrC

Hi @hbouk ,

Here are a couple of things to check:

1. the file is exported as UTF-8.
2. there are the same number of columns per row (generally, this means the same number of commas).
3. you've made sure your manual quoting is correctly quoting embedded double quotes and commas, in all fields.

Don't worry, you won't have to import your passwords manually. If you cannot get the internal CSV importer to work, you can use the csv importer in the converter suite. If you use it, use the 1.09 version in Testing Bits. Just follow the directions in the first post and the README.pdf.

khad

MrC is our resident import guru, so there is much I can defer to him. But I want to make sure you know we are here if you are still having trouble, hbouk.

Let us know how it goes.

hbouk

Finally got the import to work. I tried the converter suite to convert my CSV file to hopefully one that could be imported into 1Password. When I ran the suite I got an error "CSV column names do not match expected names". Seemed strange to me since my CSV didn't have any column names so that got me thinking.

I exported a few of my existing passwords from 1Password using the File, Export and then copied my password records int this exported CSV from my CSV file. Then deleted the exported password records from the file so that it contained only my records that I wanted to import. Still no luck.

Looking back at the recommendations above I focused on making sure that my CSV file was saved in UTF-8 format. I opened my Excel generated CSV in TextWrangler where I noticed that Excel had converted my quotes around my notes into triple quotes. Eg. In Excel looking at one particular password record looked like title,URL,username,password,"This is a sample note" but in TextWrangler it appeared as title,URL,username,password,"""This is a sample note""". So in TextWrangler I did a search and replace to change all """ to ".

Now I was able to import the file into 1Password except all of the data was imported into the wrong fields. I quickly realized that this was because the exported CSV put the fields into the wrong order for the import to work correctly. An export puts the fields in the following order Notes,Password,Title,Type,URL,Username but the import wants the fields in this order: title,URL,username,password, notes.

I think the knowledge base article should be enhanced to mention the issue with Excel changing some syntax on CSV files or at least create a tip to double check in a text editor. Also, the default export order for the fields should be the same as your expected import order for the fields. The user experience on this is really, really bad.

I am a huge fan of the product but this is a definite area of weakness that shouldn't be too difficult to make better.

MrC

@hbouk,

The csv converter in the converter suite requires the column names:

● CSV
This is a generic CSV converter which currently handles the categories of Logins and Credit Cards. Only one category per CSV file is currently supported.

Construct the CSV in a spreadsheet program. The first row must be a header row that contains the category-specific field names, and possibly other columns, as mentioned below. The letter case of the field names does not matter, nor does the order of the columns.

For Logins, the required column fields labels are: Title, Username, Password, URL, Notes.

By adding them, you're telling it the meaning of each column. It differs than the 1P csv importer in this way, so that you don't have to reorder the columns, and so that you can add custom labels to additional columns and have them imported as custom fields.

It appears Excel thought one of your columns was part of the data from another column, saw the quotes, and quoted the quotes so as to preserve them as textual, non-escaping quotes. This can happen if you have some unquoted cell data, and Excel was confused about how many columns are in the CSV. Complicated. Creating a CSV file by hand is almost too complicated to explain to folks, and even when the precise rules are explained, most folks are not used to the precise detail and precision required to make the computer happy. And then there's TL;DR - people just don't want to read more than a twitter-sized clump of text.

hbouk

For me, I learned a valuable lesson when trying to use Excel to create CSV files. I'll use Excel to quickly assemble the data into the needed format but then check the CSV file in a standard text editor to make sure the format is what I expect.

Thanks for the help to get this problem resolved. Love the product and the support provided. Cheers.

Drew_AG

I'm so glad to see MrC was able to help you out with this! Hopefully you're all set now, but if you have more questions or need anything else, just let us know. :)