Python Tool: Downloading Qualtrics Survey Data using Python API Calls

[HeatherMHarris]

I am thrilled to post this "one tool wonder" made possible by the new Python Tool in Alteryx Designer 2018.3.

Thank you to @IanCo, our Solutions Consultant, for steering me in this direction.

This post will show you how you can use the new Alteryx Designer Python Tool to implement the Qualtrics Python 3 API calls to download your survey data. (There is a template workflow attached below that includes all of the Qualtrics Python 3 code mentioned in this post for you to customize as follows.)

• Open a new workflow and drag the Python Tool onto your canvas.

• Add the Qualtrics Python 3 code for the API calls just below the Python line of code that says "from ayx import Alteryx". The full code is at the bottom below so that you may cut-and-paste it into your Python Tool. (I found this Python 3 code on the Qualtrics web site: https://api.qualtrics.com/docs/response-exports. Make sure you select the tab for Python 3.)

 

• Modify the following three lines of the Qualtrics Python 3 code for your particular Qualtrics token, survey and data center values.
  • apiToken = "YOUR_API_TOKEN_HERE"
  • surveyId = "YOUR_SURVEY_ID_HERE"
  • datacenter = 'YOUR_DATA_CENTER_ID_HERE"
• Qualtrics provides some help for finding these values on the following web sites:
  • https://api.qualtrics.com/docs/root-url
  • https://api.qualtrics.com/docs/finding-qualtrics-ids

• Modify the next to last line of Qualtrics Python 3 code for the destination path for where you would like to land this data. It is landed in a directory as a CSV file.
• You will need to put in your own file path on this line. (I added the 'r' before the path to allows to enter slashes without having to "escape" them as special characters 
• zipfile.ZipFile(io.BytesIO(requestDownload.content)).extractall(r"YOUR_PATH_HERE")

IMPORTANT: Before you run your workflow, you need to make one change to a file in the Alteryx Designer 2018.3 Python installation.

• Go to the following directory:
  • C:\Program Files\Alteryx\bin\Miniconda3\PythonTool_venv\Lib\site-packages\nbconvert\preprocessors
• Edit the execute.py file
• Change the following line from "30" to "-1" to disable the timeout -- The time to wait (in seconds) for output from executions.
  • timeout = Integer(-1, allow_none=True, ...

• Run your workflow. The returned CSV file will be in the location that you entered above. There is nothing that comes into your workflow canvas. You can then use the Input Tool to read in the CSV file to a workflow.

Enjoy!

Heather Harris

Alaska Airlines

Alteryx ACE

@HeatherMHarris

----- Cut and Paste This Python Code Into the Python Tool -----

----- Enter your Qualtrics API Token, Survey ID and Data Center ID -----

# Python 3

import requests
import zipfile
import json
import io

# Setting user Parameters
apiToken = "<YOUR_TOKEN>"
surveyId = "<YOUR_SURVEY_ID>"
fileFormat = "csv"
dataCenter = '<YOUR_DATA_CENTER_ID>'

# Setting static parameters
requestCheckProgress = 0
progressStatus = "in progress"
baseUrl = "https://{0}.qualtrics.com/API/v3/responseexports/".format(dataCenter)
headers = {
    "content-type": "application/json",
    "x-api-token": apiToken,
    }

# Step 1: Creating Data Export
downloadRequestUrl = baseUrl
downloadRequestPayload = '{"format":"' + fileFormat + '","surveyId":"' + surveyId + '"}'
downloadRequestResponse = requests.request("POST", downloadRequestUrl, data=downloadRequestPayload, headers=headers)
progressId = downloadRequestResponse.json()["result"]["id"]
print(downloadRequestResponse.text)

# Step 2: Checking on Data Export Progress and waiting until export is ready
while requestCheckProgress < 100 and progressStatus is not "complete":
    requestCheckUrl = baseUrl + progressId
    requestCheckResponse = requests.request("GET", requestCheckUrl, headers=headers)
    requestCheckProgress = requestCheckResponse.json()["result"]["percentComplete"]
    print("Download is " + str(requestCheckProgress) + " complete")

# Step 3: Downloading file
requestDownloadUrl = baseUrl + progressId + '/file'
requestDownload = requests.request("GET", requestDownloadUrl, headers=headers, stream=True)

# Step 4: Unzipping the file
zipfile.ZipFile(io.BytesIO(requestDownload.content)).extractall("<YOUR_PATH>/MyQualtricsDownload")
print('Complete')

[gene_denny]

Once I get this all hammered out, I FULLY intend to openly share the code here on the Community for anyone who needs it.  That's the entire premise of our Community and I couldn't be more proud of that!

Now, if I could just convince Alteryx to give us back the free geocoding tool in the Gallery I'd be really happy!  🙂

[jackdaniels]

The python tool is trying to get a key from an environment variable - which isn't present.

to make this skip that particular error you can do the following

```
import sys

apiToken = "apikey"
surveyId = "surveyid"
dataCenter = 'datacenter'

# Setting static parameters
```

The code will run and now its just a matter of retrieving the contents of the zip file

[gene_denny]

Thanks for that.  I think I'm getting closer.  I've attached the new code that incorporates your response as I think it should be.  When I run it now, I get this error:

[jackdaniels]

You need to add the following at the beginning of the script.

import requests

[jackdaniels]

import sys
import requests

apiToken = "{my APIKEY}"
surveyId = "{my surveyID}"
dataCenter = 'co1'

# Setting static parameters




requestCheckProgress = 0.0
progressStatus = "inProgress"
url = "https://{0}.qualtrics.com/API/v3/surveys/{1}/export-responses/".format(dataCenter, surveyId)
headers = {
    "content-type": "application/json",
    "x-api-token": apiToken,
    }

# Step 1: Creating Data Export
data = {
        "format": "csv",
        "seenUnansweredRecode": 2
       }

downloadRequestResponse = requests.request("POST", url, json=data, headers=headers)
print(downloadRequestResponse.json())

try:
    progressId = downloadRequestResponse.json()["result"]["progressId"]
except KeyError:
    print(downloadRequestResponse.json())
    sys.exit(2)
    
isFile = None

# Step 2: Checking on Data Export Progress and waiting until export is ready
while progressStatus != "complete" and progressStatus != "failed" and isFile is None:
    if isFile is None:
       print  ("file not ready")
    else:
       print ("progressStatus=", progressStatus)
    requestCheckUrl = url + progressId
    requestCheckResponse = requests.request("GET", requestCheckUrl, headers=headers)
    try:
       isFile = requestCheckResponse.json()["result"]["fileId"]
    except KeyError:
       1==1
    print(requestCheckResponse.json())
    requestCheckProgress = requestCheckResponse.json()["result"]["percentComplete"]
    print("Download is " + str(requestCheckProgress) + " complete")
    progressStatus = requestCheckResponse.json()["result"]["status"]

#step 2.1: Check for error
if progressStatus is "failed":
    raise Exception("export failed")

fileId = requestCheckResponse.json()["result"]["fileId"]

# Step 3: Downloading file
requestDownloadUrl = url + fileId + '/file'
requestDownload = requests.request("GET", requestDownloadUrl, headers=headers, stream=True)

# Step 4: Unzipping the file
zipfile.ZipFile(io.BytesIO(requestDownload.content)).extractall("MyQualtricsDownload")
print('Complete')

[gene_denny]

First, a shoutout to both @jackdaniels and @HeatherMHarris for contributing to this!  I have finally been able to massage the code into a workable format that is now successfully downloading the data file again via Qualtrics newest API requirements.  I found a few places in the code that was provided by Qualtrics that did not work and, through the help of the aforementioned community members, was able to modify that code so that it actually functions correctly in my environment.  There were a couple of basic syntax issues at the beginning of the Python script and again at the end where the location of the constructed data file is identified for saving.  I have attached the entire code to this post.  Users should be able to copy/paste into the Alteryx Python tool and edit only 4 lines.  Those lines should be easily identifiable.  Be sure to follow the INSERT instructions exactly with regards to which punctuation to delete and which to maintain.  The final line which needs to be edited, where it identifies the local directory to which to save the resulting file, should look similar to this example:

zipfile.ZipFile(io.BytesIO(requestDownload.content)).extractall(r"C:\Users\{username}\Documents\MyQualtricsDownload")
print('Complete')

where {username} is replaced with your local computer login credential and the brackets ({ }) are removed.

In addition, you will need to create this directory PRIOR TO running the completed tool.  In my experience, it does not create the directory for you.  Remember, I knew NOTHING about Python prior to solving this issue and now only enough to make this particular solution viable.  

Best of luck everyone!  The code is attached as a .txt file.

[jackdaniels]

Great work @gene_denny !

[gene_denny]

Could NOT have done this without your input, @jackdaniels!

[jackdaniels]

hey @gene_denny , you may find the following useful.

The following replaces step 5. It reads the csv from the zip and writes to the #1 output anchor without needing to save a file.

It does require pandas, which will need to be installed and imported.

Alteryx.installPackages("pandas")

import requests
import zipfile
import json
import io, os
import sys
import pandas as pd

The new step 5

#Step 5 

surveyzip = zipfile.ZipFile(io.BytesIO(requestDownload.content))

# see what's in the zip (doing this as I am not sure if it is possible to download mutiple surveys)
surveyzip.namelist()

# creates a dataframe / extract and read the csv corresponding to the index, x:
df = pd.read_csv(surveyzip.open(surveyzip.namelist()[0]))

# drop unwanted rows
df = df.drop(df.index[[0,1]])

# output to anchor #1
Alteryx.write(df,1)

[BobSnyder85]

Hi - 

Thanks for this guide! It was very helpful. I would say as a note from a beginner, that "Datacenter" is basically your companies first half of the domain for your qualtrics URL "xxxxxxx.co1.qualtrics.com" and you shouldn't key in the ".qualtrics.com" part as it will be redundant in the code.

Question: How do I save the Python code so the workflow will retain it? Also - This will be run from the alteryx gallery, so I will need the code to be packaged with the workbook.

Context: I configured this code yesterday and got it to output. I saved my workflow yesterday and came back and all my python was gone. 

Thank you in advance!!