Jump to content
Enpass Discussion Forum

CSV Import [solved] - Splash ID, Excel, other - nothing to import error


Recommended Posts

The following steps and information will allow you to import a CSV file. Although I am coming from SplashID, the information below is useful to anyone. 

Tools

  • Excel
  • TextWrangler (now BBEdit) - free. Or probably any feature-rich text editor. 
    • "TextEdit" default app is NOT sufficient.
  • Thumb drive 
    • Investigate how you will secure-delete your unencrypted SplashID export file before you export. SSD is questionable, as are flash drives. However, Flash drives are more easily reformatted in whole, or flat out sacrificed and destroyed. Google "wear levelling".
    • The csv file is dangerous. It's unencrypted, small and fast to copy. AND, it's the most important data you have.

 

Overview

You will export the SplashID CSV file to a sacrifice-able thumb drive. You will import a .csv file of your own structure using Enpass desktop. The SplashID .csv export will be cleaned up in TextWrangler, imported into Excel for sorting, and finally opened in TextWrangler again for final processing. To import to Enpass, you will choose the "Pre-formatted CSV" option, then choose "Other Miscellaneous" from the drop down list before choosing and uploading your file. 

These instructions are a careful sequence of Find-Replace for your SplashID CSV export, resulting in a properly formatted file.

You will still have to work your data inside Enpass, such as dragging items into Enpass categories or adding tags through multi-select. This is not very sophisticated. It DOES get your data into Enpass, and in the tidiest possible way. Probably it's time to tidy your data anyhow.

 

Result

Your final file will have one record per line, no spaces/returns between lines, and the top line will be the field names with quotes around them. The ends of the lines have no punctuation character. Your file will have had all commas that do not relate to fields removed. In case you didn't know it, CSV stands for Comma Separated Values.

Attention: dates (January 5, 2007) and notes (Dear Jim, I am ...) will create import problems or unexpected results due to commas. 

 

Difficulty

There are many small but easy steps. Frankly, Enpass, I don't know why this can't be a wizard.

 

Steps

0.

Export the SplashID CSV file to an empty flash drive that you don't mind destroying later. 

Make duplicate of the CSV file. Don't work on your original. I don't take any responsibility for your data, nor will I help further than this post. 

Let's assume your original export file name was SplashID-export.csv

 

1.

Open the file in TextWrangler

Immediately: File > Save as...

Choose a new name like MyPasswords.csv. You may need to add .csv at the end. If it says .txt you can simply replace it with .csv. 

Choose Unix and UTF-8 encoding. Save.

1356023026_ScreenShot2019-05-23at19_03_02.png.8b4789f92274e0fbc07267036794cbd7.png

 

In TextWrangler, you want to work with unwrapped lines...

View > Text Display > Soft Wrap Text

Even if something below is checked but grayed out, Soft Wrap Text is NOT CHECKED

Tip: In TextWrangler, when you click a word all same words are highlighted with an underscore.

3.

We will be using semi-colons for markers. Therefore, replace any existing ones in the CSV file with something else NOW... before the "marker semi-colons" are added. Later, Excel will import commas, but export semi-colons. If you don't clean out extraneous semi-colons now, it'll be a big pain later.

Find  ;

Replace  -

Replace it with whatever you like that is NOT a semi-colon or a comma. Here I use a dash.

  • The semi-colon will tell Excel (eventually, on import) "This is the end of a record. Start a new line."
  • The comma will tell Excel (eventually, on import) "This is the end of a field."

 

4.

Now build the formatting framework for the further work. First, we want to remove all tabs and return characters. You can see that in TextWrangler as a little hook and perhaps a delta-triangle when you...

View >Text Display > Show Invisibles

 

4-a.

Get rid of all the line breaks.

Select everything. Text > Remove Line Breaks

 

4-b.

Unfortunately, some return characters remain. They are little hooks.

Highlight one this way: click to the left of the character and drag your mouse directly down one line to the start of the line below. Only the return character and it's line will be selected. cmd-c (copy).

Open Find. cmd-f (find). In the find box, cmd-v (paste) in the return character. The find box will look empty

In the replace box, type 4 spaces. Again, it will look empty. 

Now replace ALL. 

CORRECT RESULT: ALL DATA IS ON ONE LINE.

Note: Forget about all those extra spaces. They automatically get sanitized out in the process below. If they don't, you can later replace 4 spaces with something else in the same way. Naturally, 4 spaces is more searchable than 2 spaces.

 

5.

The exported SplashID CSV has a handy structure for the following step. The record TYPE comes at the start of the record and the CATEGORY ends the record. We are going to mark these using Find-Replace, then use the markers to put line breaks at the end of records - where we want them. Remember, Enpass only accepts "one record - one line". 

The semi-colon markers make it easier to look at your records in Step 8. Also, they tell Excel when to start a new row. Any extra semi-colons will transfer into Excel as blank lines. No need to worry. They automatically get sanitized out. If you didn't do Step 3, you will regret it after this point.

 

So, going from your SplashID TYPE dropdown list...

Find   FirstType,

Replace    ;FirstType,

Put the semi-colon before the type name. Include the comma in both operations.

Repeat for all Types, including "Unfiled" or "Other".
 

Note: If you’re clever at cross referencing, you can compare the category counts with the search results for, for instance, “Personal” TYPE. If Splash ID counts 10 files under "Personal", but your search result window finds 12 instances of "Personal", then review it to make sure you aren’t setting a semi-colon in an unnecessary place. Specifically, if the TYPE “Personal” doesn’t appear at the beginning, where TYPE is found, or at the end, where CATEGORY is found, then you do should NOT add a semi-colon.

 

6.

The CATEGORY should be done like this: 

Find  ,FirstCategory

Replace  ,FirstCategory;

Put the semi-colon after the category name. Include the comma in both operations.

Repeat for all Categories, including "Unfiled" or "Other".

 

7.

Now reintroduce the line breaks...

Open the find dialog box with cmd-f (find). 

Activate the checkbox for GREP.

in the find box, type    [\;]   ...bracket  BACK-slash  semi-colon  bracket

in the replace box, type   &\n  ...ampersand  BACK-slash  n

1580139607_ScreenShot2019-05-24at02_34_52.png.141fea84fca9f0208f81f4371394a7eb.png

The codes say, "take all the semicolons and put a new line." The search terms will light up in colors, signifying GREP search.

GREP is a sophisticated and precise code-based Find-Replace technique. More, in case you have really crazy data structures.

Now replace ALL.

 

8.

Review your data. Does each and every record-line start with a Type? If not, you can safely fix it by hand. Use the delete key to join up the rogue line with the line above it, to which it belongs. "One record - one line." Don't worry about lines that only hold a semi-colon.

Save.

Because you saved the MyPasswords.csv file as a .csv and changed encoding to Unix/UTF-8 at the very beginning, you are all good to go. You can probably see the current encoding in the info bar along the bottom.

 

9.

OPEN EXCEL

Open a new document to see the option to Import

Import > CSV file > choose MyPasswords.csv > Deliminated File > checkbox "Semi-colon" and "Comma" > Next > Select All Columns > Choose the Text Format> Finish > New Sheet 

Long numbers (like credit cards) will be shortened to scientific notation if they are imported under the "General" Format. The numbers will NOT be normal on export if you leave them alone. To prevent ... In the last screen of importing you can choose General, Text, Date, .... USE THE TEXT FORMAT AT THE MOMENT OF IMPORT by first selecting the column (Or all columns - this spreadsheet has no calculation purpose) and then selecting the Text Format. Note: the difference between selected and unselected columns is very subtle. You will get a tiny white line around the black area.) To be sure, perform this formatting every time you are doing an Excel import!

542495837_ScreenShot2019-05-26at13_11_42.png.febd65f9c51bb3a1a504c40607630e0f.png

1343753019_ScreenShot2019-05-26at13_10_19.png.2eb5f3723ce8f1d677ed98adab63c363.png

847479558_ScreenShot2019-05-26at13_10_43.png.2ab48fc9a6f29b9585a37e5b2302a2ff.png

1878149653_ScreenShot2019-05-26at13_11_06.png.e655d40a088afd5bd45ab33dbe030a86.png

2145548513_ScreenShot2019-05-26at12_57_43.png.52f8e4ee50c7ee05d8614360f4409941.png

 

9-a.

Select All.

Sort Ascending or descending.

Is it blank?!?! Don't Panic. You can delete the many blank rows created by our semi-colon Find-Replace routine. Or Select All and change your sort direction.

 

10.

Add an empty row at the top of the sheet.

Add the following column headers, being sure to include, at a minimum,  the bold ones - spelled EXACTLY as here.

TagsTitleUsernameEmailPasswordWebsite, yourChoice1, yourChoice2, yourChoice3, yourChoice4, Note, yourChoice5, etc6, etc7

 

Naturally, put the titles on the columns to correspond with what is indeed there. Mind the spelling and make sure you have all the bold items, even if you need to insert a new column.

I suggest to put the Tags title on the TYPE column.
 

Note on Tags: The Tags column should hold only one value at this pointIf you try to complicate things with tag separators in the tag field, forget the rest of these instructions. These instructions are a careful sequence of Find-Replace. I didn't have multi-tags, so I don't have a process for multi-tags. I titled my TYPE column as "Tags". After import you'll have the chance to select several records at a time and add tags.

Note on Creation Date: I sorta tried, but I do not think Enpass can retain the dates exported by SplashID. I deleted them. Probably, I could have re-entered all the dates in a format that uses dashes like 01-01-2001. Oddly, using the Format > Cells... > Date did not work. If you do not get rid of these date commas somehow, you are going to have trouble! Imho, just delete the date column.

IMPORTANT: use "Note", singular, and not "Notes", plural, so the field appear in a yellow box in Enpass

 

11.

The order of the columns doesn't matter to Enpass... not exactly. Since it plucks out "Note" for itself, for instance, it seems to affect the import of the columns that follow the Note column - depending on whether a record contains a Note or not. 

I don't know and I didn't trouble myself with understanding, but... if I move the Note and Tags column to the end I got the expected import results.

Therefore ... move the Tags and Note column to the end.

Reminder! Always select the column with a click ...as a whole... to keep your data orderly.

11-a. 

OPTIONAL: ADD A CHECK FEATURE

Follow this if you want to give yourself an extra check that your import will have an expected result. Add a column at the very end. Title the column 2DELETE.. Use the handle to copy the word down the entire column. As a spot check, if you've imported properly, when you examine your imported items in Enpass, the 2DELETE field will also contain the value 2DELETE. Otherwise, with so much text file gibberish, you could overlook a data alignment problem.  

Of course, you should go back later and delete this column and re-step from Step 13.  

395051604_ScreenShot2019-05-23at19_49_34.png.687a81083b47ab7cf05260c69c071943.png

I also deleted the Category column. Once you're in Enpass, you'll want to drag each item into the native category folders that comprise Enpass anyway.

 

12.

TIDY UP

I used the split pane function to always show the title row while I tidied up some files. No time like the present to tidy the data. It may help you to see your data better if you use the "Wrap Text" cell format in Excel...

Select All > Format > Cells > Alignment > Wrap Text

IMPORTANT REMINDER: If you see long numbers (like credit cardswritten in scientific notation. The numbers will NOT be normal on export. They will have been shortened and therefore changed. This is a matter of using the "General" format on that particular column in the import settings. In the last screen of importing you can choose General, Text, Date, .... General will allow the shortening of long numbers, and in the cycle of import/export of this method, the original numbers will be lost. USE THE TEXT FORMAT AT THE MOMENT OF IMPORT by first selecting the column (Or all columns - this spreadsheet has no calculation purpose) and then selecting the Text Format. Note: the difference between selected and unselected columns is very subtle. You will get a tiny white line around the black area.)

 

13.

Excel: File > Save as... > 

Rename it to indicate that you sorted the file and changed the data, i.e. MyPasswords-Sorted.csv. Make sure it's still CSV format. Save.

If prompted: save the active sheet, and click continue on the warning about features. 

 

14.

In TextWrangler, open the file which you identified as being sorted in step 13. 

 

15-a.

Notice that Excel exports .csv files separated by semi-colons rather than commas. Probably it's a setting I've got. Skip this if your exported file is separated by commas.

While it's still separated by semi-colons, probe for any remaining extraneous commas:

Find  ,

Replace  -

Find a comma and replace with a dash. Or replace with a number of spaces or any punctuation that is NOT a semi-colon or comma.

 

15-b.

Replace the Excel created semi-colon separator with a comma, as required by Enpass.

Find  ;

Replace  ,

Find the semi-colons and replace them with commas.

 

16.

ONLY on the Title row, put quotes around the field titles. Do not add punctuation to the end of this, or any other, row.

CONGRATULATIONS!

Your data is now sanitized and whipped into shape. Save with a new name to identify this has been sorted and processed in TextWrangler. i.e. MyPasswords-Sorted-Processed.csv This helps to identify the correct file to import into Enpass.

Save as... > MyPasswords-Sorted-Processed.csv

  • Check that you've set Unix and UTF-8 encoding. 
  • Check the file ending is .csv. If it comes up as .txt, simply delete .txt and type .csv in its place.

 

17. 

OPEN ENPASS

Once you've set the masterpassword, do a first test import into Enpass. 

File > Import > Pre-formatted CVS > dropdown "other Miscelaneous" > choose your file MyPasswords-Sorted-Processed.csv. Continue.

IF your file is one record per line, IF there are no extra/blank lines, IF the file contains commas only between fields and not within fields, IF there are no semi-colons, IF there are indeed quotations on the field titles, IF there is no punctuation at the end of each line... your data will be recognized. 

Import!

 

18.

Oh no! Is the screen empty?? That's because it was imported into the Miscellaneous category. 

Click Miscellaneous.

Examine your import. 

  • Does the 2DELETE field contain the value 2DELETE? Look at several records. If yes, your data is aligned. 
  • In the sidebar, do your tags look like tags or do they have odd snippets of text? You've probably got a comma somewhere within your data.
  • Choose a complex record. Compare it to the Enpass record. Is the record indeed complete?

Alignment errors point to an extraneous comma somewhere.

 

19.

PROBLEMS

Need any fixes? Having an import problem "nothing to import"? Want to delete a column? You probably have a comma error, or a title formatting error.

 

So... EITHER

Go back to Excel, using MyPasswords-Sorted-Processed.csv, and follow Step 9.

Rename it MyPasswords-Sorted-Processed-Excel-rev1.csv or whatever you like to indicate its freshness.

Tidy your file and keep going down the steps.

OR

Go directly back to TextWrangler, using MyPasswords-Sorted-Processed.csv,, and follow Step 14.

Rename it MyPasswords-Sorted-Processed-TW-rev1.csv or whatever you like.

Tidy your file and keep going down the steps.

 

20. 

In Enpass, you can now either import more sheets (aka Categories) 

OR

If you have fine-tuned your data, you will want to replace, not add it twice. If you worked on your records againreplace your data. You don't want duplicates. Erase what you already imported:

Gear Symbol > Advanced > Erase Everything 

Follow from Step 17.

 

21.

Organize your records inside Enpass. Sort them into the categories. Highlight several records and add tags with a right-click.

 

22.

All good? Great. Set up a sync so you can get your data to your smartphone.

First set up sync from desktop to cloud. From your phone setup sync from phone to cloud. Viola!

 

23.

According to your appropriate method, secure-delete the dangerous, unencrypted SplashID export file and all the iterations you created above. All your passwords are exposed. If you go to all this trouble to use/change password keepers, do not be lazy about handling or properly disposing of these files. 

 

24.

Kiss your Mom. We love that.

 

Screen Shot 2019-05-26 at 12.57.43.png

Edited by GOBO
I renumbered the steps, so I need to renumber references to steps. Furthermore, I misstated the import of long numbers like credit cards. If you import under the General Format, they will be shortened!
  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...