Qualtrics dumps its data to Excel is a format that’s not quite ready for Stata, but it’s pretty close. You get variable names in the first row and variable labels in the second row. Variable labels longer than XX characters get truncated in the middle in the case of sub-items on ‘matrix’ and other survey questions with multiple components, such that the title of the sub-item is listed in full if possible. We can extract the sub-item label, if it exists, by splitting the string on either of the delimiters Qualtrics (currently!) uses: …- and ?-
import excel filename.xlsx, firstrow clear
foreach v of varlist _all {
local l`v' = `v'[1]
local l`v' = subinstr("`l`v''","...-","|",.)
local l`v' = subinstr("`l`v''","?-","|",.)
tokenize "`l`v''", parse("|")
if "`3'"=="" {
label variable `v' "`1'"
}
else {
label variable `v' "`3'"
}
note `v' : "`l`v''"
}
drop in 1
Thanks for this — very useful!
One further request: When my labels are too long, they are not being truncated. They appear in the note, but nothing in the label. It would be great if you could truncate the labels to the *last* 80 chars and enter this as the variable label.
Good point – you could truncate like this:
(extended macro functions come in handy here – see p12 of https://www.stata.com/manuals13/pmacro.pdf)
local len1 : length macro 1
if `len1′ > 80 {
local 1 = substr(“`1′”, -80,.) // last 80 chars
}
local len3 : length macro 3
if `len3′ > 80 {
local 3 = substr(“`3′”, -80,.) // last 80 chars
}
This was super helpful to me! Thanks so much!!!!