# Import of Excel file into Stata!

#### Maastricht2006

##### New Member
Hello everyone,

I am a beginner with stata. I tried to import an excel file with ODBC data source.
I already shortened the variable names without empty spaces and less than 32 characters. I only used 4 variables. Data Type is str244.

I insert the data and get no error message. However, if I go on summarize to show number of observations, mean etc. Stata does not show any results

Does somebody know this problem and can tell me what I am doing wrong?
That would be very nice!

#### zerostin

##### New Member
Can you maybe upload some sample data?

#### Maastricht2006

##### New Member
yes no problem, here is the excel file

#### zerostin

##### New Member
If I try to use your data I don't get any observations. If I recreate a part of your data, then I don't have any problems.

+------------------------------------------------------+
| cusip year compan~e state Gindex classi~d |
|------------------------------------------------------|
1. | 00751Y 2002 test_1 de 8 0 |
2. | 00751Y 2004 test_2 de 9 0 |
3. | 00751Y 2006 test_3 de 8 0 |
+------------------------------------------------------+
So, I guess the problem is the excel file, but I don't know what is causing that problem. Perhaps somebody has a better solution, otherwise you maybe need to recreate your excel file.

#### bukharin

##### RoboStataRaptor
It's a pretty strange Excel file; eg what happened to rows 21-34? I can't seem to unhide them (then again I'm pretty useless in Excel...)

I copied and pasted the data from Excel to Stata which seemed to work okay, but only copied over the observations that weren't hidden. I suspect if you "unhid" the other observations you could simply copy and paste.

Otherwise I would recommend finding someone with a copy of Stat/Transfer which makes this type of conversion very easy.

#### Maastricht2006

##### New Member
thanks...probably the fact that I used a filter in the excel file why some values were hidden was the problem...So I will just copy/paste the data in new excel file...or I use my demoverison of Stat/Transfer

#### bukharin

##### RoboStataRaptor
Ah of course, the filters. I tried it with the filters disabled and copying/pasting worked fine.

Don't use a demo version of Stat/Transfer for anything important - it will randomly delete approx 1 in 16 observations. The idea apparently is that you can see what the program can do, but have to buy it to actually do anything useful.

#### Maastricht2006

##### New Member

However, I am already dealing with the next problem.

In the Excel file, I refer to the first sheet and the 6digit Cusips. The cusips are company identifiers and represent firms that introduced antitakeover provisions, such as classified boards, over the period from 1997 to 2009. Classified Boards is an independent governance variable that I use in my analysis. Most of the cusips appear several times for one firm, because they refer to the different years from 1997 to 2009.

In the second, I have other control variables. They measure size, leverage, risk. In the third sheet I have the dependent variable profitabiity measured by e.g. Operating profit margin. The 6digit cusips (Cusip6) are mentioned as well. As not all firms introduced antitakeover provisions, the first sheet does not have all cusips of Sheet 2 and 3.

I want to merge the data from sheet 2 and 3 according to the Cusisp of the first sheet. Then I want to import the data into Stata to do the regression.

Does somebody now a function how to do that in Excel?

#### Maastricht2006

##### New Member
hey everyone, its ok, I have already found a solution to this problem

#### Maastricht2006

##### New Member
hello...

I am trying to convert the two attachted Excel files into Stata.

with the first file I get this error message
1. odbc load, dsn("Excel Files") table("Sheet1$") dialog(complete) Annualized_S&P500_return_ invalid name - converted Annualized_S&P500_return_ to var2 with the second file I get this error message 2. odbc load, dsn("Excel Files") table("'G index 97-06$'") dialog(complete)
r(18);

I dont really understand this. Does somebody know the solution to the problem?

regards

#### Maastricht2006

##### New Member
here the two attachments that i mention above

#### zerostin

##### New Member
hello...

I am trying to convert the two attachted Excel files into Stata.

with the first file I get this error message
1. odbc load, dsn("Excel Files") table("Sheet1$") dialog(complete) Annualized_S&P500_return_ invalid name - converted Annualized_S&P500_return_ to var2 Try to delete the " &" in your variable names and try it again? with the second file I get this error message 2. odbc load, dsn("Excel Files") table("'G index 97-06$'") dialog(complete)
r(18);

I dont really understand this. Does somebody know the solution to the problem?
regards
If you click submit, he imports the data. If you afterwards click on okay, you get this error, but the data is already imported.
If that's not the case:

Import sheet 1, save sheet1.dta;
clear
Import sheet 2, save sheet2.dta
clear

You can't import sheet 1, 2 and 3 in one dataset as far as i know. You need to merge them afterwards.

Last edited:

#### Maastricht2006

##### New Member
Hey everyone,

after importing my file into stata, stata does not read some variables. I assume that they are not coded correctly and wanted to change them from string to numeric by using destring, real() or encode. However, it does not work I always get error messages like these:

CUSIP6encode
unrecognized command: CUSIP6encode
r(199);

. real( CUSIP6)
unrecognized command: real
r(199);

. destring CUSIP6
must specify either generate or replace option
r(198);

I already put a lable next to the variable name, but this is also not working.
Anybody knows what I am doing wrong?

#### duskstar

##### New Member
As the error message says you need to specify a generate or replace. For example, try:

replace CUSIP6=real(CUSIP6)

or

gen CUSIP6_new = real(CUSIP6)

#### Maastricht2006

##### New Member
Thank you.

In the meantime I am already dealing with the next problem.

If I type summarize I do not get any values for State_of_Inc or State_of_Inclower. I added State_of_Inclower because I thought the capital letters of State_of_Inc could be the problem, but it still does not find values.

The same is valid for Company and Companylower. I use string as datatype because the variables represent text.

Does somebody know the solution to the problem? View attachment 1305

Last edited:

#### zerostin

##### New Member
That's because there aren't any numbers in these variables. So, it doesn't make any sense, and it is not possible, to calculate mean, SD, etc.