Varnames in Firstrow of Excel import not recognized

[Rob Henst]

Hi,

I have been using the following command to import an excel spreadsheet for a few years now, without any problems.

import excel "/Users/Rob/Documents/Participant data list_no format.xlsx", sheet("All_data") firstrow case(lower) clear

However, recently the 257th column, which first row (column header) is "Q35" produces an error, because the column header is "if" (following from ia, ib, ic, id, ie) instead of "Q35", which is not allowed as a varname. Therefore, the import produces an error, which I posted about before, here: https://www.statalist.org/forums/for...ting-xlsx-file

Before this problem occurred, my columns were not A, B, C, etc., but the original first row column headers (i.e. Q1, Q2, Q3, or var1, var2, var3 in case of a conflict).

Why is the first row of the excel workmap no longer recognized as the header, and why is A, B, C used instead? How do I solve this, so that I can import the excel file again.

Also important of note is that, if I convert the file to *.csv and import it using the following command, it works perfectly fine and the first row are recognised as varnames:

insheet using "/Users/Rob/Documents/PhD Exercise Science/Participant data list_no format.csv", comma clear

I just don't want to have to convert the excel file to *.csv all the time.

Thank you so much.

[Joseph Coveney]

You could try something along the lines of the following. It uses the Auto dataset for illustration.

sysuse auto
export excel using Auto, sheet(Autos) firstrow(variables)
drop _all

*
* Begin here
*
// Import first row (column headers) only
import excel Auto.xls, sheet(Autos) cellrange(A1:L1) allstring

// Get the column header names and place into local macros
local column_index 1
foreach var of varlist _all {
    rename `var' var`column_index'
    local varname`column_index' = var`column_index'[1]
    local ++column_index
}

// Get the remaining rows (data) only
import excel Auto.xls, sheet(Autos) cellrange(A2:L75) clear

// Rename the A, B, C to column header names (converting to acceptable Stata names when needed)
local column_index 1
foreach var of varlist _all {
    local new_name `=strtoname("`varname`column_index''")'
    rename `var' `new_name'
    local ++column_index
}

exit

There are probably more elegant ways, but this approach should get you what you want.

[Rob Henst]

Hi Jospeh,

Thank you very much.

The codes works and it does get me what I want. Awesome!

I am a bit suprised that a data analysis software as Stata runs into this type of problem and needs such a roundabout way of solving it.

I would still be open to alternative, "elegant" solutions, as you put it.

Thank you so much again.