How to Convert API Results: JSON to Excel or CSV

There are thousands of APIs, tools used by developers to connect data from one service to another. Many APIs have data that could be useful in everyday situations, and you don’t necessarily have to be a programmer to connect to the data. However, it can be helpful to have it in a format that is familiar and easy to use.

The trusty spreadsheet—whether it’s Excel, Google Sheets, Numbers, or any tool that can accept comma separated values (CSV) files—is the data tool of the non-programmer. We’ve grown accustomed to viewing our data as columns and rows. Most APIs produce a different data format, called JSON (or XML). It’s full of curly braces and doesn’t look very user-friendly. However, you can convert JSON to CSV or Excel to get the data in a more familiar format.

Find Your Data Source

Before you can convert your JSON, you need to have the data. Perhaps you already know where you’ll get your data, but if you’ve never used an API before, that can be intimidating. Once you know what to look for, though, accessing many APIs is as intuitive as loading a webpage.

First, to discover the API, there are a couple of approaches that work well once you know who has the data you want:

  • Look in the footer or header of the website for the words “API,” “developer,” “partners,” or “integrations”
  • Google it by searching for “website name API”
  • Look in a directory like ProgrammableWeb.

Next, you’ll need to navigate the API documentation. The best APIs will have a “getting started” guide or similar tutorial for first-timers. It can still be intimidating, but you’re looking for descriptions of the type of data you want. For example, if you use a CRM and you’re wanting to get your contacts via API, look for sections of the documentation that reference “getting contacts” or “listing contacts.”

For our Halloween celebration map of events and our holiday light displays map, we went to Eventful and found “Developer API” in its footer. Within its API documentation, we saw an endpoint (which is like a web address URL) for /events/search. Using their examples, we could put together the API call right in the web browser:

When you replace “SECRETKEY” with your API key, that returns a bunch of text. It’s in JSON format, but if you don’t know what to look for, it might not seem very useful.

Understand the JSON Results

Much of the time, JSON is returned as a giant wall of text, which is one reason it looks incredibly confusing to non-programmers (and many programmers can’t make much sense of it, either). One quick trick to make JSON more readable is to either “pretty print” it or get an extension such as JSONView for Chrome that automatically displays JSON with colors and indentation to make it easier to understand the data within.

Take the example above. It still may be daunting, but hopefully, some of it looks familiar. For example, can you tell the total number of results? Almost 3,000—as referenced by the total_items.

Most JSON data is stored as key/value pairs. That is, there is a way to reference a value (total_items in our example) and the value itself (2869). All related key/value pairs are stored within a single object, as denoted by the curly brackets { and }. Each pair is separated by a comma. There can be objects within objects by using more curly brackets as the value.

In addition, there’s one more important type of value, highly relevant to converting from JSON to CSV: an array or list. This is many values in a row, often many objects in a row. A list occurs between two sets of square brackets, [ and ].

Our example shows a primary object defined by the { on the first line, followed by a few key/value pairs, then e key called “events” whose value is an object. Within that object, there is a single key, “event” which includes a list value (the [ shows us this is a list) and the values within the list are yet more objects. There’s a lot to unpack there and understand, and it might be helpful to see the full JSON file displayed using JSONView.

Even if it’s confusing, the JSON will always have a structure you can figure out using these basic building blocks: objects with key/value pairs, where values can be additional objects or lists of values.

Convert JSON to Excel or CSV

Once you understand the type of data that can be converted, it’s time to make your JSON data usable in Excel. That means you need to convert the JSON either directly to an Excel document, or more likely to a text document that Excel can read, such as CSV.

Let’s look back at our Eventful data again. We need to extract the list of events, which are a series of objects. The key/value pairs in all of the event objects will all contain the same keys. The values will obviously be different since they describe individual events.

Therefore, in spreadsheet terms: the keys become the header row.

You can copy all the key/values, or just the ones you want. For example, we can see the “latitude” key in the screenshot from the previous section. That is a useful value for making a map!

If there’s only a small amount of data, you could make quick progress by copying and then pasting your data from JSON to a spreadsheet. However, most of the time there’s a lot of data. In our Eventful example, there were almost 3,000 events! Use a tool like this to convert file formats automatically.

Optional: Create a Map with Your Data

Many APIs and data sources include location data, such as addresses, city names, or latitude/longitude coordinates. Once your data is in spreadsheet format (as a CSV, Excel, Google Sheets, and more) you can easily create a map like this:

View 2018 Tour de France Route in a full screen map

Create a latitude and longitude map by copy-pasting your spreadsheet data in the cases where you have geographic coordinates. Otherwise, you can automatically geocode location names using our Google Map creator.

Holiday Light Displays Around the Snow Globe

December brings Hanukkah on the 25th day of Kislev, Christmas on the 25th, Kwanzaa on the 26th, and many more holidays throughout the month. However, there is one holiday tradition everyone can enjoy all month long: dazzling holiday light displays! Whether you prefer to take in your neighborhood’s holiday lights while walking hand in hand with your kids, or it’s more your style to drive down well-lit streets listening to your favorite holiday hits as your breath fogs up the windows, you can easily find the neighborhood nearest you by browsing our map of holiday light displays around the snow globe.

In addition to finding the neighborhoods decking their halls and homes this month, discover which countries other than the U.S. light up in December, the most outrageous neighborhoods of 2017, and which states spend the most money on staying lit this time of year.

View Holiday Light Displays in a full screen map

Browse the map to see pretty pics of holiday light displays and then read on for the places that are celebrating the most during this wonderful time of year.

Eventful of Holiday Cheer

Much like our piece on Halloween events, which showed where in the world the spooky holiday was celebrated, this holiday light displays map was made in part due to Eventful — the online calendar and events discovery service. From December 1st all the way through the 31st, Eventful notes holiday light displays occurring worldwide. We made an API call to round up all the data and then converted the results into a familiar format: the spreadsheet. This step-by-step guide walks you through exactly how to convert API results and then make a map.

Our map shows that the states with the most holiday cheer are California, Texas, and New York, which isn’t too surprising as these three states have some of the highest populations in the U.S. At the time we checked Eventful, Californians had already planned 65 holiday light-themed events, Texas had 44 spectacularly lit options, and the Empire State had 38 holiday light display events. Unfortunately for residents of North Dakota, Vermont, and Wyoming, at the time we checked, there weren’t any holiday light events posted. These three states are pretty low population-wise, but even smaller populated states deserve to celebrate. That’s why we also added 2017’s most outrageously lit neighborhoods, so on our map, each of those states have one event going on.

Other Countries That Are Lighting It Up

Photo by Tim Mossholder on Unsplash

Many countries other than the U.S. are home to upcoming holiday light displays. While not on this map, countries like the United Kingdom are playing host to 202 light-related activities throughout December. Canada is also bringing their holiday spirit with 65 events, and Australia and New Zealand and holding 36 and 35 light-viewing parties, respectively. Ireland will also be well-lit this season with 23 events occurring, and Malaysia and the Philippines will each have five holiday light events. Germany is also planning on hosting two events, while Finland, France, Hungary, Isle of Man, Singapore, and Spain all have one event taking place this December.

While taking note of which U.S. states and other countries are bringing out the big bulbs is cool, our map contains more than Eventful’s holiday light display results around the world. We also mapped the United States’ most outrageously lit neighborhoods of 2017 and the cost of electricity to keep these neighborhoods in the holiday spirit.

2017’s Best & Brightest Lights

Curious about the absolute best holiday light displays from 2017? Use our map grouping feature to sort the map by “Event.” Then select “2017 Best Neighborhood By State” to see the neighborhoods 24/7 Wall St. identified as the most outrageously lit. Now, we can’t be sure that these winning neighborhoods will continue to deck their halls and homes in the years to come. However, if you happen to live in the vicinity of one of these neighborhoods, you could always stop on by to see if they are continuing with the enjoyable spectacle that is their holiday lights. Keep on reading to discover how to check if one of 2017’s best and brightest neighborhoods is located near you.

How Yule Find The Closest Lights

It’s simple to find out how far — or close! — one of the winning neighborhoods or other 621 holiday light events is to your location. First, filter the map by the type of “Event” you want to check out. If you’re not picky, you can just skip this step. Then type your address into the search bar of the map, and we’ll show you which event is closest to you!

You can even measure the distance in miles or kilometers between where you reside and the closest holiday light display to you. Click on the measuring tape in the top left corner of the map — part of Advanced Mode available with BatchGeo Pro, — and select the option that looks like a mini ruler. Then, drag a line from your location to the nearest event marker to see just how close it is! To toggle back and forth between miles and kilometers, click on the line at the bottom right-hand side of the map.

Oh Electricity, Oh Electricity…

24/7 Wall St. also included each state’s average monthly residential electric bill in their outrageous light displays research. The following ten states or districts have the highest average monthly electricity bills:

  1. South Carolina — $146.09
  2. Alabama — $145.55
  3. Connecticut — $142.19
  4. Maryland — $141.53
  5. Hawaii — $138.73
  6. Washington D.C. — $131.90
  7. Georgia — $130.87
  8. Tennessee — $128.89
  9. Virginia — $127.14
  10. Texas — $127.10

The average cost of lighting up a home for the holidays each month is $23.33 for incandescent lights and $2.67 for LED lights. So for those holiday light fans in these top electricity-consuming states, using LED lights may help to keep electricity costs down during this time of year.

Now is the time to grab some hot cocoa and wander around your city armed with a map of holiday light displays. You can even make your own map like this one using Eventful and BatchGeo. New Year’s Eve parties, anyone?

The 200 Highest Summits in the U.S.

Record-breakingly high mountain summits, which are the highest points of a mountain, are abundant in the United States — if you know where to look. In fact, the U.S. is home to over 200 mind-bogglingly high summits, including the highest in all of North America: Denali, located in Alaska. The locations — or should we say location — of the top ten highest summits may surprise you, as will the states with nearly identical massive summits that seem to be in really steep competition with each other.

View Highest U.S. Summits in a full screen map

To find out if the rest of the super-high mountain summits are condensed in a select few states or scattered across the country for all to see, check out the map above or read on.

To Summit Up Nicely: Ten Highest Summits

As we list off the ten very highest major summits in the U.S., you may start to see a pattern.

Rank Mountain Peak Mountain Range State Elevation (feet)
1 Denali Alaska Range Alaska 20,310
2 Mount Saint Elias Saint Elias Mountains Alaska 18,009
3 Mount Foraker Alaska Range Alaska 17,400
4 Mount Bona Saint Elias Mountains Alaska 16,550
5 Mount Blackburn Wrangell Mountains Alaska 16,390
6 Mount Sanford Wrangell Mountains Alaska 16,237
7 Mount Fairweather Saint Elias Mountains Alaska 15,325
8 Mount Hubbard Saint Elias Mountains Alaska 14,951
9 Mount Bear Saint Elias Mountains Alaska 14,831
10 Mount Hunter Alaska Range Alaska 14,573

That’s right, Alaska is home to every single one of the top ten highest summits in the U.S. Though the top ten all call The Last Frontier home, they are scattered throughout three different mountain ranges. The Alaska Range is home to 15 major summits in total. Three summits from the Alaska Range made the top ten list, including the very highest major summit not only in the U.S. but in all of North America: Denali.

Photo of Denali by Nic McPhee

While the Alaska Range deserves bragging rights for its monster of a mountain Denali, the Saint Elias Mountain Range is home to 24 insanely high summits. The second of the three mountain ranges to take up real estate in the top ten list, the Saint Elias Mountain Range has the most major summits of any mountain range across the U.S.

Topping off the top ten is the Wrangell Mountain Range, where two of the ten highest summits in the U.S. reside. The Wrangell Range also has five other summits which made it onto the map, though they weren’t tall enough to make it to the top ten list. In total, Alaska is home to 49 out of the 200 highest major summits in the U.S.

State by State Summits

Despite the top ten highest summits list being utterly dominated by Alaska, and Alaska having almost 25% of the country’s major summits, Alaska isn’t home to the most major summits. That would be Colorado. Colorado is home to 88 summits, which is nearly half — 44% — of all of the highest summits in the U.S. The highest summit in Colorado is Mount Elbert, which has an elevation of 14,400 feet and ranks #14. Although, if we stuck Mount Elbert side by side with Denali, which rises to 20,310 feet, Elbert would seem pretty puny, comparatively. Colorado’s highest summits range from Mount Elbert to the aptly named Little Cone, at 11,988 feet.

The home of the second-most major summits, of course, is Alaska, with 49 huge summits, or almost 25% of the map. Next up is California with its 22 high summits, the highest being Mount Whitney of the Sierra Nevada Mountain Range. Mount Whitney stands 14,505 feet tall and is the first non-Alaskan summit to appear on the list at #11. Wyoming closes out the double-digit states with 14 summits.

As for the single-digit summit states, you can visit eight major summits in New Mexico, while you’ll find five in Utah. Nevada is home to the smallest summit on the map, #200 or Charleston Peak (11,916 feet), and has three additional summits, whereas, in Montana, you can see three major summits. If you’re lucky enough to be near the Washington area, be sure to check out its two high summits as they tend to make a big splash. Washington’s Mount Rainier, which is 14,417 feet, is in the top 20 highest major summits in the U.S. Plus, the Evergreen State’s second summit, Mount Adams, is actually a volcano! It can be sighted when hiking the Pacific Crest Trail. Though Mount Adams hasn’t erupted in more than 1,000 years, it’s not considered extinct yet, so watch out if your bucket list consists of hiking all of the highest major summits in the United States.

Photo of Mount Adams by Alex Butterfield

Twin Towers: Summits with the Same Height

If you group the map by rank, you can see that several of the summits share the same rank. These twin towers have the exact same elevation, so they’re tied. Check out the eight highest summits in the U.S. with the same height:

  • Wheeler Peak & Cloud Peak — #98
  • Lone Cone & Castle Mountain — #132
  • Hess Mountain & Mount Brooks — #193
  • Lituya Mountain & Haydon Peak — #198

Wheeler Peak, located in the Taos Mountains of New Mexico, and Cloud Peak in the Bighorn Mountains of Wyoming are tied for the 98th highest summit in the United States. These two summits are both 13,167 feet tall. As for the second pair of peaks, Lone Cone is not so alone. Colorado’s smallest summit, located in the San Miguel Mountains, has the same elevation as Castle Mountain in Montana’s Absaroka Mountain Range. Both Lone Cone and Castle Mountain have elevations of 12,618 feet, putting both of them at #132.

The summit creators certainly struggled to make up their minds when creating Alaska’s Hess Mountain and Mount Brooks. These two Alaskan summits have an elevation of exactly 11,940 feet, are both located in the Alaska Mountain Range, and are both in 193rd place. Finally, Lituya Mountain and Haydon Peak — also both in Alaska and both in the Saint Elias Mountain Range — share the same 11,924 feet elevation, tying them for #198.

The sky is the limit for the highest major U.S. summits and for all of the maps you can make with BatchGeo. Make your own map of other U.S. landmarks you think are cool, or check out some “map-spiration” from maps like the landmarks and milestones along the Appalachian Trail, locations along Route 66 you can still find, or the world’s largest map of the world’s largest roadside attractions.