Udostępnij za pośrednictwem

Consume csv with Data Explorer

  • Artykuł

"Data Explorer"  preview for Excel 2013 is now available for download here. "Data Explorer" add-in is an easy to use query-by-example tool that connects to a wide variety of datasources. There are several blog posts and documentation that  talks about the benefits and features of Data Explorer, some of them are here, here and here. 

In this blog post I would take Seattle's neighborhood data from data.gov and land it in excel. It demonstrates the use of following features in Data Explorer

  • Consuming a csv file.
  • Hiding columns.
  • Text Filtering.

"seattle.data.gov"  has data about Seattle's beaches, parks, viewpoints and much more. You can look at the data here. There is an "Export" option that lets us download the file as csv, json etc.

I have download the data in csv format. Direct link for download is here. 

You can download the completed Excel sheet with Data Explorer query from codeplex here. (If you are interested in getting all the files as a Git repository, you can do that as well)

Below are complete instructions and screenshots for creating this Data Explorer query yourself.

 

 

Step1:- After you have installed "Data Explorer" add-in for Excel, click on "From File -> From CSV" link from the "Get External Data" ribbon. Below is a screenshot.n.

Provide csv file's location in the dialog that pops up. Now you would get DataExplorer editor window that lets you "Filter&Shape" the data to the format you want.

DataExplorer splits the columns appropriately and provides us the following view. 

 

Step2:- It has some columns that I don't intend to use. I would like to hide those, to do that I select the ones that I want to hide while holding Ctrl key, right-click and select "Hide Columns". Roughly the operations that you can do on a columns are all available in the context menu.

Here is how the "hide columns" screen would look like

 

 

 

 Step3:- Now all is left is to filter the table on "City Feature" column. I am only interested in places of tourist interest, by looking at various of "City Features" available, I figure I would have to filter and only take rows that have "General Attractions" or "Viewpoints" as City Feature.

I do this filtering by clicking on the drop down button in column header, below is a screen shot that does this.I choose "Text Filter" option instead of just using the check box because, the list does not contain all the unique items in "City Feature" . Data Explorer does not load the complete file in memory, and hence wouldn't know about all the unique "City Feature" values. 

Next I type in the value of "City Features" that I am interested in. Below is how the screen looks like

 

 

Step4:- That's it! Click "Done".

Data Explorer would now do the complete evaluation and land the final data in Excel. 

Have Fun!