Some export related questions from an EndNote newbie

Hello,

I’m completely new to EndNote and have some questions about the export function and the Output Style options. Let’s explain the situation first:

My girlfriend uses EndNote to import Medline formatted textfiles, downloaded from PubMed, and to export the reformatted data to comma separated textfiles that can be used to import in a FileMaker database.

We just created a new Output Style in EndNote in which we defined the following:

  • At the Bibliography, Templates section we defined this style:

“Title”,“Secondary Title”,“Volume(Number)”,“Pages”,“Author”

  • At the Bibliography, Author Lists section, we defined a “,” should be printed between every single author and before the last author.

When exporting the References to a .txt file, the result looks like this:

“Cartilage-hair hypoplasia-associated mutations in the RNase MRP P3 domain affect RNA folding and ribonucleoprotein assembly”,“Biochim Biophys Acta”,“1783(3)”,“455-66”,“T. J. Welting”,“S. Mattijssen”,“F. M. Peters”,“N. L. van Doorn”,“L. Dekkers”,“W. J. van Venrooij”,“H. A. Heus”,“L. Bonafe”,“G. J. Pruijn”

“A compendium of familial relative risks of cancer among first degree relatives: a population-based study”,“Int J Cancer”,“123(7)”,“1664-73”,“M. P. Zeegers”,“L. J. Schouten”,“R. A. Goldbohm”,“P. A. van den Brandt”

Although the result is a perfect CSV formatted textfile, which can be used to import in the FileMaker database, I have some questions I hope one of you can answer:

  • How can I configure field labels? I do need field/column labels in the first line of my CSV formatted textfile, because FileMaker uses these labels to “map” them to the correct fields in the database table. Now I use the MS-DOS command copy to merge the EndNote’s export file with another textfile that contains the field labels.
    Is there an easy way to let EndNote print the labels to (only) the first line?
  • In some cases, the Pages field is empty. Empty fields aren’t exported in my textfile. How do I force EndNote to export fields, even if they are empty? In case of an empty field, the textfile should contain: “<value>”,"","<value>" (the field in the middle is empty).
  • 371-5
    In the textfile, created by EndNotes export function, I want these values splitted into two separate fields, like:
    “371”;“5”
    The “-” sign can be left out.

I hope someone can help us by explaining how to do the above…

TIA,

Timothée

I vaguely remember the days when I switched between Endnote and Filemaker Pro in Mac OS 8. So, excuse me if I’m wrong.

Your Filemake use the first line to map the field (and ignore the line itself), and import the second line CSV as data, right? Then, you can simply have the filed definition, like “title”,“journal”,“volume”,…, then insert carriage return(in fact end of paragraph) in the style definition. The second line should be the same format as you made.

Empty page field: By default, Endnote does not format the entry is empty, which means your CSV will have inconsistent field numbers. The work around might be, search entry that doesn’t have page number in your Endnote library (Search Page IS null), then use Change Filed command to enter some special characters, like # or *. Delete them later in Endnote and Filemaker. (Not an elegant way)

As to the page range output, use the full range format first, e.g. 123-125 as your output format. Then you need to edit the CSV using text editor or Word to replace - (dash) with “,”

Now, the problem is the search and replace - (dash) would replace some other dash contained in the title, like “cell-cell junction”. Then, you have to use smart command line editors like sed or gawk (I’m assuming you’re familiar with these tools, because I heard “MS-DOS” for the first time in this forum!)

Besides, I would use tab-delimited text instead of CSV because article titles sometime have commas.

Hope this helps.

p.s. If you need the field definition for the first line, and have multiple data output following the first line, your method (adding the first line by Word or other text editors) is the only way. Endnote doesn’t have such export function.

Message Edited by myoshigi on 01-31-2009 01:50 PM

Hi Myoshigi,

Thank you for your quick reply!

You are correct that FileMaker uses the first line to map the fields from the textfile to the database fields. That’s why I need fieldnames on the first lines of my textfile that is exported by EndNote.

Fieldnames on first line / column headers:

I already tried your suggestion to type the text on the first line of the Output Style template. The text is printed to the output textfile, but not only on the first line, but on every second line! For every reference EndNote prints two lines: the first with the “fixed” fieldnames / text and a second line with the values / data.

I think you are right that EndNote doesn’t have an export function to print fieldnames only on the first line of the output textfile. That’s a pity…
I do need some “header” function in which I can place the “fixed” text (the fieldnames / headers). In the template I only define the fields which contain the data / values.

Empty fields:

I tried the “Change Fields” option in EndNote. It does what I want, but it is a manual action. I want to automate this…

When I doubleclick a reference in EndNote, it shows me the details of that reference. The “Pages” field is displayed, but of course without a value. I think, that when EndNote shows me the “standard” field “Pages” on the detail screen, it should be possible to force EndNote to export this field, regardless the value of the field.

As you mentioned, my CSV file has inconsitent fields due to this. I filter them manually, but as said, I want to automate this process and not filtering / editing the files by hand.

Splitting pages field:

I’m very familiair with editors like UltraEdit and Notepad++, so I could make a macro to do this. That’s no problem. The problem is, however, that I don’t want to use additional tools, I simply want to import something in EndNote, selecting the Output Style and export it again in a way FileMaker can import it directly. The more additional programs I need to use to edit EndNote’s output file, the more error-prone the process will be. I’m searching for the most easy way to handle this so my girlfriend can do it herserlf :slight_smile:

>Besides, I would use tab-delimited text instead of CSV because article titles sometime have commas.

Well, EndNote uses tab-delimited CSV by default and FileMaker can import tab-delimited files by default. BUT, when exporting to tab-delimited format in EndNote, EndNote will always use the characters between multiple authors defined in your Output Style template. So, the different fields will be separated by a tab, but the authors are in one field. In FileMaker I need to import each author in a different field, so I need to separate the authors. So, by default, the CSV file contains two different separators: a tab and a comma (or another character that is defined in the template). That’s not very practical, and it’s the reason why I use “,” between every field. If there’s a comma within a field’s value, it isn’t interpret as a separator by FileMaker because of the " signs.

P.S. Do you also have problems with the WYSIWYG editor while typing a reply in this forum? The scrollbar scrolls up and down automatically everytime…it’s quite irritating!

Hmm…it seems to be a problem of IE7. In Firefox 3 everything is fine…

Message Edited by tba on 01-31-2009 11:17 PM

I understand you want to automate the export/import as much as possible. I used to think that way, when I was using Filemaker for print formatting (Endnote’s print function is very primitive, while Filemaker has fantastic layout flexibility).

In the end, I gave up doing ping-pong between two software, because of the hassle like the one you are experiencing. Endnote did not start as a database software. Relational database structure was implemented only recently. It is essentially text formatting tool. But, people expect more and more, as if it should be like the Oracle’s database. That probably won’t happen. 

Well, in my opinion I’m not requesting any (relational) database feature. As said in my introduction I’m completely new to EndNote and to be honest, not very aware of which purposes this program is used for. In fact, I’m only using it because it can “tranform” the MEDLINE textfiles into .CSV files that can be processed by another application, in this case the FileMaker database application.

If EndNote is, as you say, a text formatting tool which officially offers export functions to create files that can be processed by other applications, I would expect some (export specific) options like printing headers and printing empty fields so that the created CSV file is correctly interpret by other import tools.

The CSV file EndNote produces now can not be used by other import tools because of the incorrect column lay-out due to the missing (empty) fields.

I’m afraid my girlfriend has to use Excel to manually edit the file before importing it in FileMaker.

I share with your views, and feel sorry for your girl friend, but using excel or some other secondary tools is the only way in the meantime. You can always make suggestions to Thomson, but it is actually implemented or not is their decision, as you know. By the way, why she needs to import to Filemaker? Are there anything only Filemaker can do for bibliography organizing/formatting?

These are things I would try, but I have never tried importing the EN library into FileMaker, so they are just guesses.

You asked:

  • How can I configure field labels? I do need field/column labels in the first line of my CSV formatted textfile, because FileMaker uses these labels to “map” them to the correct fields in the database table. Now I use the MS-DOS command copy to merge the EndNote’s export file with another textfile that contains the field labels.
    Is there an easy way to let EndNote print the labels to (only) the first line?"

Couldn’t you creat a record in the library that has the header names in the fields and make sure it is sorted first. Create a custom field to sort on and all your files would be blank, but your “header record” would have an a or a 1 in it, and you probably want to reverse sort, so it is first, then if you need the rest sorted, you will pick the second parameter as that parameter, but if you are just going to import them, then you don’t need the rest sorted anyway.

You also asked:

  • In some cases, the Pages field is empty. Empty fields aren’t exported in my textfile. How do I force EndNote to export fields, even if they are empty? In case of an empty field, the textfile should contain: “<value>”,"","<value>" (the field in the middle is empty).

If you add spaces around the field name export style, you should maintain the empty values and tabs.  At least it worked with the tab delimited style I  tested by cutting and pasting the template into word and replacing the ^t with _^t_ (where the _ is a normal space) and pasting it back in.  It seemed to keep the tab space-space tab in the output. 

You asked:

  •  
    • By default, the Pages field is displayed as follows:
      371-5
      In the textfile, created by EndNotes export function, I want these values splitted into two separate fields, like:
      “371”;“5”
      The “-” sign can be left out.

 

Can you search and replace the pages field in (a copy?) of your library and replace it with a comma space?  If so, then it should split the two outputs.  You would also have to put that into the field header in the dummy record too. 

 

@ myoshigi: By the way, why she needs to import to Filemaker? Are there anything only Filemaker can do for bibliography organizing/formatting?

>Well, the research institute she works at uses FileMaker as some sort of information system and the publications information is only one part of the collected information. That’s why she was searching for an easy way to import PubMed data into the FileMaker database. A collegue told her she could use EndNote and edit the textfile with Excel before importing in FileMaker. I suggested that this could be done easier, but I’m afraid it isn’t due to the missing features in EndNote. Maybe there are some other tools I could try, but I’m not aware of any and am not very familiair with this kind of stuf myself.

@Leanne: thank you for your suggestions, I tried them, but they don’t solve the problem completely.

> easy way to import PubMed data into the FileMaker database

If that is the case, I would develop import filter directly for FileMaker, using MEDLINE output from Pubmed. I remember FileMaker Pro has several good features to make own filter. Also, there are small utilities like Pubmedmaker that import pubmed data directly into filemaker database. Is using Endnote necessariy?