How to loop on all CSV files in several folders and create a separate dta files

[Hend She]

Hi ,
I am wondering How to loop on all CSV files in several folders and create a separate dta files .

for example :

Sample for input files :
C:\folder\country1\year1\filename1.csv
C:\folder\country1\year1\filename2.csv
C:\folder\country1\year1\filename3.csv

C:\folder\country2\year2\filename1.csv
C:\folder\country2\year2\filename2.csv
C:\folder\country2\year2\filename3.csv

Sample for output files :
C:\folder\country1\year1\filename1.dta
C:\folder\country1\year1\filename2.dta
C:\folder\country1\year1\filename3.dta

C:\folder\country2\year2\filename1.dta
C:\folder\country2\year2\filename2.dta
C:\folder\country2\year2\filename3.dta

foreach country in country1 country2 country3 { ;
foreach year in year1 year2 year3 { ;
foreach file in "filename*.csv" { ;

cd "C:\folder\`country'\`year'" ;

import delimited using "filename*.csv", case(preserve) clear ;
gen file ;
};
};
} ;

[William Lisowski]

If you are telling us you tried the code you posted and it did not work, then the following advice may help.

The backslash character

\

has meaning to Stata: it will prevent the interpretation of any following character that has a special meaning to Stata, in particular

`

will not be interpreted as indicating a reference to a macro.

But all is not lost: Stata will allow you to use the forward slash character in path names on any operating system, and Windows will not object. My understanding is that every version of Windows has accepted "/" as a path separator, and every version of MS-DOS beginning with DOS 2.0 (the first version that had subdirectories). Only in command lines was "/" not allowed, because it had already been used as a switch delimiter in MS-DOS 1.0.

. local country country1

. local year year1

. display "C:\folder\`country'\`year'"
C:\folder`country'`year'

. display "C:/folder/`country'/`year'"
C:/folder/country1/year1

So try changing the backslashes to forward slashes in your cd and import commands.

Added in edit: You also have a problem with the filenames. I will leave it to someone else to address that issue.

[Hend She]

William Lisowski Thanks for your prompt response
I tried to run the below code with forward but it gives me varlist not allowed , do you know why ?

foreach CC of local country { 
   foreach yy in local year { 
     cd "$csv_path/`CC'/`yy'" ;
     local list : dir . files "*.csv"
      foreach f of local list {
        di "`f'"
        import delimited "`f'"
        local stata_file = subinstr("`f'",".csv",".dta",.)
        save "$csv_path/`CC'/`yy'/$stata_file", replace    
    };
    
    };
} ;

Error:

> local stata_file = subinstr("`f'",".csv",".dta",.)
> save "$csv_path/`CC'/`yy'/$stata_file", replace
> };
varlist not allowed
r(101);

[Justin Niakamal]

You're declaring local stata_file, but then trying to save it as a global ($stata_file).

 save "$csv_path/`CC'/`yy'/`stata_file'", replace

As an aside, I'd recommend using filelist (from SSC). It makes this sort of task fairy simple. Take a look at the help file, there's a .csv example.

[Hend She]

Justin Blasongame I make the edit like what you said above but it still give me same error varlist not allowed
I have no Idea what SSC , I am still beginner , so I don't know what is SSC , I open the help but when no luck to find the example you are talking about , could you please write an example to explain what you mean ?

[Justin Niakamal]

In Stata type

ssc install filelist

then

help filelist

[William Lisowski]

I'm sympathetic to you as a new user of Stata - it's a lot to absorb.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. There are a lot of examples to copy and paste into Stata's do-file editor to run yourself, and better yet, to experiment with changing the options to see how the results change.

All of these manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu. The objective in doing the reading was not so much to master Stata as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

In your code in post #3, along with the problem Justin pointed out,

• foreach yy in local ... should be foreach yy of local ... just like the previous foreach
• either (a) all the semicolons are irrelevant or (b) you have previously done #delim ; in which case all your lines should end in semicolon, and the failure to do so may have caused your error message.
• Nevertheless, I would eliminate #delim ; and all the line-ending semicolons from your program: you do not need them for the code you show us. There are other ways of continuing commands onto multiple lines that you will learn in the recommended reading, or in the output of the help comment command.

[Justin Niakamal]

Perhaps you can follow this.

Consider this format --



You just need to change directory and ask Stata to list the files using filelist.

cd ../Raw
filelist

* this is a mac thing
drop if filename == ".DS_Store" 
gen path = dirname + "/" + filename 
levelsof path, local(tofile)

foreach x in `tofile' {
    import delimited using "`x'", clear
    local tosave = subinstr("`x'", ".csv", "", .)
    save "`tosave'", replace 

}

End result --