Batch conversion of SPSS files to Excel using R

Photo by Lukas Blazek on Unsplash

Many SPSS users struggle to use other analytical softwares that do not support sav files. The situation gets even more disappointing when there are too many files to handle.

Luckily for us, R is not one of them. It’s not only not one of them but also allows the conversion of sav files to most other data formats, including CSV, Excel, SAS and Stata.

In this post, we will see the conversion of sav datafile to Excel format for individual and multiple files.

Converting a single file

library(foreign)

# make sure the dataset is located in your working directory

df = read.spss("mySPSSdata.sav")

Once the data is loaded, you can view it by typing

View(df)

Once we are happy with the data, we can export it by typing:

library(xlsx)
write.xlsx(df, 'myExcelFile.xlsx')

The command will store the dataset in the working directory as myExcelFile.xlsx.

Converting multiple files

That was easy. But things will start to look messy if we have multiple files to convert. To do that, we can leverage the power of loops:

# Specify the directory containing the SPSS files
dir_path <- "folder containing spss files"

# Get a list of all SPSS files in the directory
spss_files <- list.files(path = dir_path, pattern = "\\.sav$", full.names = T)

# Now loop through the list of SPSS files
for (i in 1:length(spss_files)) {
   data <- read_sav(spss_files[i])
   file_name <- sub("\\.sav$", "", basename(spss_files[i]))
   write_xlsx(data, paste0(dir_path, "/", file_name, ".xlsx"))
}

This command will a create a copy of each of the SPSS files in Excel format.

Yes, this method can be used to convert to other file types. Just remember to use the appropriate commands for the type of file you are using.

Happy Excelling!