Spaces in imported variable names

[paulvonhippel]

I am importing the attached excel file, as follows:

import excel "Year-round trends\SASS NTPS trends\National trends, values.xlsx", sheet("Nationwide percentages") firstrow clear

By default a column header like "pct year-round single track" turns into the variable name pctyearroundsingletrack (with no spaces), which is not very readable.

I would prefer to replaces the spaces with underscores, like this: pct_year_round_single_track
Is there a way for Stata to do that?

Thanks!
Paul

[Joseph Coveney]

I don't see any option to import excel that would allow that, but you can use SQL to rename your variables on-the-fly via ODBC. Something like the following:

version 17.0

clear *

#delimit ;
local sql_statement SELECT [School year] AS school_year, Survey, Note,
    [pct year-round] AS pct_year_round,
    [pct year-round single-track] AS pct_year_round_single_track,
    [pct year-round multi-track] AS pct_year_round_multi_track FROM [Sheet1$];
#delimit cr

local dsn Excel Files;DBQ=National trends, values.xlsx;DefaultDir=`c(pwd)'\;

odbc load, exec("`sql_statement';") dsn("`dsn'")

exit

Illustrated for Microsoft Windows operating system (you'll need to set up ODBC if you haven't already done so).

[Hemanshu Kumar]

import excel "Year-round trends\SASS NTPS trends\National trends, values.xlsx", sheet("Nationwide percentages") firstrow clear

foreach var of varlist * {
    local lab: var label `var'
    local varname = subinstr("`lab'"," ","_",.)
    local varname = subinstr("`varname'","-","_",.)
    rename `var' `varname'
}

[daniel klein]

For the approach outlined in #3, also see

help strtoname()