XLS -> Zotero
March 4, 2019 5:10 AM Subscribe
I’m a writer. I just came across a messy but accessible gold mine of a digital archive, spun up a web scraper, and now have several thousand primary source documents in hand, along with a single Excel spreadsheet with extensive data on each document. I use Zotero to manage my research materials. Unfortunately, the team behind Zotero still thinks that importing data from Excel into Zotero is a fringe use case, and there is no way to do it directly. Is there a workaround?
I have no interest in switching away from Zotero to another reference manager. But, I’m happy to use an intermediate step (XLS -> ??? -> Zotero) for the sake of importing the data. Would prefer not to do this manually since (1) it’s a lot of material, and (2) I have already started scraping this archive for other projects, and they continue to release documents on a regular basis, so I expect this to be an ongoing need.
I have no interest in switching away from Zotero to another reference manager. But, I’m happy to use an intermediate step (XLS -> ??? -> Zotero) for the sake of importing the data. Would prefer not to do this manually since (1) it’s a lot of material, and (2) I have already started scraping this archive for other projects, and they continue to release documents on a regular basis, so I expect this to be an ongoing need.
It should be possible to convert the records in RIS format using the Power Query feature in Excel.
posted by elgilito at 5:45 AM on March 4, 2019 [2 favorites]
posted by elgilito at 5:45 AM on March 4, 2019 [2 favorites]
It's funny that the Zotero folks didn't mention Tropy, from the same org. Tropy is quite new and focused on research image collections, so might have a better chance of a useful import? https://tropy.org
posted by lillygog at 6:52 AM on March 4, 2019
posted by lillygog at 6:52 AM on March 4, 2019
Response by poster: elgilito - That's an interesting idea. I know of Power Query but have never used it. I'll give that a look.
DarlingBri - Thanks for that suggestion! I'm okay spending the money to get the capability to do this on a regular basis. If I can't get Power Query to work, I'll go down this path.
lillygog - Thanks! I know Tropy, and have an instance installed for another project, but don't think it would work for this. Also, I don't think it exports to Zotero (which is why, as much as I love the team at CHNW, their product decisions drive me nuts).
posted by NotMyselfRightNow at 7:39 AM on March 4, 2019
DarlingBri - Thanks for that suggestion! I'm okay spending the money to get the capability to do this on a regular basis. If I can't get Power Query to work, I'll go down this path.
lillygog - Thanks! I know Tropy, and have an instance installed for another project, but don't think it would work for this. Also, I don't think it exports to Zotero (which is why, as much as I love the team at CHNW, their product decisions drive me nuts).
posted by NotMyselfRightNow at 7:39 AM on March 4, 2019
Definitely don't buy EndNote--you don't need to spend the money.
If you can convert the records using Power Query, you can export from Excel into a CSV file, then use something like Python to pretty trivially read the CSV and output into an RIS file.
Variables that would make this slighly harder, but far from insurmountable, are:
- Is every field filled in the Excel file?
- Make sure to export so it maintains blank fields
- If not all fields are filled, will Zotero accept incomplete records?
- Are there fields in the Excel file that aren't available in RIS format?
etc.
posted by Special Agent Dale Cooper at 7:53 AM on March 4, 2019
If you can convert the records using Power Query, you can export from Excel into a CSV file, then use something like Python to pretty trivially read the CSV and output into an RIS file.
Variables that would make this slighly harder, but far from insurmountable, are:
- Is every field filled in the Excel file?
- Make sure to export so it maintains blank fields
- If not all fields are filled, will Zotero accept incomplete records?
- Are there fields in the Excel file that aren't available in RIS format?
etc.
posted by Special Agent Dale Cooper at 7:53 AM on March 4, 2019
Ref2RIS might help? (Also, it was made by a librarian.)
posted by unknowncommand at 5:23 AM on March 6, 2019
posted by unknowncommand at 5:23 AM on March 6, 2019
Response by poster: The suggestions above led me to a much simpler solution:
I designed a MS Word mail merge to pull from the MS Excel file and create a document that matched the RIS format (using details found in the RIS Wikipedia entry):
TY - [document type]
TI - [document name]
AV - [location in archives]
DA - [document date]
DB - [archive info]
UR - [document URL]
AB - [abstract]
ER -
Ran the mail merge. Got a MS Word doc with all the info. Copied and pasted into a Notepad file, and saved it as name.RIS. Imported the file into Zotero.
Worked flawlessly.
posted by NotMyselfRightNow at 10:29 AM on March 9, 2019 [1 favorite]
I designed a MS Word mail merge to pull from the MS Excel file and create a document that matched the RIS format (using details found in the RIS Wikipedia entry):
TY - [document type]
TI - [document name]
AV - [location in archives]
DA - [document date]
DB - [archive info]
UR - [document URL]
AB - [abstract]
ER -
Ran the mail merge. Got a MS Word doc with all the info. Copied and pasted into a Notepad file, and saved it as name.RIS. Imported the file into Zotero.
Worked flawlessly.
posted by NotMyselfRightNow at 10:29 AM on March 9, 2019 [1 favorite]
:::golf clap:::
Really, nice job--awesome problem solving. A super cool, low-overhead solution.
posted by Special Agent Dale Cooper at 10:01 PM on March 11, 2019
Really, nice job--awesome problem solving. A super cool, low-overhead solution.
posted by Special Agent Dale Cooper at 10:01 PM on March 11, 2019
« Older Do we need intercom, Echo, walkie-talkie or ? | What are the chances? Card distribution, game of... Newer »
This thread is closed to new comments.
posted by DarlingBri at 5:24 AM on March 4, 2019