FAQ: Exporting Data

One of the most frequent questions we get is: The players names and team names are garbled with HTML when I export data. How can I fix this?

Simple Tag Removal:

To remove any HTML tags from the data all you need to do is a quick Find & Replace All.

Fill in “<*>” (no quotes) into the Find what box, and then leave the Replace with box blank and hit Replace All.

And that’s it. You now have a worksheet completely free of all HTML.

More Advanced Parsing:

But since we’re leaving the HTML in, there are some neat tricks you can do if you want to preserve the playerid.

Step 1: Highlight the entire A column and do these replace all’s in sequence, replacing them with nothing (no quotes):

“<a href=”statss.aspx?playerid=”

Step 2: Add an additional column next to first column.

Step 3: Finally you’ll need to run the Text to Columns wizard, which you can access by holding alt + d + e in that order or clicking on it under the data menu. Select the delimiter Other, use the & symbol, hit finish and you’ll have a properly formatted worksheet with playerids preserved.

Using similar techniques, you can preserve teamids and player positions. If you find yourself doing more involved parsing frequently, it’s also possible to record the steps taken as a macro for a one click solution.

Print This Post

David Appelman is the creator of FanGraphs.

14 Responses to “FAQ: Exporting Data”

You can follow any responses to this entry through the RSS 2.0 feed.
  1. Jacob Smith says:

    Great post, David. Thank you.

    Vote -1 Vote +1

  2. Yirmiyahu says:

    Thanks for the suggestion on parsing out the player ID number. Helps to keep same-named players separate, and makes it easier to work with if you’re trying to match up the players in two overlapping sets of data.

    Random thing that would be helpful with fangraphs’ data: an “active player” checkbox.

    Vote -1 Vote +1

    • We’re looking into doing something with “Active” players. Or at least players on the 40 man roster in the off-season / 25 man when those kick in.

      With the custom leaderboards, hopefully there is not too much of a reason to ever try and match up multiple data sets in Excel, unless you’re trying to do something with splits, or across batters / pitchers/ fielders.

      Vote -1 Vote +1

      • Yirmiyahu says:

        The custom leaderboards are great. You still need to match up different data sets if you want to look at two different seasons (pitchers who pitched X number of innings in both 2010 and 2011), or if you’re using splits data (home/away or left/right).

        Vote -1 Vote +1

  3. I just do a text to columns with the “” symbols. Then I just delete the columns that have the gobbledygook. It’s an extra step, but having access to the custom leaderboard is worth the extra five seconds.

    Vote -1 Vote +1

  4. Anon says:

    Off Topic.
    Can you add a Thumb Up and Thumb Down option for articles (like exists for comments)?

    Vote -1 Vote +1

  5. byron says:

    I’d been using LEFT(), RIGHT(), and MID() (in conjunction with FIND() and LEN()) to accomplish the same thing. This definitely might be easier for your guys data, but I think you’re going to need to be familiar with those tools when you run into Lastname, Firstname lists.

    Vote -1 Vote +1

  6. Perceptron says:

    I’m sure there is a very valid reason that I am just missing, but why insist on exporting data with htmls in the first place? It can’t be that hard to just have it export the playerid, teamid, and position, can it?

    Vote -1 Vote +1

    • xeifrank says:

      I agree. Or at least have the same question. Why? Why do your readers need to jump through so many hoops. Take the HTML out. I have a couple of C and VB utilities that I wrote to do this.

      Vote -1 Vote +1

  7. Barkey Walker says:

    What is the license on exported data? I’m a little surprised your data providers let you do this.

    Vote -1 Vote +1

  8. peachesnnuts says:

    Off topic but the splits pages for players are much more detailed than the league stats pages. Is there a way to either export data directly from the individual splits page for a set of players or since they are already being uploaded into the site change it so the data also loads into the league stats pages.

    Specifically I want pitcher 2B and 3B allowed for a wOBa expirement.

    Thanks for this too Dave, I’ve wondered how to counter this.

    Vote -1 Vote +1

  9. Carlcrawfordisawesome says:

    I have a macro and it makes it so easy. Just record a macro. I did position too.

    Vote -1 Vote +1

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>