CSV import challenges with v5.0.2 for Mac

PaulB_SD

I'm using 1Password for Mac Version 5.0.2, from the Mac App Store.

After reviewing some of earlier discussion posts from MrC and AgileBits moderators I've been able to convert an Excel file to UTF-8 CSV by opening it in Numbers and then exporting from there. The text format is: Login name,url,username,password

Ensuring that the file was saved as UTF-8 has at least made the file able to import, but not properly. Now when I import the file it populates the entire string from each line in the Login Name field, and seems to capture something in the password field but nothing else is populated. Have you seen this problem before? I had hoped that this would have been fixed by now given that many of the posts on the topic were started over a year ago.

Thanks!

MrC

Hi @PaulB_SD‌ ,

Thanks for created the new thread here. I've deleted the earlier post in the convert_to_1p4 thread.

The Login CSV import columns are:

title, URL, username, password, notes, custom field 1, custom field 2, … ,custom field N

I don't see a Notes column in your export, and I think that is one of the required 5 fields for the Login category. The CustomX fields are optional, and if they exist will be imported.

Try adding a Notes column. If you have no Notes for any of your records, then create a single column header row at row 1, and provide data labels for the first five fields, or create a single dummy record that contains values for all 5 fields. This should cause the CSV exporter from whichever program you use to output an extra comma (and this will indicate a NIL value for the field).

PaulB_SD

Tried that and still getting the same result. I inserted a notes column at the end of a few records with the value: "note" This additional field is just added to the string that is imported into the Login Name. I recall one of the earlier posts discussing handling of CR vs. LF - could that be what's causing the problem here? It doesn't seem so since 1Password seems to be able to delineate between records, just not between fields.
Also tried adding spaces back in after each comma and that doesn't seem to have any impact.

MrC

I don't know how well 1Password handles some CSV aspects, but know that it is finicky, and doesn't provide you with any usable error diagnostics. So you have to guess at what is going wrong.

Above you mention you added a Notes cell to a few records. It is important that the CSV data contains the same number of fields per row - this means that same number of commas in each row (which CSV-quoted commas being excluded from the tally). So its worth validating your data that way, and ensure that the output rows that contain notes, and those that don't, still have the same number of comma-separated fields.

Excel by itself exports CSV data, and that should be consumable in 1Password. I no longer have Excel so can't verify the required CSV export options to produce a 1Password-acceptable CSV. I mentioned the OS X Numbers program in other posts to help particular users. (LibreOffice and OpenOffice can also export CSV with very specific export options.)

I believe either CR line endings should be sufficient (in the data file, these show as hex 0a, or decimal 10). This would be the format produced by a Mac program such as Numbers.

If you are still having trouble, tomorrow I'll create a generic CSV to 1PIF converter for you. This will be a drop-in module for the suite of converters available in my convert_to_1p4 utility. It will require that you have a header row, and that header row will specify the data contained in the column. This will have the added benefit of producing specific error messages for invalid CSV, handle some CSV errors or violations, allow arbitrary column orders, and support all of 1Password's categories as import types.

PaulB_SD

First of all, thanks so much for your time on this. I have verified that each row contains the same number of fields/commas and I'm still getting the same result after a few more attempts. When I open and view the file in TextEdit the file rows show as: ", , , , note" With the quotations, and where everything in brackets has been replaced by the field or column heading. Also, I'm not using any column headings in the file at all - should I be? I don't see a hex or decimal character at the end of each line.

Appreciate any more help you can provide.

Thanks again!

MrC

Hi @PaulB_SD,

If your rows are starting with a single quote, and ending with a single qoute, and these are the only quotes, then this is the problem. The double quotes are quoting the commas, so the CSV entry only contains a single column (field). Which process made this data? I suspect it happened either in Excel or upon Export, or on import into Numbers.

PaulB_SD

That would explain the behavior I'm seeing! I'm not sure where in the process the data had been formatted to this - my list started in Excel, then I opened & saved it in Numbers, then exported to CSV from there. Oh I know! I think I may have messed this up by combining all of these columns into one, THEN attempting to export as CSV. I'll try to re-export from Numbers (with the original column layout) and see if that translates correctly.

Thanks again!

PaulB_SD

That did it! A short cleanup and I'm good to go. Thanks very much!

MrC

:D