Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can I upload an excel file with multiple sheets to stata?

    Hi, how can I upload to stata an excel file(from mac) with multiple sheets?

    Thanks in advance,

  • #2
    Carmen,

    You can import each sheet one at a time using the sheet() option for the import excel command. If you want to automate the process you can use a loop:

    Code:
    foreach sheet in "Sheet1" "Sheet2" ... {
      import excel using file.xls, sheet("`sheet'")
      save file_`sheet', replace
    }
    Regards,
    Joe

    Comment


    • #3
      Doesn't import excel work on the Macintosh? I would imagine that if the worksheets are similarly arranged (same number of columns and same types of data in each), then you could set up a foreach loop with the names of the worksheets, and import each one in succession, saving to a tempfile and then import the next worksheet and append them. I believe that this general algorithm has been shown several times in the past. You might want to do a search of the list's archives to see the details.

      Comment


      • #4
        And if you really want to automate the process (i.e., without having to specify the name of each sheet):

        Code:
        import excel using file.xls, describe
        forvalues sheet=1/`=r(N_worksheet)' {
          local sheetname=r(worksheet_`sheet')
          import excel using file_xls, sheet("`sheetname'")
          save "file_`sheetname'", replace
        }

        Comment


        • #5
          Also have a look at xls2dta from SSC, which is a wrapper for import excel that does exactly what has been suggested here.

          The syntax could be something like

          Code:
          cd directoy_containing_xls_files
          xls2dta ,allsheets
          Best
          Daniel
          Last edited by daniel klein; 01 May 2014, 12:06.

          Comment


          • #6
            Thank you guys, your suggestions were great. In my case the code by Joe did not work exactly as Stata said that data in memory would be lost.

            With minor adjustment it worked:
            Code:
             import excel using file.xls, describe
            forvalues sheet=1/`=r(N_worksheet)' {  
            local sheetname=r(worksheet_`sheet')  
            import excel using file_xls, sheet("`sheetname'")  
            save "file_`sheetname'", replace  
            clear
            }

            Comment


            • #7
              I used code provided by Tobias and Joe. Both gave me an error "file_xls not found". I think this error is coming from
              "import excel using file_xls, sheet("`sheetname'". I used original link to the data file, but does not work. How should I manipulate this section to make it work?

              Comment


              • #8
                Originally posted by Bijesh Mishra View Post
                I used code provided by Tobias and Joe. Both gave me an error "file_xls not found". I think this error is coming from
                "import excel using file_xls, sheet("`sheetname'". I used original link to the data file, but does not work. How should I manipulate this section to make it work?
                You need to replace file_xls with the Excel file name of yours. In addition, as a prerequisite for the codes to work, you will need to change the working directory in Stata to where your Excel file is located by using

                cd "directory"

                Other troubleshooting possibilities may include making sure whether your file is in .xls or .xlsx format; If the original link has spaces in name of file or folder, you need to put quotes to the full path such as


                import excel "C:\path with spaces\excel file.xlsx" .

                Hope that helps.

                Comment


                • #9
                  Hello all,

                  I am using Stata 16. I want to import data from the same excel file from 3 different sheets, all in the cell range A3:C22.
                  I want to obtain a single file where all the variables and observations appear from the 3 sheets. The sheets are in alternative order and not in consecutive order. I used the following
                  Code:
                  clear
                  cd "G:\revenue\dt\Oct 2011-Dec 2011"
                  import excel "firm_100" 
                  foreach sheet in "ANNEX1" "ANNEX3" "ANNEX5" {
                  import excel using "firm_100", sheet("`sheet'"), cellrange(A3:C22)
                  save file_`sheet', replace
                  }
                  I get invalid 'cellrange'
                  r(198);

                  and also when I tried it without the cell range, I only get the ANNEX1 sheet, not ANNEX3 and ANNEX5. Is there a problem with my loop?

                  Kindly suggest to me where I am going wrong. Thank you

                  Comment


                  • #10
                    first, import sheets,
                    Code:
                    foreach sheet in "ANNEX1" "ANNEX2" "ANNEX3" {
                    import excel "firm_100", sheet(`sheet') cellrange(A3:C22) clear
                    tempfile `sheet'
                    save ``sheet''
                    }
                    then, either merge sheets (varlist are the variables that the sheets will be matched on),
                    Code:
                    use `ANNEX1', clear
                    merge 1:1 varlist using `ANNEX2', nogen
                    merge 1:1 varlist using `ANNEX3', nogen
                    or, append sheets,
                    Code:
                    use `ANNEX1', clear
                    append using `ANNEX2'
                    append using `ANNEX3'

                    Comment


                    • #11
                      Thank you Sir, I got the temp files. I want to merge them, so while merging I used the above code you shared

                      Code:
                      use `ANNEX1', clear
                      merge 1:1 varlist using `ANNEX3', nogen
                      merge 1:1 varlist using `ANNEX5', nogen
                      and I got the following output --

                      do "C:\Users\INSPIRON\AppData\Local\Temp\STD2fe8_0000 00.tmp"

                      . use `ANNEX1', clear
                      invalid file specification
                      r(198);

                      end of do-file

                      r(198);

                      .

                      Am I making a mistake in saving?

                      Comment


                      • #12
                        is the sort order the same across sheets? i.e., does the first observation in ANNEX1 correspond to the first observation in ANNEX2 and ANNEX3, and so on?
                        if so, run the following code without interruption,
                        Code:
                        foreach sheet in "ANNEX1" "ANNEX2" "ANNEX3" {
                        import excel "firm_100", sheet(`sheet') cellrange(A3:C22) clear
                        gen id = _n
                        tempfile `sheet'
                        save ``sheet''
                        }
                        
                        use `ANNEX1', clear
                        merge 1:1 id using `ANNEX2', nogen
                        merge 1:1 id using `ANNEX3', nogen

                        Comment


                        • #13
                          Thank you sir, for the code. I got the output but I can only see the annex1 observations.
                          The data in sheets corresponds to a firm name and expenditure for 3 different variables.
                          So I am trying to obtain where for var1 firm name, amount from Annex1, for var2 firm name amount from Annex3 and for var3 firm name and amount from annex5 and merge them together to see them in a single datasheet from that excel file.

                          Grateful for your help.

                          Comment


                          • #14
                            Yes, of course. Try,
                            Code:
                            foreach k in 1 2 5 {
                            import excel "firm_100", sheet(ANNEX`k') cellrange(A3:C22) firstrow clear
                            if `k'==5 {
                            keep firm var3
                            }
                            else {
                            keep firm var`k'
                            }
                            tempfile Sheet`k'
                            save `Sheet`k''
                            }
                            
                            use `Sheet1', clear
                            merge 1:1 firm using `Sheet2', nogen
                            merge 1:1 firm using `Sheet5', nogen

                            Comment


                            • #15
                              Thank you for this great query and replies!
                              I was following the thread for the same issue.
                              How should I sort the variable within each sheets (before merging)?
                              I have used the following command in Stata 16.1 (as mentioned above)
                              Code:
                              foreach sheet in "sheet 1" "sheet 2" "sheet 3" {
                              import excel "C:\Users\survey_data_02092022.xlsx", sheet(`sheet') firstrow clear 
                              tempfile `sheet'
                              save ``sheet''
                              }
                              use `sheet 1', clear
                              merge 1:1 SURVEY_ID using `sheet 2', nogen
                              merge 1:1 SURVEY_ID using `sheet 3', nogen
                              However, the command is not running without interruption,
                              The error message is
                              variable UNIT is str4 in master but byte in using data
                              Thanks in advance

                              Comment

                              Working...
                              X