SplashID import - I'm getting close, need more help

trashman

My SplashID example: 320 records, 16 different templates.



So far I have:

Exported from SplashID:

selected all the records that share a template.

export these as a CSV file.

each record has 12 fields,

each record is terminated with CRLF (0x0D, 0x0A).

file looks good with 2 exceptions:

(a) notes field can contain New Line characters (0x0B)

(<img src='http://forum.agile.ws/public/style_emoticons/<#EMO_DIR#>/cool.gif' class='bbc_emoticon' alt='B)' /> fields can contain commas (but are enclosed in quotes when they do, and only when they do)



Import as CSV format.



Problem 1:

How can I get 1Passport to recognize the quotes and ignore the encapsulated commas (i.e., not terminate the field)?



Problem 2:

Will Passport1 handle the New Line character OK?

(I haven't got that far yet, but just thought I'd ask in case anyone knows.)

khad

Welcome to the forums, trashman.



Have you tried the [url="http://forum.agile.ws/index.php?/topic/3142-splash-id-vid-importer/"]SplashID VID Importer[/url]?



If you are still having trouble, do you have a sanitized — usernames/passwords removed — CSV file we could take a look at?

trashman

Thanks for the reply.



No I don't think I'm going to try the script. Sounds kind of buggy and I have too many records to worry about whether I lost data or not. I don't think I'm going to follow your other suggestion either, because sending you a CSV file without sensitive data would be as big a task as what I'm dealing with now.



Since you didn't really answer my question, I'll assume there is no way to have 1Password recognize the quote as a way of encapsulating text with commas in it. (Too bad, this is a rather standard way of handling this.)



I seem to be having better luck using the tab delimited, but I'm still not quite there. The good news is that the notes field does handle the Vertical Tab character OK, 0x09 (aka, New Line character).



Here's what I'm struggling with now..... How would you suggest I handle the fact that SplashID exports 13 fields and 1Password can only import 9?



(Based on my experience so far, I think listing SplashID as one of the formats 1Password is able to import is bad advertising. So far it's cost me 2 days. What about the poor sucker who doesn't check thoroughly and finds he's lost information after it's too late? Not good!)

khad

I'm sorry that you are still having trouble, trashman.



Offhand I'm not sure that the SplashID importer should have any limitations different than our "regular" CSV importer, but you might try importing the data as "CSV or Delimited Text." This will give you finer control of some of the settings.



Please set the delimiter character to "Comma" (or Tab if appropriate) and check the box to indicate the the "Fields are quoted." (This ensures that quoted commas and new line characters do not terminate fields.) Make sure you map each column. Columns which are not mapped will not be imported.



One of the toughest aspects of importing data is that half of the process (the export) is beyond our control. If you have a sample CSV file, we would love to improve the process. It is working well in my testing, but due to the nature of SplashID (with custom fields which can have virtually unlimited possibilities), it can be hard to test every scenario.



I hope that helps. Please let me know.



Thanks!

Ricardo Corai

@trashman I have a similar problem. Did you find a solution?

[Deleted User]

Hi Ricardo,



[quote name='Ricardo Corai' timestamp='1303347788' post='25271']

Did you find a solution?[/quote]

Have you tried Doug's SplashID vID importer script, mentioned (with link) earlier in this topic? I posted about my experience using it: [url=http://forum.agile.ws/index.php?/topic/4377-import-into-software/page__view__findpost__p__25048]here[/url]

brenty

Thanks for the bump, sjk. It's easy for these good links to get lost in the shuffle.



Ricardo and trashman: please let us know how it turns out. <img src='http://forum.agile.ws/public/style_emoticons/<#EMO_DIR#>/smile.gif' class='bbc_emoticon' alt=':)' />





[quote name='sjk' timestamp='1303351752' post='25278']

Hi Ricardo,





Have you tried Doug's SplashID vID importer script, mentioned (with link) earlier in this topic? I posted about my experience using it: [url=http://forum.agile.ws/index.php?/topic/4377-import-into-software/page__view__findpost__p__25048]here[/url]

[/quote]

trashman

Through trial and error, I did manage to get all the data transferred. It was a painful process. So painful in fact, that I should set the record straight: 1Password cannot import from SplashID. Plain and simple. There is no magic for mapping 13 fields to 9 and no way to handle quoted fields (unless all are quoted, which is not what SplashID exports). A little provider integrity would have saved me a lot of aggravation. But this is how I spent my 2 days recovering from the deception.



Since I had a fair number of commas in note fields, it was easier to find the tabs and get rid of them. So, I exported in Tab Separated Variable format (TSV, or CSV with Tab as delimiter) then opened in Excel and found where I had more than 13 columns. Any extra tabs show up as a row with more than 13 columns which are easy to spot because the last field is the category, which is never empty. I deleted the Tabs in SplashID and exported the TSV file again. It took me a few iterations of this to get rid of all the Tabs.



Then to get the SplashID 13 fields down to the 1Password limit of 9, I used Excel's Concatenate function. By the way, the Vertical Tab character (VT = 0x0B) that SplashID uses for a new line in the notes field was handy here. I found one, which shows up in Excel as a nonprinting character, and pasted to its own cell. I could then use an absolute reference to this cell in the Concatenate function to place New Lines between the old 5 fields, into the new Note field. 1Password handles the 0x0B ok. Now I had a 14th column that contained the data from the other 5 columns, all separated with a New Line character (well a VT which works the same). Then, Select All, Copy, and Paste Special -- pasting Only Values back into their original places. This got rid of the concatenate formula and replaced it with the resulting data for export.



With the final TSV file open in Excel, I sorted the rows based on type (I didn't use categories, but I could have sorted on this too). Now another hard part... I had to go through my SplashID record Types and make a map of the fields for each Type, in order to line up the URL, username and password for the import to 1Password. I ended up with about 7 groupings where I could use the same mapping of Excel columns with 1Password import fields. These groupings (of a few Types each) were copied to their own Excel Worksheet and saved as a TSV file. Then I imported these 7 files into 1Password using the field mappings for my Types. I used the Type field itself for the folder name.



Now at least I have all my data in 1Password. Unfortunately they are all formatted as Logins. And of course, 1Password doesn't have a way to convert one type to another. So, for all my credit cards, bank accounts, etc., etc., etc.... I still have to copy and paste all the record information into a new record....one....field....at....a....time.



So my request now is for 1Password to give us a type converter function. I have seen others ask for this. All it has to do is map fields, one to the other. In fact, it only has to map Login types to other types since this is the only import option. This is a really simple function and it would alleviate a few of the import headaches that many of us are having. For extra credit they could even let us save templates for the mappings. oooohhh, nice. Actually, it would have been easier for me if I could import as something other than Login type. After all, I had all the data separated already.

khad

Thanks for the feedback, trashman. I am glad that things did end up working out for you (as well as they can currently). I will add your vote for custom templates (which would include the ability to change vaults) to the list. Imports are never the most fun part of switching products but we hope you find 1Password worth it the more you use it. Thankfully it is not a process you need to repeat...!



We will continue to improve the process thanks to feedback like you have provided.