+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

Thread: Import of Excel file into Stata!

  1. #1
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Import of Excel file into Stata!




    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!

  2. #2
    Points: 1,469, Level: 21
    Level completed: 69%, Points required for next Level: 31

    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    Can you maybe upload some sample data?

  3. #3
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    yes no problem, here is the excel file
    Attached Files

  4. #4
    Points: 1,469, Level: 21
    Level completed: 69%, Points required for next Level: 31

    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    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.

  5. #5
    RoboStataRaptor
    Points: 10,127, Level: 67
    Level completed: 20%, Points required for next Level: 323
    bukharin's Avatar
    Location
    Sydney, Australia
    Posts
    1,285
    Thanks
    11
    Thanked 317 Times in 308 Posts

    Re: Import of Excel file into Stata!

    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.

  6. #6
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    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

  7. #7
    RoboStataRaptor
    Points: 10,127, Level: 67
    Level completed: 20%, Points required for next Level: 323
    bukharin's Avatar
    Location
    Sydney, Australia
    Posts
    1,285
    Thanks
    11
    Thanked 317 Times in 308 Posts

    Re: Import of Excel file into Stata!

    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.

  8. #8
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    thx for the advice...

    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?
    Attached Files

  9. #9
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

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

  10. #10
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    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)
    you must start with an empty dataset
    r(18);

    I dont really understand this. Does somebody know the solution to the problem?
    or even better: Does someone know a site where to download the full version of stat transfer for free?^^


    regards

  11. #11
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    here the two attachments that i mention above
    Attached Files

  12. #12
    Points: 1,469, Level: 21
    Level completed: 69%, Points required for next Level: 31

    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    Quote Originally Posted by Maastricht2006 View Post
    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)
    you must start with an empty dataset
    r(18);

    I dont really understand this. Does somebody know the solution to the problem?
    or even better: Does someone know a site where to download the full version of stat transfer for free?^^
    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 by zerostin; 04-05-2011 at 11:58 AM.

  13. #13
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!

    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?

  14. #14
    Points: 1,838, Level: 25
    Level completed: 38%, Points required for next Level: 62

    Location
    UK
    Posts
    163
    Thanks
    0
    Thanked 9 Times in 9 Posts

    Re: Import of Excel file into Stata!

    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)

  15. #15
    Points: 1,951, Level: 26
    Level completed: 51%, Points required for next Level: 49

    Posts
    68
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import of Excel file into Stata!


    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?trial stata 3 var.xls
    Last edited by Maastricht2006; 04-25-2011 at 09:05 AM.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

           




Similar Threads

  1. Replies: 2
    Last Post: 12-02-2010, 06:19 AM
  2. Help with ANOVA: Please Check my Excel file.
    By physio_amer in forum Biostatistics
    Replies: 1
    Last Post: 10-19-2010, 12:26 PM
  3. Reading .csv excel file
    By Deep in forum SAS
    Replies: 3
    Last Post: 07-12-2010, 01:45 PM
  4. excel file not opening into SPSS
    By Dr.Appalayya in forum SPSS
    Replies: 4
    Last Post: 05-17-2010, 10:07 AM
  5. Stata - Do-file capability
    By sjq2102 in forum Stata
    Replies: 0
    Last Post: 05-10-2009, 10:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats