lastpass import csv number format goes exponential


Just a heads up to any LastPass importer that you might want to be careful manipulating the CSV file generated during export from LP.

I've ended up with a bunch of username entries that are a string of numbers expressed as an exponent value. I suppose this isn't really exclusive to LP but rather any import process that requires using a CSV file.

I think what happened is that I opened up my export CSV file in Excel, and filtered it to separate out some categories, then saved that data into separate CSV files.

All good, but I know see that I have a number of username entries like 1.23456E+11 instead of a contiguous 16 digit number - such as one might find if a username entry is actually an account number. I don't know why other than I guess Excel has some default method of handling numbers and chose to convert them. I cannot be bothered to dive back into what happened since I need to spend time fixing this bad entries.

Needless to say - a pain. I'm also not thrilled that I need to use a CSV file for import since there is no way to secure that file. Anyway...

1Password Version: Not Provided
Extension Version: Not Provided
OS Version: Not Provided
Browser:_ Not Provided
Referrer: forum-search:lastpass import csv number format


  • Tertius3
    Community Member
    edited January 2023

    If you postprocess a CSV file with Excel, you get all the automated processing of Excel, which includes autoformat for numeric values. For Excel, a field with a numeric value contains a numeric value. It doesn't know that it's actually a userid string that must not be formatted according to numeric formatting rules. That's standard Excel behavior.

    You have better control over how Excel treats csv fields by not double-clicking a csv file in Explorer and let Excel open it, but instead start Excel, then go to the "Data" Category and choose "From Text" in the external data section. This starts the csv importer wizard, and in the last wizard screen you can define the data types for each column. For the userid field that might contain numeric values but should actually been treated as text, choose "Text" instead of the default "Standard" column type. This will do no (or less) undesired autoformat of numeric values in that column.

    You might also try the converter from MrC and not edit the export with Excel: I didn't try it with Lastpass, but it might create a better conversion of the csv export than the default importer - MrC tries painstakingly to reproduce the original data to fit the various 1Password item types.

  • deadlydog
    Community Member

    I came here to report the same issue. Tertius3's answer is spot on. I had exported my data from LastPass, but for some reason LastPass had duplicated all of the entries in the csv, so I opened it in Excel, removed duplicates, and saved it. That must've been what converted the large numbers to the scientific exponent format. Unfortunately since the numbers were converted before importing into 1Password, you can't automatically get the original number back. You need to find your original number and manually update it in 1Password. Thanks!

This discussion has been closed.