Convert multiple csv files into dta

[Fernando Bastidas]

Hello guys, I have almost 90 csv files which I'd like to transform into .dta each one of them separately. That's why I'm using a loop, however each .dta file created is not separated each column, it shows all of the columns in just one. I think that the problem is that I'm not separating the columns properly. The delimiter in the csv file is ";". I'd really appreciate your help.

This is my code:

cd "/Users/fernandobastidasespinoza/Desktop/Universidad/2022 S2/Tesis/Datos/Asistencia"
clear
local myfilelist : dir . files"*.csv"
foreach file of local myfilelist {
drop _all
insheet using `file', comma
local outfile = subinstr("`file'",".csv","",.)
save "`outfile'", replace
}

[Mike Lacy]

1) What version of Stata re you using? Your use of the obsolete -insheet- suggests you might be using an old version of Stata, which would influence an answer for your problem.

2) You say "each .dta file created is not separated each column." I'm guessing that you mean that the values of all the separate variables on each line of the CSV file are not being separated into separate variables, but are instead being read into just one variable. That is likely to be due to some issue with the format of your CSV file, perhaps misused commas or quotes, or something like that. So, to get a helpful answer, I'd encourage you to post here the first few lines of the data from your CSV file.

[Fernando Bastidas]

I just fixed the problem. Here's my new code:

cd "/Users/fernandobastidasespinoza/Desktop/Universidad/2022 S2/Tesis/Datos/Asistencia"
clear
local myfilelist : dir . files"*.csv"
foreach file of local myfilelist {
drop _all
insheet using `file', delimiter(";")
local outfile = subinstr("`file'",".csv","",.)
save "`outfile'", replace
}

However, from a total of 88 .csv files, it created only 66 files. I'm using Stata SE 17.

[Ali Atia]

It's very hard to diagnose the problem without additional detail. Are you seeing any errors messages come up? Can you identify any common trends across the 22 files?

To isolate the problem further, try inserting the following code within the loop after your final save command:

local check
local check: dir . files "`outfile'.dta"
if `"`check'"'==""{
    di "Fail"
    mac li
    pause on
    pause
}

The above code will pause the program every time a file fails to save. This will enable you to take a look at the active dataset and identify any possible causes leading to the failures.

More generally, have a look at the helpfiles for the export/import delimited command, which is likely preferable to outsheet/insheet for your use case.

[Hemanshu Kumar]

Providing us with a directory listing of your 88 file names would also help, and you can name examples of a few that are not being saved.

[Fernando Bastidas]

I couldn't find any common trends. I just added your code exactly where you told me, but now it stopped at the 68th file. Don't what can be causing the problem. Here's a screenshot of the stata console.

cd "/Users/fernandobastidasespinoza/Desktop/Universidad/2022 S2/Tesis/Datos/Asistencia"
clear
local myfilelist : dir . files"*.csv"
foreach file of local myfilelist {
drop _all
insheet using `file', delimiter(";")
local outfile = subinstr("`file'",".csv","",.)
save "`outfile'", replace

local check
local check: dir . files "`outfile'.dta"
if `"`check'"'==""{
    di "Fail"
    mac li
    pause on
    pause
}

}

[Ali Atia]

Please show us the output of the -mac li- command which is displayed when the program pauses on the 68th file. Additionally, when the program is paused, type in -describe- and show us the output of that command as well. In general, when showing output, pasting in the text between [CODE] [\CODE] delimiters is preferable to a screenshot.