Forloop to import multiple .sav files not working

[Aditi EPRI]

Hello all, would appreciate an advise on how i can fix the following line of commands to import/read multiple SPSS files in a directory and save each as .dta file. The commands below do not produce any output or give an error message --

global dir "C:\Users\mydesktop"
local fls: dir "$dir" files "*.sav"
global save "$dir/STATA"

foreach f of local fls{
usespss `"`fls'"' , clear
save "$save/`"`f'"'.dta", replace

}

[Jorrit Gosens]

Not tested because this is a bit tricky to replicate, but:
- you should refer to the individual element and not the list of elemnets (i.e., f, not fls)
- youll probably need to strip the .sav extension from the filename to save it
- there's no need to specify .dta when saving, but it doenst hurt

foreach f of local fls{
usespss `"`f'"' , clear
local savefile = subinstr("`f'",".sav","",.) 
save "$save/`"`savefile'"'.dta", replace
}

[Nick Cox]

You're lucky not to be my student as I would have sharp words in your best interests about using globals and the incorrect name STATA. However, although I can't see your files --- or even their names, which would help mightily in debugging this -- I don't think either of those style lapses is biting you,

As you got nothing my best guess is that the foreach loop cannot see the local macro, so it is happily looping over an empty set. This can happen whenever you are issuing code in chunks from a do-file editor window or -- more generally -- will happen whenever the local macro is defined in a different place from where you refer to it. That's what local means. It's on all fours with whatever is said in a soundproof room being inaudible outside,

Beyond that I have not used SPSS since the 20th century and am not familiar with usespss (SSC, as you are asked to explain) but my best guess is that your code inside the loop needs to be quite different, say

global dir "C:\Users\mydesktop"
local fls: dir "$dir" files "*.sav"
global save "$dir/STATA"

foreach f of local fls {
    usespss `"`f'"' , clear
    save `"$save/`f'.dta"', replace
}

You also need to be working within the right directory.

EDIT: Jorrit Gosens made some of the same points, and more.

[Aditi EPRI]

Thanks Jorrit Gosens Nick Cox appreciate your response. Unfortunately, Jorrit's codes did not work .

Thought it will be helpful to attach the file names. I hope the contents of image are legible?

In case not, ahead is name of one of the SPSS files that i have tried to read

"Consumption of cereals-pulses- milk and milk products during the last 30 days - Block 5.1- 5.2- 6 - Level 5 - type 2 - 68.sav"


Tried two sets of forloops, that only read the files into Stata, did not work:

local fls: dir "C:\Users\Eprifellow\Dropbox(EPRI)\EPRIProject2016UNICEFHyderabadSSPS\Microsim\Nss68_1.0_Type2_new format" files "*.sav"
foreach f of local fls{
 
save "$save/`"`f'"'.dta", replace

}

foreach f of local fls{
usespss `"`f'"' , clear
local savefile = subinstr("`f'",".sav","",.)
}

I hope you can provide more suggestions.

[Aditi EPRI]

Edit to above

foreach f of local fls{
usespss `"`fls'"' , clear
}

[Nick Cox]

#4 and #5 are both wrong as already pointed out.

In #5 it is the individual file name held in local macro f which you want to use within the loop, not the whole kit and caboodle.

You have that right in #4 but

 
 "$save/`"`f'"'.dta"

won't I think work and -- immodesty aside -- I would still bet on my code over Jorrit's in that particular detail. Jorrit had a good point about stripping the .sav extension. That being so, I suggest

global dir "C:\Users\mydesktop"
local fls: dir "$dir" files "*.sav"
cd $dir 
global save "$dir/STATA"

foreach f of local fls {
    usespss `"`f'"' , clear
    local savefile = subinstr(`"`f'"',".sav","",.) 
    save `"$save/`savefile'.dta"', replace
}

[Jorrit Gosens]

Some small changes:

foreach f of local fls{
cd $dir
usespss  `"`f'"' , clear
local savefile = subinstr("`f'",".sav",".dta",.)
cd $save
save `"`savefile'"', replace
}

I cannot really figure it out or explain, but one issue was with the parsing of compound quotes following a slash. So changing the code above to

save $save/`"`savefile'"', replace

will not work.
I also could not immediately figure out why the addition of .dta in the line with save wasnt being read right, so I put it in the local savefile line. Contrary to what I said, you do need it with your filenames.
Free tip for future projects, although you are probably aware by now: much of this sort of code would be a bunch easier if the filenames were a little simpler.


Edit: typed whilst Nick was also writing. His better understanding of quotes and compound quotes makes for fewer lines of code.

[Kye Lippold]

A general suggestion for Aditi: if you type -set trace on-, Stata will report everything done within your loop in great detail, showing you the actual commands that are being run. If you do this, you will see right away why you don't want to use the `fls' macro, but want the `f' macro instead, as Jorrit and Nick pointed out. This is really important when learning to debug loops on your own. (Type -set trace off- to turn it off).

Also, FYI: if you have Stata16, the -import spss- command is a new alternative to -usespss-.

[Aditi EPRI]

Big thanks to all of you.

I understand that the file naming is very problematic. We have exported the files from a government source therefore, couldn't help with the names. Had to take them as it is.

Kye Lippold thank you for bringing up "set trace on". It did not run the loop at all that's why thought of using the forum.

I ran these codes:
#1

clear
set more off
global dir "C:\Users\Eprifellow\Dropbox (EPRI)\EPRIProject2016UNICEFHyderabadSSPS\Microsim\Nss68_1.0_Type2_new format"
local fls: dir "$dir" files "*.sav"
cd "$dir" 
global save "$dir/Stata"

foreach f of local fls {
    usespss `"`f'"' , clear
    local savefile = subinstr(`"`f'"',".sav","",.) 
    save `"$save/``savefile''.dta"', replace
}

Nick Cox please notice that i made a minor change to the last line of the loop - adding extra quotes around ( ``savefile'') than in your codes. When i ran your codes w/o tweaking anything, the loop encountered an error. While it read the fourth file in the directory to Stata it could not save it -- i got the error "file could not be opened".

I tried isolating the file to a different folder -- and running the codes again by setting the directory to its new location and still the same error. Then i added the quotes, and the loop worked. Oddly though, the dataset saves w/o a name. It is a dta file but no name appears on it in the folder (it doesnt take the name as it SPSS file).

Name of the file-- "Expenditure for purchase and construction (including repair and maintenance) of durable goods for domestic use- Block 11 - Level 9 - Type 2 - 68.sav"

so this file caused the loop to break.

Jorrit Gosens i ran your codes too; same problem with the same file -- i tried to tweak last line the same way i did above, got an error saying incorrect file specification.

clear
set more off
global dir "C:\Users\Eprifellow\Dropbox (EPRI)\EPRIProject2016UNICEFHyderabadSSPS\Microsim\Nss68_1.0_Type2_new format"
local fls: dir "$dir" files "*.sav"
cd "$dir" 
global save "$dir/Stata"

foreach f of local fls{
cd "$dir"
usespss  `"`f'"' , clear
local savefile = subinstr("`f'",".sav",".dta",.)
cd "$save"
save `"`savefile'"', replace
}

[Jorrit Gosens]

Not sure. I am trying this with some similarly named csv and import delimited, but that shouldn't make for a difference.
Bit of a guess but it could be a dropbox issue. Try exiting dropbox (or stop syncing) and then run it. Another possibility is dropbox does not allow such long filenames. Alternatively, as a test, try saving to a non-synced location.

[Aditi EPRI]

Jorrit Gosens thank you so much. Yes, saving it locally worked. The loop ran smoothly. Thank you once again!

[Sergio Rivaroli]

Dear Nick Cox , Aditi EPRI and Jorrit Gosens , I've tried to replicate the Code in #9, but it doesn't produce noting in STATA directory...

[Nick Cox]

Sorry to hear in #12 that you have had problems, but to comment helpfully I for one need more details about what you tried -- and naturally I need some independent evidence about what exists in your directory.

[Sergio Rivaroli]

Dear Nick Cox,
I've simulated having two .sav files and a folder "Stata" on my desktop ( C:\Users\sergio.rivaroli\OneDrive - Alma Mater Studiorum Università di Bologna\Desktop, and C:\Users\sergio.rivaroli\OneDrive - Alma Mater Studiorum Università di Bologna\Desktop\Stata).
As suggested by Kye Lippold, I've adopted the command - import spss - instead of usespss as follows, but nothing happens in Stata folder created on my desktop.
I Apologise for bothering you, but it is very helpful for me; where have I've wrong?

clear
set more off
global dir "C:\Users\sergio.rivaroli\OneDrive - Alma Mater Studiorum Università di Bologna\Desktop"
local fls: dir "$dir" files "*.sav"
cd "$dir" 
global save "$dir/Stata"

foreach f of local fls{
cd "$dir"
import spss  `"`f'"' , clear
local savefile = subinstr("`f'",".sav",".dta",.)
cd "$save"
save `"`savefile'"', replace
}

[Nick Cox]

I've looked through your code and can't see a problem, but I can't test it. You may need to pepper your code with displays of this and that:

macro list

will list visible local and global macros. Are you executing the code all at once, or line by line from a do-file editor? That would be a source of problems.