Microsoft Excel integration¶
The OpenDataSoft OData service makes it possible to gather data directly from Microsoft Excel, without any programming knowledge. There are 3 ways to proceed: first, for Microsoft Excel 2013, the OData native integration can be used. For Excel 2010 and Excel 2013, PowerPivot and PowerQuery extensions can be used. This article will give a detailed explanation on how to do it.
Here are the steps that will allow you to import data through the Excel native OData integration.
- To import data from OpenDataSoft OData service, open Microsoft Excel and click the “DATA” tab. Once done, click “From Other Source”. This should let you see the list of supported data sources. Select “From OData Feed”.
- In the address bar of the window that was opened, enter the service address. This address should be “http://platform.url/api/odata”. Replace the platform.URL by the actual platform URL.
- Select the table(s) of your choosing by clicking their checkbox.
- After validation, the “Import Data” window will open. Click “OK”.
- Your data are now displayed in the Microsoft Excel cells.
Here are the steps that will allow you to import data through PowerPivot for Microsoft Excel.
- Before you can use PowerPivot, you must activate it. To do so, open the “Add-ins” tab in the Microsoft Excel settings window. In the “Manage” drop down menu, select “COM Add-ins” and click “Go...”.
- In the window that was just opened, select “Microsoft PowerPivot for Excel 2013” or “Microsoft PowerPivot for Excel 2010”, whichever applies.
- Back in Microsoft Excel main window click the “POWERPIVOT” tab to display the PowerPivot menu bar. In it, click the “Manage” button. It will open a source management window.
- In this window, click “Get External Data”, and in the menu that appears, click “From Data Service”. In the second menu that appears, click “From OData Data Feed”
- In the import window that was opened, enter the OpenDataSoft OData service in the address bar.
- In the table selection window, select the dataset(s) of your choosing by clicking their checkbox. Once done, click “Finish”.
- When the transfer window shows a success indicator, close it with the “Close” button on the bottom right.
- You can now verify the integrity of your data om the data source management window.
- In the Microsoft Excel main window, click “PivotTable”, and in the popped up list, click “PivotTable”.
- In the destination selection window, select the worksheet of your choosing, and click “OK”.
Here are the steps that will allow you to import data through Power Query for Microsoft Excel.
- Before you can use Power Query, you must download and activate it. To download it, visit this link. The activation process is similar to that of PowerPivot.
- Once done, return to Microsoft Excel main window, click “POWER QUERY” to display its menu bar. In the “POWER QUERY” menu bar, click “From Other Sources”. You should see a list with all data sources supported by Microsoft Power Query.
- In this window, enter the OpenDataSoft OData service URL and click “OK”.
- Once done, a Power Query navigation window will be displayed on the right hand side of the main window. In it, click the IDs of the table of your choosing.
- Your data is now displayed in the Microsoft Excel cells.