List Archives >  EndNote List Archive >  Archive by date >  This Month By Date >  This Month By Topic

Re: EndNoteX2 to excel in columns not all info in a row

Search email archive for  

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Mon, 29 Jun 2009 13:27:53 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: dpoyser (New User) Date: 06-29-2009 09:27 PM Hello all:   I am having a similar problem, but with Endnot 9.  As I am new to the software, I assume there is some minute detail I have missed in the extensive Google and forum search processes.   I have tried several times, all unsuccessfully to save the Endnote database in a format that is easily accessible in the Excel or Access formats.  While I was able to save the library as a .txt format, I was unable to then reopen it in Endnote (which one respondent said would remove the carriage returns responsible for causing all the unnecessary splits in the data across cellse), or in Excel (using the Data import feature).  I am running MS Office 2007, and there seems to be only one option to import any data, and it does not work for me after setting up a database much like Maritha's example, i.e.: author, date, journal, title, etc.   Having exhausted what appears to be all the available options, I have resorted to submitting this question to the forum in hopes that some more experienced individual will be able to help me.  Thanks in advance!   Darenne

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Mon, 29 Jun 2009 15:51:39 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: Leanne (Authority) Date: 06-29-2009 11:51 PM I meant  ;<space> to indicate that you needed a semi-colon followed by a space. not the word itself:smileyhappy:

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Tue, 30 Jun 2009 05:22:10 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: myoshigi (Maven) Date: 06-30-2009 01:21 PM Hello Darenne,   We could be of some help, but you have to elaborate a little bit more what you are trying to do. Do you want to export from EN and import to Excel, or the other way around?   Import and Export from Endnote is controlled by Filter and Style, respectively. You need to set up import Filter such that your .txt file is imported to EN correctly. Likewise, you need to set up Style file that matches with your need.   Are you exporting reference data from EN, find/replace CR or LF in the .txt file using Word, and import to EN again? At least you could use "Change Text" function in EN to replace CR.   So, please tell us what you are trying to do, first.

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Tue, 30 Jun 2009 06:29:36 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: dpoyser (New User) Date: 06-30-2009 02:29 PM Hi there:   Thanks for the confirmation Leanne!   I am trying to export from Endnote and import into Excel.  I tried to use MS Word to replace the CR; I also tried change text to remove the CRs, but perhaps I went about this wrong.  However, I am not sure if this even worked because for some reason, I am unable to import back into EN upon making all of my changes.  Perhaps you could reiterate?  I know this has been covered thoroughly here, but I cannot get it to work.   I altered the Style to suit my needs, including Journal, Date, Author, Year, Article title, etc and created an Excel spreadsheet that had matching columns with these headings.  Unfortunately, for whatever reason, this method did not work either and I spent hours on this yesterday, only to throw up my hands in utter confusion, as it does not seem like it should be this hard! :-)   Thanks very much myoshigi; please let me know if there is anything further you need clarification on.  Your help would be GREATLY appreciated.

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Tue, 30 Jun 2009 07:13:51 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: myoshigi (Maven) Date: 06-30-2009 03:13 PM Okay, now I know more about what you need to do (still need more info, but let's figure them out later).   So, when you use import command in Excel, you use tab as a delimiter, right? This means you need to export from EN as "tab delimited text". You can use comma as field delimiter, but I don't recommend comma as a delimter (CSV format), because your title and authors may have comma. Important thing you need to know here is, tab (or comma) is a field delimiter and CR is a record delimiter.   First,  make a simple style like this: Author[tab]Title[tab]Year[CR]   Then, select references you want to export, and use copy formatted commnad (Ctrl + K) from your EN library. Go to word, from Edit, "Paste special" and "Unformatted text". It is better to turn on "All" in the Word's Tools/Option/View/Formatting marks, to see all the formatting marks. Make sure you have all the tabs between field and CR at the end of each line. This means, each line corresponds to each record. Save this document as xxx.txt format. Then, import this .txt document using Excel.   Once you work this simple thing out, you can add more fields to the Style template. If you include Keyword field or other fields that contain CR, import will screw up because Excel considers CR as the delimiter between records. You have to replace CR with something like ; (semicolon) within EN, such that CR is not found except for deliminating records (single line - single record).   If you still have problems, please let us know.

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Tue, 30 Jun 2009 08:21:16 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: dpoyser (New User) Date: 06-30-2009 04:21 PM Oh my goodness, I think it works!  I selected only a few documents in order to try it out, and it imported to Excel with no problem!  Thanks so much!  Going to attempt the entire library now (this is the smallest of the archives I am trying to do this with).  The only problem is with a book section (all the others are journal articles), which did not go into Excel as expected, but will see if the other journal articles work.  Will follow up.  Thanks so very much for your help!

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Tue, 30 Jun 2009 09:29:11 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: myoshigi (Maven) Date: 06-30-2009 05:28 PM You can actually make your export style, such that first field (Journal, Year or whatever that doesn't have CR) is formatted as Bold font. Then, Ctrl+K to the Word document. You would expect each line starts with Bold font. If you find a line, that doesn't start with Bold font, there should be an unexpected CR somewhere in the output.   Sometimes, book sections and things alike may have CR "within" a filed data. So, you would be able to detect such anomaly using this method.

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Tue, 30 Jun 2009 09:36:52 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: dpoyser (New User) Date: 06-30-2009 05:36 PM Alright, will try to do that as well.  So far:   I copied formatted to Word Imported to Excel All appears normal save a few exceptions where page numbers appeared as dates; book sections appeared out of order I added hyperlinks for e-copies of articles using Excel so that database info will be available to other staff who do not have Endnote   This saves me much time because I am the only one with Endnote and am responsible for updating articles on a monthly basis in a system-wide Excel sheet, a soft .pdf library, and a hard copy archive.  I think I will need to do this export on a monthly basis to make sure all is up to date.  This allows me to simply do it once in Endnote then do the export without the added data entry.  Will also have to make a few changes so that all fields that do not transfer correctly will appear correctly in Excel.   Thank you so much for your assistance; again, will follow up re: the boldface option in first fields to root out anomalies.  You're a lifesaver.

Re: EndNoteX2 to excel in columns not all info in a row
Author: Community Mailer    Posted: Tue, 30 Jun 2009 10:17:36 -0700
Subject: Re: EndNoteX2 to excel in columns not all info in a row Author: dpoyser (New User) Date: 06-30-2009 06:17 PM Very obvious follow up question I neglected to ask:   Is there also a way to export from Excel and import in EN?  I'm not sure reversing the methodology would work.  Would prefer some guidance vs. doing what I did yesterday and accidentally causing VERY BAD CHANGES to essential files.  Fortunately, was able to get it back.   Thanks again

Previous by date: Re: New features in version X3, Community Mailer
Next by date: exporting from Endnote 9 to alternate database format, Community Mailer
Previous thread: importing attachments from papers, Community Mailer
Next thread: exporting from Endnote 9 to alternate database format, Community Mailer