Export Logins .csv file: date-stamp fields data format?
When using the Export Logins feature to create a .csv file, the last few fields don't seem to translate into any known date-stamp format in Excel. The data seems to have relative chronological value, but I can't make sense of it in a spreadsheet.
Can a developer check this and modify the output of those fields so it's more compatible?
Example:
Header Cell data ----------------------------
timeCreated 1675178753239 timeLastUsed 1684763722184 timePasswordChanged 1675178753239
Ausgewählte Lösung
This is Unix epoch time and to convert it in Excel you can use =DATEVALUE("1/1/1970")+A1/86400, where A1 contains Unix timestamp in seconds, for instance 1675178753239/1000.
Diese Antwort im Kontext lesen 👍 1Alle Antworten (6)
Ausgewählte Lösung
This is Unix epoch time and to convert it in Excel you can use =DATEVALUE("1/1/1970")+A1/86400, where A1 contains Unix timestamp in seconds, for instance 1675178753239/1000.
Geändert am
Those are date-time values in Unix Epoch format: seconds since 1-1-1970.
You can easily decode the 13-digit microseconds datetime in the Web Console or use a website. javascript:void(prompt('datetime',(new Date(Number(prompt('epoch',Date.now()).substr(0,13)))).toISOString()));
See also:
- /questions/1362419 Window 10 Firefox login table date scientific number conversion to Excel date numbers
So the .csv time fields have valid data, but we have to translate it.
That would result in a useful reference document or an editing tool.
If Firefox activated an actual import logins and passwords function that worked, maybe it would use the unix timestamp data, but we humans also need to be able to understand and work with that login data, perhaps as a backup, or to sort, update, and weed-out entries. Editing the .csv should be made easier, and maybe someday we can import a nice clean copy.
The .csv logins and passwords export feature should include additional localized Excel date-time-stamps. Those are actually decimal numbers in Excel. If a .csv has those, Excel can format them to dates and times you can see and sort. That makes it a snap to analyze and edit. The headers might be labeled like "DateTimeLastUsedExcel" to remind the user to format the cells correctly. Excel stamps would be skipped if imported back to Firefox.
Got the .csv in shape:
In Excel, format all the unix cells to NUMBER with ZERO decimal places, and save the .csv file as .xlsx before working with it. Excel destroys the unix numbers when saving a .csv file unless properly formatted each time before saving, and it doesn't use the quote formatting seen in the Firefox .csv file. Just easier to work with an Excel file.
Sort by one of the unix-time columns (expand selection), select column to right, insert an empty column.
Put this formula into the top empty cell next to a unix number.
=(CELL/86400000)+25569+(-5/24)
Change CELL to point at the unix-time 13 digit cell next to formula cell.
Calculation is unix days+time plus Excel offset, with a time zone offset. -5 is hours behind GMT to get EST. Change as required. EDT (-4) is second Sunday of March until first Sunday of November
The first formula number cell gets treated to: FORMAT CELLS, CUSTOM, m/d/yyyy h:mm AM/PM (paste or update existing choice)
This gives a proper date/time (widen the column if ####).
Grab the lower right corner of the now-formatted formula cell and drag it down the column below. BOOM.
After the first calc it'll be evident what row ranges are Daylight-time dates, so re-calc those (using -4 EDT) if needed. Re-calc sections by tweaking the formula of one cell, and dragging its handle up/down as many rows as required. Check the day of the week of a date by starting to format a cell by date and selecting the example that shows the name, then cancel to resume working.
Once all the proper date and time info is showing in the formula column, right-click its column letter and COPY. Then right-click it again and PASTE SPECIAL > VALUES & NUMBER FORMATTING. Pure Excel date-stamps. Give it a header name. Save as .xlsx file, not csv.
Now pre-sort via the other unix-time columns and repeat the process.
Date-stamps make more sense, provides historical data, keeps track of changes, as well as having a backup to edit/convert and maybe import someday.
See also:
Note that on Windows you would expect support for 64-bit Windows time (100-nanosecond ticks since 1 January 1601 00:00:00 UT) and since Unix time is used quite commonly (i.e. also on Android) also MS might consider support to this format as well.
On what I mentioned previously:
If your Windows version of Excel doesn't put "quotes" in saved CSV files...
This Just In: export-csv does!
Open a Windows PowerShell window:
import-csv "D:\Excel.csv" | export-csv "D:\Fixed.csv" -NoTypeInformation
(keep as one line, and use quotes to preserve path/file names)
More background on what we get by translating the unix timestamps:
I doubt most users would need or bother with such things, but even apart from Firefox support, it might be practical to have a utility to insert localized Excel date-time decimal stamps into the logins.csv file for editing or viewing.
Run through header fields and find the time-prefixed ones, creating Excel-suffix fields after each, then process each line of the file calculating the extra decimal numbers from unix times using the system time zone and daylight time rules. Read a line, spit out a longer line, one or a thousand times.
CSV data from all your machines and backups could be pasted into a text editor, before working on it with Excel. Replace the header line of appended data with a plain text [Label], and put blank lines between blocks of data to make it easier to see and select.
When loaded into Excel, different blocks of data could be color-coded by data source, so when sorted-in, they show what came from where. Maybe the same account was on multiple machines, but updated or used differently. Or there's an old account you need to close that's not on the machine you use now.
Once editing is done and saved in Excel, the Excel columns could be deleted for .csv saving if they would cause any issue with import.
Example of EST/EDT Excel fields in a logins.csv file:
"timeCreated","timeCreatedExcel","timeLastUsed","timeLastUsedExcel" "1635285241009","44495.74585","1637443368864","44520.68251" "1637186057385","44517.70437","1637245334539","44518.39045" "1629412352160","44427.77259","1636670262871","44511.73452" "1626289241110","44391.62548","1634598324444","44487.79542"
In order to work in Excel, the cell formats would need to be changed. The first thing to do is format the unix cells to NUMBER with ZERO decimal places, so they save properly in a .csv file if the edited version will be imported back to Firefox.
Format the Excel cells to date and time stamps with AM/PM, which can even include seconds! Sort on those columns and see what relates to what, and make any edits.
You can merge data from your other machines and old backups, and see what is in need of attention/deletion.
Format the date cells back to GENERAL (or delete the columns) before saving as a .csv file. That file may (someday) import to Firefox on any number of PCs, and also serve as a backup.
To convert a date from an exported Firefox login.csv file, I tested a lot of formulas. The only one that works is this one:
=ROUNDDOWN(J103/(60*60*24*1000)+DATE(1970,1,1),0)
Then select Format > Date.