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.
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.
- Save the Microsoft Excel Web Query file.
- If you want to see the query file content, open it using Notepad.
- 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.
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.
- 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.
Returning to
form 2, we have to choose a default table (or sheet) –
step 4.
Now we have a new source. To add a custom SQL code, just click on the
OK command button.
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)
If I want to see what the SQL code looks like, I will click on the
SQL button from the
toolbar.
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.
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.
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.
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.
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.
Author :Alexandru Dionisie
|
