How to Convert PubMed and Research XML Exports into a Spreadsheet

A search can find far more papers than you can read one by one. You need a list you can sort, filter, and share. A spreadsheet is the right tool for that job.

PubMed data often comes as XML. XML is a text format that marks each field with tags. That format is good for systems, but hard to scan by eye. A sheet, with one row per article and one column per field, is much easier to work with.

This guide shows how to turn PubMed and research XML into a clean sheet: where the XML comes from, what is inside it, which tag to use as the row, and how to handle authors, abstracts, and large sets. It gives no medical advice. It is only about handling the data file.

If you are new to this, start with the general XML to CSV steps first. Then return here for the research parts.

Who Needs This, and Why

Students need this when they build a reading list for a class or a thesis. A thesis is a long piece of work for a degree.

Reviewers need this when they screen papers for a review. Screening is the step where you read titles and abstracts and decide which papers fit your topic. A sheet gives each paper a row, and a column where you can mark keep or drop.

Librarians need this when they merge lists from more than one search. Research teams need this when they split screening work between people.

In each case, the goal is the same: one row per article, with title, journal, year, authors, and abstract in clear columns.

Where the XML Comes From

PubMed is a search service for research articles, run by the US National Library of Medicine. You search it with words for your topic.

You can download search results as XML. That means you can save a set of articles in one file, instead of copying each page by hand.

NCBI is the center that runs PubMed and related tools. Its E-utilities are tools that let you fetch PubMed data in forms like XML. The key point is simple: a search gives you a set of articles, and you can get that set as one XML file.

Save that file as it is. Do not edit it by hand first. A small edit can break the tags. File size depends on how many articles are in the set and how long their abstracts are. Later sections cover big sets.

What a PubMed XML File Holds

The file holds a set of article records. Each record is one article. In PubMed XML, that record is called a PubmedArticle.

Inside each record is a citation part. A citation is the set of facts that names a paper, such as its title and journal. That part holds most fields you want: journal name, article title, and abstract text. The abstract is a short summary of the paper, written by its authors.

The record also holds a list of authors, publication dates, and IDs. Authors repeat, because most papers have more than one author. An ID is a short code that names one record. For PubMed, the key ID is the PMID. Each article has its own PMID, so it is the best way to tell two articles apart.

Not every record holds every field. Some papers have no abstract in the file. Blank cells are normal.

Attributes in Research XML

Some values may be held in attributes. An attribute is a name and a value inside a tag, not between tags. On XmlToCsv, attributes are kept as columns, named like element@attribute. So you do not lose a code just because it was stored as an attribute.

A Small Simplified Example

The example below is simplified, with short abstracts. It shows the shape: two article records, each with the same kinds of fields.

<PubmedArticleSet>
  <PubmedArticle>
    <MedlineCitation>
      <PMID>38001001</PMID>
      <Article>
        <Journal><Title>Journal of Sleep Research</Title></Journal>
        <ArticleTitle>Sleep and Memory in Young Adults</ArticleTitle>
        <Abstract>
          <AbstractText>This study looked at how sleep affects memory...</AbstractText>
        </Abstract>
        <AuthorList>
          <Author><LastName>Lee</LastName><ForeName>Anna</ForeName></Author>
          <Author><LastName>Patel</LastName><ForeName>Ravi</ForeName></Author>
        </AuthorList>
        <PubDate><Year>2024</Year></PubDate>
      </Article>
    </MedlineCitation>
  </PubmedArticle>
  <PubmedArticle>
    <MedlineCitation>
      <PMID>38001002</PMID>
      <Article>
        <Journal><Title>Journal of Nutrition</Title></Journal>
        <ArticleTitle>Diet Patterns and Heart Health</ArticleTitle>
        <Abstract>
          <AbstractText>This review summed up past work on diet...</AbstractText>
        </Abstract>
        <AuthorList>
          <Author><LastName>Garcia</LastName><ForeName>Maria</ForeName></Author>
        </AuthorList>
        <PubDate><Year>2023</Year></PubDate>
      </Article>
    </MedlineCitation>
  </PubmedArticle>
</PubmedArticleSet>

With the article record as the row, the sheet looks like this.

The same result as CSV text is below. CSV is a plain text format where commas split the columns. Long text with commas in it is placed in quotes, so the columns stay in place.

PMID,Title,Journal,Year,Authors,Abstract
38001001,Sleep and Memory in Young Adults,Journal of Sleep Research,2024,"Lee, Anna; Patel, Ravi","This study looked at how sleep affects memory..."
38001002,Diet Patterns and Heart Health,Journal of Nutrition,2023,"Garcia, Maria","This review summed up past work on diet..."

The PMIDs and titles here are made up for teaching. In a real file, use the values as they come from the source.

Mapping XML Fields to Spreadsheet Columns

The table below maps common fields to the columns most teams want.

Dedupe means to remove repeat copies of the same record. More on that step comes below. A preview is a quick look at the first rows before you export. On XmlToCsv, the preview shows the first rows plus the total row count.

Pick the Article Record as the Row Element

The key choice is the row element. The row element is the tag that repeats for each row. For this file, the row element here is the article record — the PubmedArticle record.

XmlToCsv finds the row element on its own. Still, check it. If the row were set to the author tag, you would get one row per author, not one row per article.

You can pick another tag, or type a path to the tag you want. Look at the total row count in the preview. It should match the number of articles in your set. If it is far off, the row choice is likely wrong.

Once the row is right, each field inside the record becomes a column. You can drop columns you do not need, and rename the ones you keep.

Paid plans can save field mapping templates. A field mapping is a set of rules that says which source field goes to which column, and what it is called. That helps when you run a new search each month.

Authors Repeat: Join Them or Expand Them

Authors need a choice. Most papers have more than one author, so the author field repeats inside one record. XmlToCsv lets you join repeated values with ; in one cell, or expand them to one row per value.

Join them when you want one row per article. That is the right choice for screening. Your keep or drop marks stay simple, and the full author list sits in one cell.

Expand them when you want to count or sort by author, or build a pivot table by author. A pivot table is a summary table that counts data by group. For that job, use one row per author for counting and pivots.

If you expand, the sheet will have more rows than there are articles. Do not use an expanded sheet for screening counts, or you will count some articles more than once.

Author names may come in parts, such as last name and first name in separate tags. Check the preview so no part is lost.

Abstracts Are Long Text

Abstracts are much longer than titles or names. That needs care.

Use XLSX when abstracts matter. XLSX is the file format for Excel sheets. XmlToCsv exports to CSV and to XLSX. XLSX holds long text in a cell well, and you can wrap the text so it is easy to read.

CSV can also hold long text. But some tools may have cell limits or show long text poorly. A cell limit is the most text a tool will hold in one cell. If an abstract looks cut off, try a different tool, or export as XLSX, before you assume the data is lost.

Test with a small file first. Check one full abstract against the same paper on PubMed. Keep the full text if you will screen on it.

If the XML is broken, stop and fix that first. Broken XML on XmlToCsv gives a clear error with line and column numbers.

Merge Several Exports and Dedupe by PMID

One search rarely covers a whole topic. You may run one search for each sub-topic, or one for each year range. Some articles will appear in more than one export.

Convert each file with the same mapping, so the columns match. Then join the sheets into one list, and dedupe by PMID.

To dedupe, sort by PMID, then remove rows where the PMID has already appeared. Most sheet tools have a remove duplicates tool. Use it with the PMID column as the key. Why PMID, and not title? Titles can vary in small ways between exports, by a capital letter or extra space. The PMID is a single code, so it is a safer key.

Keep a note of how many rows you had before and after this step. Also keep the source XML files.

Large Result Sets, Streaming, and Splitting

A large search can give you a large XML file. Abstracts add a lot of text, so file size grows fast.

XmlToCsv processes large files with streaming. Streaming means the tool reads the file in small parts, one part at a time. For big sets, big result sets need the streaming approach for large files so the job does not depend on loading the whole file at once.

Plan limits still apply. Free covers files up to 2 MB, Starter up to 25 MB, and Pro up to 100 MB. Free gives 10 conversions per month. Starter is $9 per month. Pro is $19 per month.

Row limits matter too. Free export is up to 5,000 rows. If your set has more articles than that, split the job by year. Export one year range at a time, convert each part, then merge the sheets and dedupe by PMID. If one part fails, you can run that part again on its own.

Before a big job, test with a small set from the same source. Check the columns, the authors, and one abstract in full. If you are on a paid plan, save the mapping as a template from the small test, and use it for each part.

A Step-by-Step Plan

First, run your search on PubMed and download the results as XML. Save the file with a clear name that includes the topic and date.

Second, upload the file to XmlToCsv and check the row element. It should be the article record.

Third, read the preview. Check the total row count, and check that PMID, title, journal, and year are each in their own column.

Fourth, choose how to handle authors. Join them for screening. Expand them only if you need counts by author.

Fifth, drop columns you do not need, and rename the ones you keep.

Sixth, export to XLSX if abstracts matter, or to CSV for a simple list. Open the file and check one abstract in full.

Seventh, if you have more exports, convert each one the same way, merge the sheets, then dedupe by PMID.

Eighth, add your own columns for screening marks and notes, apart from the source columns.

Common Mistakes to Avoid

The first mistake is picking the wrong row element. Always check the row count first.

The second mistake is screening on an expanded sheet. One row per author is for counting. One row per article is for screening.

The third mistake is deduping by title. Use PMID as the key. It is more stable than title text.

The fourth mistake is editing the XML by hand. A small change can break a tag. If the file fails, use the line and column in the error to find the spot, and get a fresh download if you cannot fix it.

The fifth mistake is losing the mapping. If you will run the same search again, save the template on a paid plan.

Final Check Before You Share the Sheet

Check the row count, and check the PMID column for blanks. A blank PMID is a sign that a row may not be a full article record.

Sort by year and scan the list. Does the range match your search? Open one or two papers on PubMed from their PMIDs, and compare the title, authors, and abstract with your sheet.

Then share the sheet with a short note. Say where the XML came from, when you ran the search, and how you deduped the list. A good sheet makes screening clear and easy to track: one row per article, each field in its place.

References

• https://www.ncbi.nlm.nih.gov/books/NBK25501/ — NCBI's Entrez Programming Utilities help. It explains how PubMed data can be fetched as XML with NCBI's tools.
• https://pubmed.ncbi.nlm.nih.gov/ — PubMed itself, from the US National Library of Medicine. It is the search service where the article records come from.