Announcement

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

  • importing multiple excel files using a loop

    Hi statalists, I have the following code but I always get the error message

    local dpto AMAZONAS ANCASH APURIMAC
    foreach x in local dpto {
    import excel using "Z:\HigherEducation_Inequality\INEI\`x'_t.xls" , sheet(`x') clear
    keep A B C
    keep if A=="18"|A=="19"
    xpose,clear
    drop in 1
    rename v1 pob18
    rename v2 pob19
    gen Name=`x'
    gen id=_n
    gen Year=1998 if id==1
    replace Year=1999 if id==2
    drop id
    save data_`x',replace
    }

    file Z:/HigherEducation_Inequality/INEI`x'_t.xls not found
    r(601);


    Does anyone has any thoughts about what am I doing wrong?


  • #2
    I'm not sure what operating your system you're using, but you have

    Code:
    import excel using "Z:\HigherEducation_Inequality\INEI\`x'_t.xls" , sheet(`x') clear
    with backslashes \

    And your error with forwardslashes /
    Code:
    file Z:/HigherEducation_Inequality/INEI`x'_t.xls not found
    And your error doesn't match your file path above (/INEI`x'_t.xls instead of INEI\`x'_t.xls).

    I would also try sheet("`x'") instead of sheet(`x')

    Comment


    • #3
      I noticed that too but I don't know why does the error changes the code. I followed your tip I got this, I had to applied the "" to every `x' cause when I only try it with the ...sheet("`x'") I still got the same error message. This way I get this:
      clear all
      cd "D:"
      local varlist "AMAZONAS ANCASH APURIMAC"
      foreach x in `varlist' {
      import excel using "Z:\HigherEducation_Inequality\INEI"`x'"_t.xls ", sheet("`x'") clear

      save data_"`x'",replace
      }

      invalid 'AMAZONAS'
      r(198);

      Comment


      • #4
        Rocio,

        Since the directory address involves no space, this below may work:

        Code:
        import excel using Z:\HigherEducation_Inequality\INEI`x'_t.xls , sheet(`x') clear

        Comment


        • #5
          It's because of Stata's use of the \ as escape character.
          https://www.stata.com/support/faqs/programming/backslashes-and-macros/

          Code:
          import excel using "Z:\HigherEducation_Inequality\INEI\`x'_t.xls" , sheet(`x') clear
          Will be interpreted as
          Code:
          import excel using "Z:\HigherEducation_Inequality\INEI`x'_t.xls" , sheet(`x') clear
          Prevent by writing as:
          Code:
          import excel using "Z:\HigherEducation_Inequality\INEI\\`x'_t.xls" , sheet(`x') clear
          Or
          Code:
          import excel using "Z:\HigherEducation_Inequality\INEI/`x'_t.xls" , sheet(`x') clear

          Comment


          • #6
            Thanks a lot! It worked

            Comment

            Working...
            X