How to get Qualtrics V3 API to Work with Power BI

RCBIZMarketingRCBIZMarketing VirginiaCommunity Member Qubie ✭
edited May 2019 in Qualtrics API

How to get Qualtrics V3 API to Work with Power BI

Since there is no official documentation on how to do this and I'm not familiar with Python, I'm pretty confused on how to do this. I was given this code by a support person but it's not working:

Python 3

 
import requests
import json
import io, os
import sys
import pandas
from pandas.io.json import json_normalize
 

Setting user Parameters

 
apiToken = '[ID]'
surveyId = "[ID]"
fileFormat = "csv"
dataCenter = ‘[Datacenter]’
 

Setting static parameters

requestCheckProgress = 0.0
progressStatus = "inProgress"
baseUrl = "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

downloadRequestUrl = baseUrl
downloadRequestPayload = {"format": fileFormat, "compress": False, "useLabels": True}
downloadRequestResponse = requests.request("POST", downloadRequestUrl, json=downloadRequestPayload, headers=headers)
progressId = downloadRequestResponse.json()["result"]["progressId"]
print(downloadRequestResponse.text)
 

Step 2: Checking on Data Export Progress and waiting until export is ready

while progressStatus != "complete" and progressStatus != "failed":
    print ("progressStatus=", progressStatus)
    requestCheckUrl = baseUrl + progressId
    requestCheckResponse = requests.request("GET", requestCheckUrl, headers=headers)
    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 = baseUrl + fileId + '/file'
requestDownload = requests.request("GET", requestDownloadUrl, headers=headers, stream=True)
 

data = json.loads(requestDownload.content)

df = json_normalize(data['responses'])

data = requestDownload.content
df = pandas.read_csv(io.StringIO(data.decode("utf-8")))
df

Does anybody have any experience with working the V3 API and Power BI? I'm simply trying to have survey results automated as they come in and they've made it harder than it should be. Do I still have to have a CSV file somewhere on my computer in order to do this? I was under the impression the survey results could be imported right into Power BI.

Best Answer

  • KendraRKendraR Provo, UT Moderator
    Accepted Answer

    Hi, @RCBIZMarketing! It looks like you were able to reach out to our Support Team and work with an Integrations Specialist. As they mentioned, Qualtrics does not currently have a supported integration with Power BI. Though it may be possible to accomplish this by using the Python Script functionality with our v3 APIs, this process would require some coding abilities from your end that our Support Team is unable to assist with.

Answers

  • davidgarza95davidgarza95 New YorkCommunity Member Qubie ✭

    @KendraR could you help me contact the Integrations Specialist from the Support Team that helped @RCBIZMarketing so I can also connect Qualtrics to Power BI?

  • gunterhgunterh Community Member Qubie ✭
    edited March 5

    @RCBIZMarketing For the above code, I would recommend changing one thing under step 3:

    try:
        data = json.loads(requestDownload.content)
        df = json_normalize(data['responses'])
    except:
        data = requestDownload.content
        df = pandas.read_csv(io.StringIO(data.decode("utf-8")))
    return df
    

    What you'll get is exception handling and the file should pass through. The error might have been from json not loading the file, but if you have the specific error response that would help as well.

Sign In to Comment