Set up a connection in Excel

With a Power BI/Tableau connection (OData feed) set up in 2ask, you can also import the survey results into Microsoft Excel.

Prerequisite: The feed URL and password must already have been set up in 2ask (see Power BI / Tableau (OData feed) – Creating a connection in 2ask). The feed cannot be accessed without a generated password.

Note on authentication: 2ask uses the ‘Standard’ option in Excel for OData access. Leave the username field blank; enter the password generated in 2ask as the password.

Step 1: Open Excel

Launch Microsoft Excel on your computer. These instructions apply to the current desktop version; menu labels may vary slightly depending on the language and version.

Step 2: Retrieve data

Switch to the ‘Data’ tab and click on ‘Get data’ at the far left. From the drop-down menu, select ‘From other sources’ and then ‘From OData data feed’.

Step 3: Paste the feed URL from 2ask

In the OData Feed dialogue box, paste the full feed URL from the 2ask connection (using the ‘Copy feed URL’ button). Ensure that the URL is complete and contains no spaces. Click OK to confirm.

Step 4: Set the authentication method

For authentication, select ‘Standard’ (or ‘Basic’ / ‘Username and password’, depending on the version).

Leave the username field blank. Enter the password displayed once in 2ask as your password.

Important: In the drop-down menu for the level at which this setting is saved, please select the bottom-most option (point 3 in the screenshot; the exact label may vary)

Confirm by clicking ‘Connect ’ or ‘Log in ’.

Step 5: Select and load tables

In the Navigator, you can select the tables you wish to import:

  • Responses – actual participants
  • PretestResponses – participants from pre-tests or the preview function
  • ValueLabels – coding scheme with labels for the codes

Select‘Load ’ or ‘Transform data ’.

Troubleshooting

Error Solution
Unable to retrieve feed

Has a password been generated? Has it been entered correctly? Is the full feed URL being used?

Login fails Use ‘Standard’ authentication; in Power BI, leave the username field blank and enter the 2ask password.
Expected responses are missing

Check the data scope in 2ask: completed or uncompleted questionnaires, as well as coded or uncoded output.

Password is no longer known

Open the connection in 2ask and – if necessary – generate a new password. Then update the saved login details in the BI tool.

Do you find this article helpful?