Importing from Excel to Zotero?

As I know, there isn't way to import data from Excel to Zotero. Main reason that prevents importing is that Excel data isn't properly, how to say, attributed, as I know.
But maybe some workaround can be done? I don't know about XML and coding anything, so I managed to do only such a thing: attribute Excel file date to Zotero fields.
I created XML map file (.xsd) for my Excel sheet with database of books and applied it after that: http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2008/07/30/10644.aspx Quite easy.

My xsd file:
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">;
<xs:element name="books">
<xs:complexType>
<xs:sequence>
<xs:element name="books" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="callNumber" type="xs:string" use="required" />
<xs:attribute name="author" type="xs:string" use="required" />
<xs:attribute name="title" type="xs:string" use="required" />
<xs:attribute name="place" type="xs:string" use="required" />
<xs:attribute name="publisher" type="xs:string" use="required" />
<xs:attribute name="date" type="xs:string" use="required" />
<xs:attribute name="numPages" type="xs:string" use="required" />
<xs:attribute name="series" type="xs:string" use="required" />
<xs:attribute name="archive" type="xs:string" use="required" />
<xs:attribute name="libraryCatalog" type="xs:string" use="required" />
<xs:attribute name="archiveLocation" type="xs:string" use="required" />
<xs:attribute name="accessDate" type="xs:string" use="required" />
<xs:attribute name="notes" type="xs:string" use="required" />
<xs:attribute name="ISBN" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

As you can see rows are mapped to Zotero field variables.
After conversion to XML I've got such a file:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<books>
<books callNumber="1795 x" author="Husovianas M." title="Giesmė apie stumbrą" place="Vilnius" publisher="Vaga" date="1977" numPages="105" series="Lituanistinė b-ka, 19" archive="S" libraryCatalog="0.3" archiveLocation="Jėzuitynas" accessDate="02 11 27" notes="" ISBN=""/>
<books callNumber="1796 x" author="" title="Lietuvos gyventojai: statistikos rinkinys" place="Vilnius" publisher="Mintis" date="1990" numPages="87" series="Statistika visiems" archive="S" libraryCatalog="0.3" archiveLocation="Jėzuitynas" accessDate="02 11 27" notes="" ISBN=""/>
and so on.
Now this needs translator from such xml file to Zotero :).
That is limit of my "coding" capabilities. But maybe exists translator from such a xml file to Zotero. Or maybe this can show the way of importing data from Excel file to Zotero without mediation of EndNote (as it is now).

Maybe that will be helpful to someone.

Code exceeds field of Firefox, sorry for that.
  • There is no existing translator that can import that, no, though it wouldn't be terribly hard to write one (you could look e.g. at the MARCXML translator as a template on how to read XML, but you wouldn't need to call on another translator - alternatively, you can look at the part of the Primo translator that imports XML).

    If you have larger code snippets you want to post it typically makes sense to put them as a public gist on gist.github.com and link to them.
  • No, that is too much for my coding "capabilities" :). I can only modify existing translator but can't write new. Too much unknown to me. I managed to import only empty records or to get an error during import.
  • I almost succeeded in importing from Excel to Zotero via... BibTeX format. No need to write translator, to code etc. Very simple solution to average (not dumb :)) user.

    There: https://groups.google.com/forum/?fromgroups=#!msg/comp.text.tex/mnG8n4iwrM4/R8L5aGeI4c0J I found simple solution:
    Suppose you have your excel table
    like this:
    msp82 Milner The Pi Calculus
    ws01 Wischik Explicit Fusion Machine
    then you could make a fourth column to the right of these with the formula
    ="@Article{"&A1&", author="""&B1&""", title="""&C1&"""}"
    Then simply select this right hand column, and paste it into a text file,
    and Hey Presto! you have your bibtex without the need for any intermediate
    programs.
    And it works like a charm after some modifications to my needs.

    But I can't get imported "Access date", "Archive" and "Library catalog" fields. Latter two haven't mapping in translator (is there any possibility to do that for my needs only? How to map them, to which BibTeX fields?).

    And I don't understand, what is wrong with "Access date" field. It exports from Excel to txt in such form:
    lastchecked="2002 11 27", [y m d format]
    But Zotero field "Accessed" is empty after importing that txt. All other fields imports well. What is wrong?
  • If I read this right, people are generally fine with including accessed dates, but they're not in the current BibTeX translator:
    https://groups.google.com/forum/?fromgroups=#!topic/zotero-dev/UBgYrnYahPg
    my inclination would be to go with urldate on export and import from either lastchecked or urldate.

    Since Library Catalog is practically never cited, I doubt there is a corresponding bibtex field. Archive might have one, but I suspect that there is no agreement, so you'll find different versions for each bibtex style - you can just make something up for a custom translator: the field mappings are almost all at the top of the bibtex translator.
    You could probably also just add whichever accessed field mapping you prefer there.
    (more complex fields like author and the date fields are towards the bottom).
  • Thank you. I solved all problems except one: how to export properly formatted dates from Excel. Zotero can import only such format of date:
    lastchecked="2002-11-27"
    So I converted cells with dates from text to date format. And now I am getting such a format after exporting from Excel:
    lastchecked="37587"
    (number of date, but not date itself). How export dates from Excel keeping their format?
  • lastchecked and urldate now import in bibtex Zotero with the regular bibtex translator.
    Can't help you with the Excel question - Zotero should import in a bunch of formats actually, including 11/27/2002, Nov. 27 2002 and some more, but I'm not 100% sure.
  • All the problems are solved now and all references (about one thousand) are transferred from Excel to Zotero.
    I deliberately mapped BibTeX fields to suitable Zotero fields in in BibTex translator so all data was transferred.

    My steps to transfer access dates was to change appropriate Excel field from general to date format (essential was conversion "Data -> text to columns" from there: http://www.ozgrid.com/Excel/convert-excel-date-formats.htm ), format all dates and then convert them to text by such formula: =CONCATENATE(TEXT(A2;"yyyy-mm-dd")) (from there: http://www.ozgrid.com/forum/showthread.php?t=59850 ).

    There were some problems with author names. Names were inverted - last name first. So I changed translator line fromcreator = Zotero.Utilities.cleanAuthor(name, field, false); to creator = Zotero.Utilities.cleanAuthor(name, field, true); After that last names and first names were recognized properly.

    Also author names were separated by semicolons in my Excel sheet. So I changed translator line form } else if(field == "author" || field == "editor" || field == "translator") {
    var names = value.split(/ and /i);
    to} else if(field == "author" || field == "editor" || field == "translator") {
    var names = value.split(";");
    and after that all authors were separated properly.

    I think, such way of importing references from Excel to Zotero is quite convenient: there is no need for EndNote as intermediary; only simple and easily adjustable Excel formulas, quite good BibTeX translator. Maybe it is worth to prepare some "how to"?
  • I would LOVE a "How To" like that. Actually, just publishing a redacted copy of the excel file Maras used would be very helpful.

    And Maras: Thanks so much for exploring this option. I think it would be very helpful for many people (like me) who want to get large numbers of references from the bibliographies of articles into Zotero.
  • So, let's assume, that Excel sheet is like this:
    | A | B | C
    1 |Call No | Author | Title
    2 | 1795 x | Husovianas M. | Giesmė apie stumbrą

    You want to copy second line. So then you should make a fourth column (row D) to the right of these in second line with the formula
    ="@book{lccn="""&A2&""", author="""&B2&""", title="""&C2&"""}"
    There can be other types of references (article, chapter etc.) instead of "book" and other fields (and more of the fields, of course). BibTeX names you can read from Zotero BibTeX translator: types of reference (section "var bibtex2zoteroTypeMap") and field names (sections "var fieldMap" and "var inputFieldMap"). So you can assign right field name to the row. Call number is called "lccn" in BibTeX so I assigned to A row "lccn" value and so on. Number besides A, B, C in formula refers to Excel line number.
    If there are more than one line of references, you should point with mouse to the bottom right corner of cell D2 (in which formula resides) and drag it downward D row until all lines with your references are covered. Excel converts formula according to the lines (A2 becomes A3 in a third line and so on), so you don't need rewrite formula manually.
    After that you should copy only D row of Excel sheet (that part of it with formulas), paste it to simple txt file (if there are unicode characters in references save it in utf-8, not ANSI) and select that file to import with Zotero. That's it.
  • Thanks Maras,

    Although I think of myself as computer competent, following your suggestion turned out to be quite difficult (simply because finding and then integrating all the BibText fields into an excel file that includes all types of documents turned out to require lots of fiddling). After screwing around with it for 3 hours today, I finally just searched for "plain-text to BibTex" and found this website.

    http://text2bib.economics.utoronto.ca/

    It provides a reasonably quick way to get plain-text references into
    Zotero. It took me about 15 minutes for 100 references. Entering them manually took 2-3 times that. If you're doing lots of references, this is a great website.
  • There are other tools for importing reference lists into Zotero listed here
    http://www.zotero.org/support/kb/importing_formatted_bibliographies
    I've added text2bib - thanks.
  • Glad to hear that you succeeded in importing.

    I think, my solution is best for one type references or with references that are sorted by type.

    Translator is placed in Zotero data directory (Zotero preferences -> Advanced -> Show data directory) translators folder.
    There is Zotery field types: http://aurimasv.github.com/z2csl/typeMap.xml Search BibTeX translator for mappings and put BibTeX entries to the formula.

    Automatic translator is easier way of course but my references were too complicated (such a formula: ="@book{lccn="""&A1&""", author="""&B1&""", title="""&C1&""", address="""&D1&""", publisher="""&E1&""", year="""&F1&""", pages="""&G1&""", series="""&H1&""", abstract="""&I1&""", copyright="""&J1&""", location="""&K1&""", lastchecked="""&L1&""", note="""&M1&""", isbn="""&N1&"""}" ) to automatic conversion.
  • If interested, another solution to export Excel ou Access file in RIS format compatible with Zotero

    => https://forums.zotero.org/discussion/12649/import-from-excel-and-access-databases/
  • Hi,

    The procedure described above by maras for importing data from an Excel list into Zotero works (almost) perfect, thank you!
    I don't know if there is a completely different method by now and therefore my question is unnecessary, but can someone tell me what 'code' I can use to import the fields "Archive", "Location in archive", "Library catalog" and "Call Number"? The straightforward labels "archive", "archiveLocation" and "callNumber" are unfortunately not imported at all; "lccn" appears under "Extra"...
  • What format are you using for import? BibTeX? RIS?
  • I actually don't know; I copy the data from a column in Excel and then import it into Zotero from the clipboard.
  • What does the data look like ?
  • For example, like this:
    @book{author="Norman Bryson", title="Looking at the Overlooked. Four Essays on Still Life Painting", year="1990", language="Englisch", edition="", volumes="1", pages="192", address="London", publisher="Reaktion Books", isbn="0948462078", series="", number=""}
  • Ok, and what names or code does Bibtex use for the "# of volumes", "Archive", "Location in archive", "Library catalog", "Signature" and "Extra"-fields in Zotero?
  • There's no fixed mapping for all of those fields, it depends on the item type, and you would have to take care to escape some characters properly. I think RIS is going to be easier.
  • These are exclusively books and the data to be transmitted consist mainly of numbers and capital letters, i.e. no special characters, 'umlauts' or the like.

    Sorry that I, as a complete beginner, have to ask everything: Can I just as easily generate a RIS format in Excel and import it into Zotero via the clipboard? How would that work?
  • edited April 19, 2021
    I would think RIS would be easier, but I don't know anything about excel automation, and I don't know enough about RIS to suggest a field mapping.I am very familiar with bibtex though and there is not a bibtex-field-to-zotero-field mapping there, it is going to vary by zotero item type, which is going to make a bibtex export from excel more complicated.

    I think your best bet would be to create some entries in Zotero the way you'd want them to look like after import, export them to bibtex/RIS, and then look whether you can re-create those from your excel file. If you're going with bibtex,I'd recommend installing my "Better BibTeX" plugin, as it does more detailed conversions, but even there I can't guarantee that entries you export with it import precisely the same. Bibtex and zotero use slightly different concepts, and translations are not always lossless.
  • This is what you will want to do to answer all your questions:

    “I think your best bet would be to create some entries in Zotero the way you'd want them to look like after import, export them to bibtex/RIS, and then look whether you can re-create those from your excel file.”
Sign In or Register to comment.