Help to import from Excel

[Jorge Vinseiro]

Hi.

I am trying to import an excel file into Stata. However, when I use the option firstrow, Stata name soke of the variables using the columns letters from the excel file. At the same time, Stata uses the variable names in the first row to create labels.

Can somebody explain me how to overcome this issue with Stata?

Thanks.

[Nick Cox]

Why is this an "issue", presumably meaning a problem? Do you prefer all your variables to be named A B C onwards?

Perhaps you should give an example of how the first few rows appear in Excel, what Stata does, and what you want instead.

(Please don't show screenshots; copy and paste into Statalist.)

[Jorge Vinseiro]

Well, its a problem.

Look at the following example containing three of the variables in my data. I am attaching an example of the data. I am using the following command to import this data:

import excel "$route\Data.xls", sheet("1") cellrange(A3) firstrow clear

I would really appreciate if you can find the problem.

Thanks.

[David Benson]

Hi Jorge,

Normally people won't open Excel files from strangers (I, being foolish, did this once). (In the future, you could post it as a .txt or .csv file).

Here are a few of the reasons Stata is using column letters for variable names:
1) In some cases there are empty columns, and in those cases, Stata used the column letter (i.e. "BD").

2) Stata doesn't allow duplicate variable names, so since "SISTEMA BANCARIO" is repeated in column BK and BN, the 2nd time around Stata named the variable the column letter.

3) Regarding var labels: Stata doesn't allow variable names to have spaces, start with numbers, etc. So Stata will convert "NACIONAL DE CRÉDITO" to variable name "NACIONALDECRÉDITO" but put ""NACIONAL DE CRÉDITO" as the variable label.

4) Spreadsheets often have empty rows to make things easier to read, but you will want to delete those observations in Stata.

Finally, I assume that you will end up reshaping this data from wide format to long. If that is the case, you might find the following posts helpful:

• Import many sheets from an excel file, reshape, and merge
• This post here also has good info

NOTE: I've also uploaded the files as .CSV or .TXT files in case others want to chime in.

[Jorge Vinseiro]

Thank you David for your advise. I was not aware of the dangers of Excel files. I will keep it in mind for the future. Also thanks for your ideas about the data.