Forum Discussion

reffr's avatar
reffr
New Member
10 years ago

Import SPSS file into Power BI

Has anyone been able to easily import an SPSS .SAV file into Power BI?  Any help would be greatly appreciated.  I am a Power BI newbie so please don't judge!   Thanks in advance for your help. Rob...
Need Help
Barendnu's avatar
Barendnu
Advocate III
9 years ago

Actually, via R, there is a possibility to at least load SPSS data. Unfortunately, variable labels seem to be lost, so I support this idea to improve Power Bi with a dataconnector for SPSS files, but this is a way to get data from SPSS to Power BI:

 

# 1. Install R https://mran.microsoft.com/open/

# 2. Install Microsoft R Client https://msdn.microsoft.com/en-us/microsoft-r/r-client-get-started
# 3. Now go to PowerBI, go to Get data > More > Other > R script and copy/paste this R script:

 

spssFile <- file.path("d:\\yourpath\\yourfile.sav");
tempFile <- "c:\\temp\\temp.xdf";
tempData <- rxImport(inData = spssFile, outFile = tempFile, overwrite=TRUE);
spssData <- rxFactors(inData=tempData, sortLevels=TRUE,factorInfo=c("weegvar"));

 

# In the first line, be sure to enter the path to your SPSS file and use double slashes (\\)
# In the last line, enter a variable in your dataset to sort on
# Reference and more info: https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-data-import

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I can confirm this works for a Survey Monkey Result set! Thank you so much for this. A few things to keep in mind:

    Make sure you're on the latest R Client to also have access to rxImport. I had to upgrade mine. 

    The last line assumes you have a column named weegvar. I suggest relaciing that with StartDate or RespondentId.

    The questions don't have labels. so you will need to go back to your survey to figure out what q0011 really was about. 

     

    If you want to read multiple Survey Files in one hit it gets a bit more complicated. You need to use lapply to run rxImport mutlitple times. the issue is that you don't load the full dataset directly into memory but instead into an xdf file. Reloading the xdf file once you're done is all it then takes to use the data in Power BI.

    Here is my version of the script to handle multiple surveys

     

    #delete the xdf file so we don't duplicate data on each refresh. 

    tempFile <- "c:\\temp\\temp.xdf";
    if (file.exists(tempFile))
    {
    file.remove(tempFile)
    }

    mySourceFiles <- list.files("d:\\yourpath", pattern = "*.sav", full.names=TRUE)

    lapply(mySourceFiles, FUN = function(sav_file) {
    rxImport(inData = sav_file, outFile=tempFile, append = file.exists(tempFile)) } )

    ds<- rxImport(inData = tempFile)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      has anyone overcome the issue where variable labels are not being imported into Power BI?  I run the same script in R and variable labels come across  but not in Power BI.  The code I'm using is:

       

      library(foreign)
      dataset = read.spss("C:\\SPSS data files\\telephone2018.sav",use.value.labels =TRUE, to.data.frame =TRUE, full.names = TRUE )

       

      Am I missing anything?

       

       

       

      Any help much appreciated.

       

      Thanks

       

      Jo

  • Dayem's avatar
    Dayem
    Regular Visitor
    6 years ago

    Thanks A million. 

    It works with me, but is there ability to change data from values to numbers?