1Password import confused on importing CSV with line breaks and blank lines

mb10101

When importing a CSV into 1Password for Windows, if the csv contains quoted line breaks and sometimes blank lines (such as in the notes field of an entry), 1Password gets confused and can import the entries incorrectly.

Example:
url,username,password,"notes line 1
(blank line)
line 2
another line
(blank line)
etc",entry name

I would really like to see this behavior handled correctly. Going through several hundred entries and fixing them all by hand is a tremendous effort, and I'm sure I'm not the only person who has run into this issue.

---

1Password Version: 4.5.0.575
Extension Version: Not Provided
OS Version: Windows 8
Sync Type: Dropbox

LauraR

@mb10101 - thanks for this feedback - I shall pass on your request.

ref: OPW-460

djmares

i too have found this problem. both on import and export. apparently the export function doesn't follow true quoted csv guildelines. it would be really nice to see it fixed.

AGAlumB

@djmares: While we hope to improve 1Password's built in import functions in the future, @MrC's conversion tool may prove to be a solution for you right now. I hope this helps! :)

mb10101

@brenty - That'll do. It seems to have handled everything. I kept a backup export of the old DB just in case. Thanks.

MrC

Hi @mb10101,

That's good to hear. I'm wondering what password manager you were using? Since you mentioned only CSV in your post, I assumed you had your own spreadsheet full of data, so didn't suggest the converter. But brenty saw deeper into your post.

Anyway, good to hear you're converted and imported!

mb10101

I was using LastPass as I went adventuring into Windows Phone. The underdeveloped app ecosystem brought me back, but I had to move my data back into 1P as many entries had changed.

MikeT

Hi @mb10101,

Thanks for the details. I don't know if you saw but we're working on 1Password for Windows Modern for Windows Phone.

Hopefully, the universal Windows apps that Microsoft is working on should expand the ecosystem quickly on Windows Phone.

svondutch

@mb10101 I cannot seem to reproduce this problem. Here's what I have tried to import:

URL,Username,Password,Decription,Title
gmail.com,a@a.com,myPassword,"notes line 1

line 2
another line

etc",My Gmail

Am I missing something?

mb10101

@svondutch - Maybe. Possibly. I can't say for certain.

I assumed the issue was what I had mentioned above. I ran the conversion tool, which made the import work again, and didn't give it much thought past that. Until some logins failed.

What I have noticed is that some special characters in passwords -- most notably ampersands, backslashes, and double quotes -- have been altered. Ampersands became & and backslashes and double quotes were backslash-escaped. I also had some passwords with commas, parentheses, angle brackets, and parenthesis in them. Some of my secure notes had all manner of characters in them, including many of the above.

I can only speculate at this point that maybe the special characters were causing issue. Maybe. Can't say for certain.

What I can say for certain is that 5 hours of sleep isn't enough, and that I want a nap. Hopefully this post makes sense, otherwise let me know and I'll try to clarify it after I've gotten some more sleep. :)

MrC

@mb10101,

Are you saying the convert_to_1p4 converter produced issues for you (either the converter itself, of the resulting import by 1Password of the 1PIF file)? I'd like to evaluate this and resolve the issues in the converter, or bring them to @svondutch 's attention (who is always very fast to resolve these issues).

mb10101

@MrC - Yes, but it may have been an indirect consequence.

mb10101

By the way, you guys are all awesome, staying on top of the forum like this :)

MrC

Ok, well, if you don't happen to have a reproducible case or clear set of circumstances, I'll just take mental note and watch for any other reports. Thanks for the replies, and kind words.

mb10101

I threw this together: http://pastebin.com/xjEWPfJP

I was able to reproduce unexpected behaviour when trying to import this.

EDIT: Note that I threw this together by hand. It's not a result of the conversion utility.

MrC

Technically, that "CSV" is broken. Newlines require escapement, and the multi-line notes field is missing that escapement. This:

url,username,password,notes,title
http://four.example.com,userfour,insecure4444,blah,four.com
http://www1.example.com,testy,"abc123!@#$%^&*([{;:'<./?",this site

has really crazy password requirement and causes confusion,www1
http://www2.example.com,tester,"non)}];:/?.>",something

something...

something else not importable,www2
http://three.example.com,userthree,password3333,blah blah blah,three.com

Should be:

url,username,password,notes,title
http://four.example.com,userfour,insecure4444,blah,four.com
http://www1.example.com,testy,"abc123!@#$%^&*([{;:'<./?","this site

has really crazy password requirement and causes confusion",www1
http://www2.example.com,tester,"non)}];:/?.>","something

something...

something else not importable",www2
http://three.example.com,userthree,password3333,blah blah blah,three.com

mb10101

With the quotes, the CSV paste you provided did import. I'm clear on a few things now:

The CSV export I had was improper in that line breaks were not properly escaped. That was part of my issue in why it wouldn't import properly. After running it through the converter, it seemed to fix that issue. Allowing everything to import. The extra escaped characters in my passwords from the converter (see above) were some light collateral damage, but nothing I couldn't work around. I would take importing the entire database with a few escaped password characters over not being able to import it correctly at all. And that post should say that "ampersands became & amp;" (without the space)

Thanks for helping me learn through this. I don't know why I didn't realize that the CSV wasn't proper to begin with. I appreciate the help. Also thanks for your converter. It saved me many headaches. You learn something every day, whether you want to or not :)

I'll stop bothering you with my broken CSVs now :)

Appreciation: @LauraR, @MikeT, @MrC, @brenty, @svondutch, and everyone else (because we all deserve some): Thanks for keeping on the forums and offering genuine help, instead of long response times and canned responses. Few things in life are more frustrating, especially when dealing with passwords. Also thanks for the conversion utility which allowed the import to be successful. Thanks for a product based on licenses and not a subscription model. A huge thanks for supporting TOTP in 1Password (one of my favorite features), and for putting effort into Windows Modern.

Fun fact: I've been going through my roughly five-ish year old password database, and hitting up sites and changing passwords, and I've found that with about 10% of sites, either a) the site has undergone changes making my login credentials no longer valid (and I never found out), or b) the site is now gone. Out of about 408 logins, only 370 are still valid, and I've checked almost every single one of them. (Seriously, I have)

Thanks again.

MrC

From my end, thanks for your thorough engagement and great replies.

The converter uses a very powerful CSV conversion module, which can handle the "loose quotes" (so it is called), so it can compensate for bad CSV. That's why it works, when 1Password's native CSV might not. The CSV module alone is over 3000 lines of code!

With your info, I know where to focus attention. I'll see if I can create a test case to cause the improper conversion and/or import into 1Password. The issue has to do with either the converter not properly escaping certain reserved (to JSON) metacharacters, or 1Password not handling them. I'll report back here if I find anything.

mb10101

Thank you @MrC.

svondutch

I've been going through my roughly five-ish year old password database, and hitting up sites and changing passwords, and I've found that with about 10% of sites, either a) the site has undergone changes making my login credentials no longer valid (and I never found out), or b) the site is now gone. Out of about 408 logins, only 370 are still valid

This is why I believe auto-save is better than import.

MrC

Wait! mb10101 had a 91% good site rate. With only 38 no longer useful logins, I'd say import was a very good strategy.

But I think there's another important factor here. Conversion + Import means you get to leave an old password manager behind in minutes, rather than over days or weeks of painstakingly testing each of those logins. Rapid conversion means users can take a lazy approach towards site validation, and do so in their own time.

Convert + Import == More Time for Beer

mb10101

More time for beer?! I wasted it checking all those logins (and not drinking beer while I was doing it)... :(

MrC

Well, I'm not sure exactly what data and steps you used that caused the & vs. & issue with the converter and the generated 1PIF. I can't reproduce it, using the convert_to_1p4 lastpass converter, so if you are able to, and are so inclined, I'd be grateful for any pointer. But don't feel obligated.

mb10101

@MrC - I went back and looked at my original CSV export, prior to running it through the converter script. All of the above character mangling issues are present in the CSV before running it through your converter. My apologies for the assumption.

However, the multi-line notes are quoted too. So I'm more curious than anything at this point as to why this didn't import.

I'm going to try to go through this file to locate some of the problematic entries and see what the issue might have been.

MrC

You are a good person, and are owed one tall one.

mb10101

So, I did some digging, and snipped out a section that will reproduce the problem: http://pastebin.com/Qp5DrwvJ

It seems that LastPass mangled one of my passwords that had a bunch of special characters in it (that's it, unredacted -- it's been changed since, naturally). Everything after that section becomes problematic. You'll also notice the inconsistent quoting. Go ahead and import for yourself.

I'm sure this meets the criteria for "edge case", and I don't think there's much that can be done. But, nonetheless, there it is.

AGAlumB

@mb10101: Thanks so much for sharing that! I'll admit that I'm a bit out of my depth with this particular subject, but I've been following this fantastic thread with a great deal of interest. I think I've learned a lot. :)

It's definitely problematic that the password was 'mangled' to begin with (although I think I can see why that happened). I'm glad that you and MrC found each other, as it seems that the collaboration has been productive — and also, beneficial for me. Cheers! :lol:

MrC

@mb10101,

Thanks for the data. I'd need to see the raw data before it was exported so that I can recreate the entries to see what causes LastPass to botch the export. If there is anyway you can create screenshots of those two or three entries that created this data, that would be great. Then I can create the records and take it from there. Perhaps the converter can compensate (as it does for the botched CSV in older versions of mSecure).

mb10101

The original password is here: http://pastebin.com/YcCveS5r

If you specifically want me to go back and recreate the entry in LastPass, I suppose I can do it. Let me know.

MrC

I had no problem adding that password to an entry in LastPass, exporting the entries, and converting them. Your CSV shows the incorrectly CSV-ified:

"""\""xY0'$9aNn\\M73k\""Z%Ol6\"""""

and mine shows the correctly CSV-ified:

"xY0'$9aNn\M73k""Z%Ol6"

I'm using:

LastPass Firefox Toolbar
Version: 3.1.95
Built: 2015-04-29 15:25:10
Fast Encryption: true (ctypes)

Which version are you using?

mb10101

I was using the LastPass Chrome extension Version: 3.2.11