Navigate Up
Sign In
Supporters of Developer
Web

How to Use Microsoft Excel to Query a SharePoint List

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.

You may also be interested in: SharePoint Hosting by Fpweb.net

 

Editor's note: Contributor Alexandru Dionisie is an Internet Professional and Technical Writer. Follow him @tutorialeoffice

A great way to extract and expose data from SharePoint is by using Microsoft Excel. Of course, not by doing a simple copy-paste, but by using an Excel query.

Even though Excel’s query editor is not that great looking (like the one from Microsoft Access), it still is functional and very helpful.

I am going to use a query in Excel to extract only a part of a SharePoint list data.

Here are the steps

  • Open the SharePoint list and from the list tab click on the Export to Excel command button.

2012-10-28-ExcelToQuery-01.png

  • Save the Microsoft Excel Web Query file.
  • If you want to see the query file content, open it using Notepad.

2012-10-28-ExcelToQuery-02.png

  • Double click on the Microsoft Excel Web Query file to open it using Microsoft Excel.

A new Excel file opens and displays the SharePoint list data. The biggest disadvantage of that query file is that you can’t edit it and add custom SQL code.

Still, you can create your own query, with a custom SQL code. As you can see in the bellow image, that workbook is connected to the SharePoint list through that Microsoft Excel Web Query file.

2012-10-28-ExcelToQuery-03.png

Save the above Excel file and then open a blank workbook. From the Data tab click on the From Other Sources button and then click on the From Microsoft Query option.

2012-10-28-ExcelToQuery-04.png

  • make sure that the Use the Query wizard to create/edit queries is unchecked.
  • click on the New Data Source option (form 1) and then click on the OK button.
  • in form 2:
    • add a name for the new source (step 1);
    • select the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) - step 2
    • click on the Connect command button (step 3);
  • in form 3 click on the Select Workbook command button;
  • in form 4 select your workbook.

2012-10-28-ExcelToQuery-05.png

Returning to form 2, we have to choose a default table (or sheet) – step 4.

2012-10-28-ExcelToQuery-06.png

Now we have a new source. To add a custom SQL code, just click on the OK command button.

2012-10-28-ExcelToQuery-07.png

In the query editor I will enable the Criteria pane (from the View menu) because I want to see certain products that have a custom price.

More than that I want to see only some column: Categorie, Produs and Pret.

Criteria:

  • Product category (Categorie = LCD 22);
  • Custom Price (Pret >=400)

2012-10-28-ExcelToQuery-08.png

If I want to see what the SQL code looks like, I will click on the SQL button from the toolbar.

2012-10-28-ExcelToQuery-09.png

To add the query result in a sheet, just close the query editor.

Now, the Import Data form is displayed.

Choose a cell to put the data in and the click the Properties command button.

2012-10-28-ExcelToQuery-10.png

In this form we have to check the first and the third option, so that the data updates in the background and when the file open.

2012-10-28-ExcelToQuery-11.png

After we confirm all the options, the data will be imported in the sheet. Now we can create custom reports and charts, based on this data.

2012-10-28-ExcelToQuery-12.png

If you want others to use this method, just export the query from the query editor. Then, all they have to do is to double click on the query and the data is imported.

To edit the query you can use Notepad or the query editor.

2012-10-28-ExcelToQuery-13.png

After some tests, it seems that the data is not refreshing.

Why ? Because the first Excel workbook (the one created after running the Web Query) wasn’t updated either. So, update the first workbook and then the second one.

To avoid the above procedure, in the first workbook (created by the Web Query) we must export the second query and import it into the first workbook.

Now, we have only one workbook that contains the Web Query and the ODBC Query.

2012-10-28-ExcelToQuery-14.png

Categories: dev; Office 365; Excel Services; SQL; MOSS; WSS; 2007; 2010; Libraries and Lists

Comments

Gerson

Great Solution!

I've been searching for a method on how to do this and you've helped me greatly. It seems as it should be more native to the application, but your walk-through worked for me and I'm grateful for the write-up. I'll keep an eye our for other articles that you write!

Posted 02-Jan-2013 by Gerson
Alan Grieve

Problem with article

What does this actually mean? After some tests, it seems that the data is not refreshing. "Why ? Because the first Excel workbook (the one created after running the Web Query) wasn’t updated either. So, update the first workbook and then the second one. To avoid the above procedure, in the first workbook (created by the Web Query) we must export the second query and import it into the first workbook. Now, we have only one workbook that contains the Web Query and the ODBC Query." I was going along nicely until I came to that point.

Posted 14-Jan-2013 by Alan Grieve
Dejan

Another way

Would you achieve the same thing by creating view on SP list and then export that view in excel? Thanks for the article, it was big help for me

Posted 30-May-2013 by Dejan
mightystudents

thankful

I'll keep an eye our for other content that you write! upgrade the first book and then the second one. To prevent the above process, in the first book I've been looking for a technique on how to do this and you've assisted me significantly. It seems as it should be more indigenous to the program, but your walk-through proved helpful for me and I'm thankful for the write-up.

Posted 24-Jun-2013 by mightystudents
cyberpine

Nice

Possible to use this join multiple List sources. Possible to build functions that return rows or can be filtered or joined on like in SQL?

Posted 03-Jul-2013 by cyberpine
jumana

viewing different columns each day

Hi i wanted to know if i can export from excel to sharepoint a file and each day i can view at the sharepoin specific columns ? for example, i have an excel file that had A--Z columns at Sunday i want to view A--D at Monday i want to view E--G etc... can i set an automation to do that?

Posted 29-Jul-2013 by jumana
trancoc

How to display image

I connect excel to sharepoint list, but picture field is picture path. How to display picture in excel ?

Posted 20-Sep-2013 by trancoc
Roger

Changing the View ID to retrieve data from another View

This article was very helpful. Instead of using a second Workbook I changed the first Query to retrieve data from another View of the list by changing the ID of the View referred to in the Query. This variable look like this in my Query file: _vti_bin_SharePointListView={CEEDDF8D-F7E5-4499-A660-654AED12229D} I found the ID in the *.aspx file of the View using Sharepoint designer.

Posted 22-Sep-2013 by Roger
dissertation-writing-help.org

Great Essay

That was truly surprising for a student, you created your piece well and you showed a mess of insurances. Much gratitude to you for offering this fantastic post here.

Posted 24-Oct-2013 by dissertation-writing-help.org
akin

I think the way you

I think the way you write feels like having so much passion and you have put it in a way that everyone can understand. Must appreciate the great posts you have written here sky customer service

Posted 05-Jan-2014 by akin
kainio

A good point Ralph,

A good point Ralph, but I believe people are going to have a hard time defending prejudice when the possibility of answering certain kinds of questions with much greater precision increases. ee customer services number

Posted 07-Jan-2014 by kainio
rajakala

Mr......

This is the very good idea! Just want to say thank you for information, Just continue to the write such a position. I will be your faithful reader. Thank you again. ketones side effects

Posted 28-Jan-2014 by rajakala
rajakala

I'm sure

I'm sure there will be more delighted opportunities, It is definitely worthed to appreciate your high quality writing. Your blog post will be very beneficial for people who seeks about this area cambogia results

Posted 28-Jan-2014 by rajakala
kata kata mutiara

halus

I think the way you write feels like having so much passion and you have put it in a way that everyone can understand. Must appreciate the great posts you have written here cara agar cepat hamil,kata kata lucu, kata kata mutiara,kata kata romantis kata kata indah,hamil

Posted 09-Mar-2014 by kata kata mutiara
Elizabeth Fredrick

Elizabeth Fredrick

A reliable blog always comes-up with new and exciting information and while reading I have experience that this blog Elizabeth Fredrick

Posted 24-Mar-2014 by Elizabeth Fredrick
Eve Housman

Eve Housman

That's pretty powerful stuff. I like the way your logic follows, and how you're able to put it into words with such a powerful post! I am enthralled. Eve Housman

Posted 24-Mar-2014 by Eve Housman
James Kelly

James Kelly

I was very encouraged to find this site. I wanted to thank you for this special read. I definitely savored every little bit of it and I have you bookmarked to check out new stuff you post. James Kelly

Posted 24-Mar-2014 by James Kelly
business schools

business schools

Great weblog here! Also your site quite a bit up fast! What web host are you the usage of? Can I get your associate link on your host? I want my site loaded up as quickly as yours lol business schools

Posted 24-Mar-2014 by business schools
Clyde Picard

Clyde Picard

Part of the Balearic archipelago which also has the islands of Minorca and Ibiza among its components, yacht charter Mallorca is by far the largest and the most popular, because of its fine weather, the beaches and recently, the infrastructure. Clyde Picard

Posted 24-Mar-2014 by Clyde Picard
atlanta web designer

A good point Ralph

A good point Ralph, but I believe people are going to have a hard time defending prejudice when the possibility of answering certain kinds of questions with much greater precision increases. atlanta web designer

Posted 24-Mar-2014 by atlanta web designer
James Scott

James Scott

This is one of the best blog because, there are lot of informative postings. James Scott

Posted 24-Mar-2014 by James Scott
Tosha Friedman

Tosha Friedman

crimes against humanity and would in no manner support any international structure limiting its’ ability for practicing war and peace against any other state and/or party it considered a threat to its national interest. Tosha Friedman

Posted 24-Mar-2014 by Tosha Friedman
Timothy Flemming

Timothy Flemming

This is quite wonderful post. The article affects a lot of urgent challenges of our society. We can not be indifferent to these challenges. Your post gives the light in which we can observe our real life. Very professional. Timothy Flemming

Posted 24-Mar-2014 by Timothy Flemming
web development consulting

web development consulting

This is just what I'm looking for, I was actually going through the nice helpful tips from the blog and I put respect to the writer of this post, enjoying the smart way you write articles in this web development consulting

Posted 24-Mar-2014 by web development consulting
is bubblegum casting legitimate

is bubblegum casting legitimate

A good point Ralph, but I believe people are going to have a hard time defending prejudice when the possibility of answering certain kinds of questions with much greater precision increases

Posted 01-Apr-2014 by is bubblegum casting legitimate
yitoyitoytyo

yitoyitoytyo

La otra novedad de este año Replicas Relojes es la versión bicolor en rojo oro 18 k y acero inoxidable. Este reloj está Rolex Replica montado sobre una correa de cuero marrón. Los deportes bicolor Aqua Terra day-Date una esfera gris vienen con Tag heuer carrera agujas y el índice en oro rojo blanco de 18 quilates cubiertos de Superluminova.

Posted 17-Apr-2014 by yitoyitoytyo

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: