What is the date format when exporting database to .csv file?
Hi there,
I would like to (and did) export my database to a .csv text file.
How can I turn the "date created" and "date modified" columns into human readable format?
Thanks and kind regards,
Hans
1Password Version: 6.8.1
Extension Version: Not Provided
OS Version: 10.11
Sync Type: Not Provided
Comments
-
Hi @wonshu ,
These are "Epoch" times, seconds from 1970. There is no trivial formula to convert these time stamps, because they rely on leap year, locale, and time change information.
Let me suggest you use the onepif converter with the csv formatter in the converter suite of utilities. Use the 1.10 version in Testing Bits, mentioned in the first post.
With it, you can export your 1PIF file into CSV, which will contain the converted time stamps.
Example 1PIF item (which you export from 1Password):
{"uuid":"4A27DCAF7FB34FE89B09775A96FA95FB","updatedAt":1400347201,"securityLevel":"SL5","openContents":{"tags":["STOCK"]},"contentsHash":"d1a206bc","title":"Bank Account - stock","secureContents":{"accountNo":"2222222","branchPhone":"555-1212","bankName":"my bank","owner":"joe bank user","routingNo":"1111111","swift":"9998174","iban":"66666","sections":[{"fields":[{"k":"string","n":"bankName","v":"my bank","t":"bank name"},{"k":"string","n":"owner","v":"joe bank user","t":"name on account"},{"k":"menu","n":"accountType","v":"savings","t":"type"},{"k":"string","n":"routingNo","v":"1111111","t":"routing number"},{"k":"string","n":"accountNo","v":"2222222","t":"account number"},{"k":"string","n":"swift","v":"9998174","t":"SWIFT"},{"k":"string","n":"iban","v":"66666","t":"IBAN"},{"k":"concealed","v":"1234","n":"telephonePin","a":{"generate":"off"},"t":"PIN"}],"title":"","name":""},{"fields":[{"k":"phone","n":"branchPhone","v":"555-1212","t":"phone"},{"k":"string","n":"branchAddress","v":"123 main st. redmond, wa","t":"address"}],"title":"Branch Information","name":"branchInfo"}],"accountType":"savings","notesPlain":"stuff","telephonePin":"1234","branchAddress":"123 main st. redmond, wa"},"createdAt":1400347088,"typeName":"wallet.financial.BankAccountUS"}
Example converted into CSV:
Category,Title,"bank name","name on account",type,"routing number","account number",SWIFT,IBAN,PIN,"Branch Information::phone","Branch Information::address",Tags,"Last Updated","Date Created",Notes
"Bank Account","Bank Account - stock","my bank","joe bank user",savings,1111111,2222222,9998174,66666,1234,555-1212,"123 main st. redmond, wa",STOCK,"2014-05-17 10:20:01","2014-05-17 10:18:08",stuff
See the README.pdf for details on using the onepif converter, but the conversion command you run will look like something like:
perl convert_to_1p4.pl onepif 1pif_export.1pif --format csv
and it will place the CSV on your Desktop with the name 1P_converted.csv.
0 -
Thank you.
Is there no way that a spreadsheet program can simply interpret these numbers correctly?
Otherwise I may have to go down that route...
Best
Hans0 -
The conversion is platform-specific, and I don't know which spreadsheet program you are using. Here's the rough idea, and a Google search will provide others (search: epoch conversion spreadsheet):
See if this helps you any.
0