Troubleshoot excel import

[Sarah Heavey]

I'm trying to import excel data (.xlsx) using code that works for other excel workbooks, but for some reason it is not working here. I use the first row as variable names and the second row as variable labels, with the data following.

import excel using mydata.xlsx , clear  cellrange(A1:AJ365) 
foreach var of varlist * {
    local row1 = `var'[1]
    local row2 = `var'[2]
    label var `var' "`row2'"
    local vn = strtoname("`row1'")
    rename `var' `vn'  
}

It works until the rename statement, when I get a "syntax error" r(198). However, when I use this exact code, just with a different xlsx file, it runs perfectly. Any thoughts on what I'm missing here?

[Daniel Schaefer]

I'd bet the local `vn' is set to the empty string on some iteration. If for whatever reason the excel file isn't formatted exactly as you expect, it is possible the row1 local is empty on an iteration of the loop. In that case, strtoname will just return the empty string (i.e. nothing) and the local vn won't be set to anything.

As a next step, you should inspect `var' and `vn' with display.

display "`var'"
display "`vn'"

Do this just before the rename line. If either of these display commands print nothing, that's your problem. You might also visually inspect the first two rows of the excel file after it's loaded in. Since the code works for a different excel file, chances are good the problem is with the format of the problem excel file.

[Sarah Heavey]

this was incredibly helpful! Thanks you!