cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
reffr Frequent Visitor
Frequent Visitor

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

 

7 REPLIES 7
ankitpatira Super Contributor
Super Contributor

Re: Import SPSS file into Power BI

@reffr power bi desktop has number of in-built file connector that you can use but SPSS isn't one of it. therefore if you're able to convert spss file into any of these format then you can import. also of you are able to find odbc driver that can access spss file then in power bi desktop you can use odbc connector to access that file.

 

Capture.JPG

Barendnu Frequent Visitor
Frequent Visitor

Re: Import SPSS file into Power BI

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

 

micahwilliams12 Frequent Visitor
Frequent Visitor

Re: Import SPSS file into Power BI

 With Power BI Desktop, you can connect to data from many different sources. A full list of available data sources is at the bottom of this page.

 

May be this link gives you the solution about importing spss file: https://docs.microsoft.com/en-us/power-bi/desktop-data-sources

alxdean Frequent Visitor
Frequent Visitor

Re: Import SPSS file into Power BI

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)

jellis_sv New Member
New Member

Re: Import SPSS file into Power BI

Use the IBM ODBC. Usuallyit is provided with SPSS install software or from the IBM Passport site (where one downloads IBM entitled software from). For reference you may visit

https://www-01.ibm.com/support/docview.wss?uid=swg21609910

 

Also there is copy of the IBM manual for an older version of this ODBC driver that you can get here

https://www.academia.edu/28578792/IBM_SPSS_Statistics_Data_File_Driver_Guide

 

Hope it helps

jojop1972 Member
Member

Re: Import SPSS file into Power BI

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

jamesperry Frequent Visitor
Frequent Visitor

Re: Import SPSS file into Power BI

I've used the Haven package as described https://rstudio-pubs-static.s3.amazonaws.com/466787_84982191b86e4591bfcee776e498a6d4.html

 

Worked perfectly.

 

 

James

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 344 members 3,424 guests
Please welcome our newest community members: