Announcement

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

  • Extracting names of file from a directory

    Dear All,

    I have a folder containing more than 250 files. Each file is identified by the name of a country. I would like to save in a macro the list of those files, since I want to loop over them later.

    I tried:

    Code:
    local f_names: dir "C:\Users\dario\Downloads\wetransfer_bilateral_2023-11-23_1545\bilateral" files "*"
    But the result is not as expected (below is just an example)

    Code:
    display `f_names'
    
    afghanistan.xlsxalbania.xlsxalgeria.xlsxamerican samoa.xlsxandorra.xlsxangola.xlsxanguilla.xlsxantigua and barbuda.xlsxargentina.xlsxarmenia.xlsxaruba.xlsxaustralia.xlsxaustria.xlsxazerbaijan.xlsxbahamas.xlsxbahrain.xlsxbangladesh.xlsxbarbados.xlsxbelarus.xlsxbelgium.xlsxbelize.xlsxbenin.xlsxbermuda.xlsxbhutan.xlsxbolivia, plurinational state of.xlsxbonaire, saint eustatius and saba.xlsxbosnia and herzegovina.xlsxbotswana.xlsxbouvet island.xlsxbrazil.xlsxbritish indian ocean territory.xlsxbritish virgin islands.xlsxbrunei darussalam.xlsxbulgaria.xlsxburkina faso.xlsxburundi.xlsxcabo verde.xlsxcambodia.xlsxcameroon.xlsxcanada.xlsxcanary islands.xlsxcaroline islands.xlsxcayman islands.xlsx
    I would like to get only the names (not the extension of the files) correctly spaced. Do you have any hint?

    Thanks in advance

    Dario

  • #2

    Code:
    display `"`f_names'"'
    should show a more readable result. Otherwise put, the problem here is in the display -- not the macro or how it was produced.


    You should be able to loop over the files (the extensions are unlikely to be a problem but can be removed if they are).


    Code:
    local names afghanistan.xlsx albania.xlsx algeria.xlsx
    
    foreach f of local names {
          di "`f'"
          local F : subinstr local f ".xlsx" "", all
          di "`F'"
     }
    
    afghanistan.xlsx
    afghanistan
    albania.xlsx
    albania
    algeria.xlsx
    algeria
    You may need to be more careful with spaces in filenames, but the local macro should have " " to protect those.
    Last edited by Nick Cox; 27 Nov 2023, 03:46.

    Comment


    • #3
      Nick Cox Thanks a lot. Indeed after fixing "display" it works.

      Comment


      • #4
        Well, the display didn't change the local macro. The point is that display without quotation marks didn't show what you wanted to see.

        Comment


        • #5
          Nick Cox Thanks again for your last reply. I have tried your suggestion and it works. Once I get the list of countries in the folder, I would like to import each excel file and to convert ihem in Stata format. With this in mind, I wrote:

          Code:
          clear
          
          local f_names: dir "C:\mydir" files "*"
          
          foreach f of local f_names {
                local F : subinstr local f ".xlsx" "", all
                
          }
          
          foreach f of local F {
              import excel "`f'", firstrow
              save `f', replace
              clear
          }
          Surprisingly (at least for me), only the last file in the folder (Zimbabwe) is loaded and converted. Apparently, there is something in the loop, but I cannot figurate what? Can you give me some suggestions?

          Best wishes,

          Dario

          Comment


          • #6
            Your loop structure is wrong. You need a single loop for trimming off the filename extensions and importing/saving the files. The way you have it,local macro F contains only the last filename by the time you get to your final loop. So it should be:
            Code:
            clear
            
            local f_names: dir "C:\mydir" files "*"
            local f_names: subinstr local f_names ".xlsx" "", all
            
            foreach f of local f_names {
                import excel "`f'", firstrow
                save "`f'", replace
                clear
            }

            Comment


            • #7
              Clyde Schechter thanks a lot. Clear and helpful as usual!

              Comment

              Working...
              X