Problem Exporting CSV and importing into spreadsheet programs
Hi,
I spent over an hour trying to make sense of the export function (got better at it) but couldn't really make it work properly while exporting one or more records from a one category to CSV format and then importing on a spreadsheet software like Open Office Spreadsheet, Excel or Google Sheets.
Here's what I am doing at the moment:
- select the records I want to export, from one category
- clicked on Export / Selected Items... (input password)
- Selected CSV format
- Ticked "Include Column Labels"
- Left "Common Fields" selected
- Reordered the fields in the fields box, the ones in a blue rectangle with round edges
- Hit save
- Imported in the sheets software using comma as separator
frustrated with the result
Am I missing something?
I notice that Notes have a special role on screwing the way sheets software will understand the CSV.
Also I suspect there are 'return' chars in the exported CSV files, which could be confusing the sheets too.
I hope my explanation makes sense and you can help me solving this one.
Thank you,
-GD
Comments
-
Hi @gabrieldib,
I'm sorry to hear that you've been having trouble exporting your data. I've done some testing here, and I'm not quite sure where your frustration is coming in. Here's what I see after exporting my test vault into a .csv format and importing that into a Google Docs spreadsheet:
This is organized and sorted as I would expect. Are you seeing something different?
0 -
Hi Megan,
You are right, if I use the demo vault it works perfectly. I tried both "All items" and just a few Login items and both worked perfectly, using the demo vault. But when I switch back to my vault, it doesn't behave the same way. The imported spreadsheet from the CSV generated using my data is completely scrambled. Part of it is right but at some point things get scrambled. I noticed the break starts on one of the 1password's letter+symbol+number suggested passwords I am using. Maybe one or more characters are breaking the quotes and creating the problem. I cannot post the CSV here, but I could send it to you...
-GD
0 -
Look to see if that password or entry contains a comma or a double-quote.
0 -
New informations I got from studying the case:
- The problematic password doesn't have commas or double quotes.
- Using the same complex password on a Login item in the demo vault, it exports normally, when using the same password on my Login items, it breaks the flow. The password: oN?hij;celg)uPt(oM#cymT&yEn#Noo)quauCh@yAs(hAy%rE*
- I avoided using the item with this password while exporting, and noticed that a first item exported fine, but the next item instead of going to the next row on excel, it just continued on the same row...
Well, while writing the above text I did some more tests using Google Docs instead of Open Office and it worked. I guess the problem is on Open Office side, sadly.
0 -
But one question remains: How do I export Custom Fields other than exporting All fields?
0 -
I am trying to figure what are the names of the custom fields and the used fields and the fields I didn't use. I exported all fields and now I am deleting the blank fields...
If there was an option "Export Only Used Fields" that would be extremely useful!
0 -
CSV exporting is limited, and this is primary a function of CSV being a very limited data format and "standard".
Can I ask what you are ultimately trying to accomplish? You mentioned the method you are using, but not the final goal.
0 -
I would like to be able to export any data in 1password to at least google sheets and filemaker without any troubles... why? because of the way me and my employees use data. I think it is great having it all in 1password, but being able to export/import into other databases would be brilliant.
0 -
See my comment here, which explains the problems with CSV:
https://discussions.agilebits.com/discussion/comment/132323/#Comment_132323
You could export as a 1PIF file and create a converter to produce a format that is consumable to your end software. 1PIF is complete and lossless.
The method used by some wallet software to "solve" the varying column CSV issue is to employ a few common, standard columns across all rows, one of which is a Notes column, where inside the Notes column are all the extra fields for a card, encoded in some format such as label:value. But this is a lossy encoding format if values can be multi-line and solving that problem becomes yet another encoding or data-structure problem.
Please note I am not affiliated with AgileBits, and this is by no means an official response - its is a technical one from a friend and guy with a little know-how.
0 -
Hi @gabrieldib,
How do I export Custom Fields other than exporting All fields?
Sounds like you've already figured out that fields you don't want to export can be removed under the All Fields section:
I am trying to figure what are the names of the custom fields and the used fields and the fields I didn't use. I exported all fields and now I am deleting the blank fields...
That can be easier than determining which of fields to keep in All Fields …
If there was an option "Export Only Used Fields" that would be extremely useful!
Indeed, and I'll pass that suggestion along to our developers. Thanks!
I hope @MrC's comments are helpful. As he implied, exporting complete/lossless 1PIF (JSON) and converting that data to desired formats can be preferable to exporting CSV.
ref: OPM-2280
0 -
"converting that data to desired formats can be preferable to exporting CSV" - one easy way to do this is to upload the JSON to json-csv.com then open the CSV up in a spreadsheet to redact.
0 -
I just don't think I'd want to put all my passwords and secure information in effectively "open" format on a third party site on the web...for any reason at all! ;)
Stephen
0 -
Hi @allisonbailey,
I wholeheartedly agree with @Stephen_C about this. It's strongly recommended never to transfer unencrypted 1Password data containing passwords and other sensitive information anywhere there's any risk of it falling into the wrong hands.
0
