How to export interview data in Excel

Export
#1

Can you please tell me the way to explort the interview data I collected in excel format? Because it is only downloading in tab format.

Many thanks.

#2

Here are a few ways to achieve this:

  • Open tab file with Excel and save as xlsx. Here’s a guide. There might be others. There might be extra/different steps if your data files contain Unicode characters.
  • Convert multiple files from tab to Excel. Use some (statistical) software to:
    • Open tab file
    • Save as Excel
    • Repeat for each target file (e.g. every .tab file in a folder)

To my knowledge, this could be done with R and Stata. Undoubtedly, this could also be done for Python.

Here’s an R solution:

# write file paths to source and destination directories, respectively
# note: use / instead of \ in path, since \ is an escape character for R
source_dir <- "C:/your/dir/here/" 
destination_dir <- "C:/your/dir/here/"

# install all necessary packages
install.packages(c("readr", "writexl", "fs", "purrr"))

#' Convert tab-delimited file to Excel
#' 
#' @param file_in Character. File path to tab-delimited file (input)
#' @param file_out Character. File path to desired Excel file destination (output)
#' 
#' @importFrom readr read_tsv
#' @importFrom writexl write_xlsx
#' 
#' @return Side-effect of writing an Excel file.
convert_from_tsv_to_xlsx <- function(file_in, file_out) {

    # read tsv file
    df <- readr::read_tsv(file = file_in, file_out)

    # write Excel file
    writexl::write_xlsx(x = df, path = file_out)

}

# collect names of all .tab files in the source directory, without file extension
files <- source_dir |>
    fs::dir_ls(path = source_dir, type = "file", regexp = "\\.tab$") |>
    fs::path_file() |>
    fs::path_ext_remove()

# write an Excel file for each tab-delimited file found
purrr::walk(
    .x = files,
    .f = ~ convert_from_tsv_to_xlsx(
        file_in = fs::path(source_dir, paste0(.x, ".tab")),
        file_out = fs::path(destination_dir, paste0(.x, ".xlsx"))
    )
)

With Stata, you can do something similar by using import delimited and export_excel.

With Python, I’m sure there are equivalent tools.

1 Like
#3

In short:

  • Survey Solutions does not export data to MS Excel files (to true *.xls or *.xlsx typed files).
  • data files produced by Survey Solutions can be imported to MS Excel application. Consult the corresponding manual for MS Excel for importing a tab-delimited unicode text file. The internet is also full of instructions of this kind, here is a third-party produced instruction/tutorial.

That said, MS Excel is quite a poor tool to handle this kind of data, given that Survey Solutions exports data into multiple files which must be linked to each other using the relationships between the questionnaire levels.