Import Joins Together (Merges) Many Records From CSV/TSV

DecEMDT

I have tried several different approaches to importing passwords from an Excel sheet. From 652 records/rows in the CSV I get 360 imported. Not a neat average two-for-one, but dotted around the finished Secure Notes list are five 1Password notes that have joined together (concatenated) 5-25 incoming records.

- The problem applies with both a CSV and tab-separated incoming file.

- If I chop the import file down to a smaller number of records it still joins some together (eg 225 in becomes c100).

I cannot see any pattern in the offending incoming records to trigger (or stop) the unwelcome concatenation. 1Password just seems to miss the CR/LF at end of line for 5-25 records, then suddenly rediscover it.



TIA.

Martin

khad

Hey Martin,



Sorry you're having some trouble importing.



It sounds like the file may not be formatted correctly or there is an invalid character borking the parsing. It is pretty safe to say that whatever is happening, it is not random at all. We'd love to get to the bottom of it.



If you can send us a sanitized sample — sensitive information removed — we'd love to take a look and see if we can help you either better format the input or improve 1Password's importer if we need to. You can post it here or email us directly if you prefer: support AT agilebits.com. Again, we really don't want your sensitive information. Please make sure you replace it with "xxxxxxxxx" before sending us the sample regardless of where you send it. <img src='http://forum.agilebits.com/public/style_emoticons/<#EMO_DIR#>/smile.png' class='bbc_emoticon' alt=':)' />



Cheers,

DecEMDT

Sorry I could not find my original post and therefore this reply. Just reposted - and found this reply! Will do with anon sample.

DecEMDT

Fixed it. Thanks Khad - your steer motivated me to try again and think what in the data or settings was 'borking the parsing'. I did four things of which I think the fourth made the biggest difference, but the first three data conditioning steps must have helped overall data quality.

[b]1.[/b] I identified (by sorting an extract) a few cells that had an Enter at the end, to create two lines in the CSV. These were usually from pasting in passwords. I removed the Enter at the end to give a smooth one row on Excel matches one record in the CSV.

[b]2.[/b] Instead of save as in Excel I copied the range and pasted unformatted text into Word. This gave better mass processing and save options - see below. It also showed the tab separator.

[b]3.[/b] I used Replace in Word to change ' ^p' (space paragraph) for '^p' (just paragraph ie end-of-line marker). This meant no lines had a trailing space, just real characters then Enter.

[b]4.[/b] When 1Pw opens text files it first guesses UTF-8 encoding. I followed the golden rule with computers of giving it what it wants. In Word Save As... Plain Text the subsequent prompt for text encoding options includes Other - then you can select 'Unicode 6.0 UTF-8'. This gave 1Pw the coding it presumably understands best.

Once I opened the import file, choose Secure Note and dropped down the field mapping selectors, it imported all 672 records including the headers and my 'trace record' to track mapping. The count on the folder matched my control total from Excel and all inspected records looked good. I could delete the header & trace record.

Some extra tips from several tries at import.

- Don't forget the menu option to Backup Data File just before you import. And as soon as you have something worth keeping.

- If you do multiple test import runs, make sure the 'folder' name you give is distinct. Rename unsuccessful folders if necessary. Then if you have a final good one and two earlier unsuccessful runs, you can select the folder from a bad run, then Select All for the entries in the list and delete all the duplicate/bad entries in one go.

Good luck everybody. It's a great tool and worth putting the effort in so you can switch across to it. Invest in mastery - for instance, check out Smart Folders from saved queries with multiple criteria!

Martin

khad

Awesome! Glad you were able to get it working once the data was formatted properly. <img src='http://forum.agilebits.com/public/style_emoticons/<#EMO_DIR#>/biggrin.png' class='bbc_emoticon' alt=':D' />



We really appreciate your kind words. If we can ever be of further assistance, please let us know. We are always here to help.



Cheers,