Remove space from file name

[Aditi EPRI]

Hello all,

I am working with a bunch of excel files and wish to re-save them without any spaces in the file name. Have tried to run the following code, however i encountered an error with "type mismatch". I am not sure how to fix this. Please advise.

Below is a picture of the folder and filenames that Stata is reading

set more off
clear
local myfiles: dir "C:\Users\Eprifellow\Dropbox (EPRI)\EPRIProject2019UNICEFMontenegro\Deliverables\Part4\SWIS" files "*.xlsx"

foreach file of local myfiles {
    local subfile = substr("`file'"," ", "")
    !rename "`file'" "`subfile'"
}

Below is the error code runs into

. local myfiles: dir "C:\Users\Eprifellow\Dropbox (EPRI)\EPRIProject2019UNICE
> FMontenegro\Deliverables\Part4\SWIS" files "*.xlsx"

. 
. foreach file of local myfiles {
  2.         local subfile = substr("`file'"," ", "")
  3.         !rename "`file'" "`subfile'"
  4. }
- foreach file of local myfiles {
- local subfile = substr("`file'"," ", "")
= local subfile = substr("1  Number of all users by CSW-DEC-18.xlsx"," ", "")
type mismatch
  !rename "`file'" "`subfile'"
  }
r(109);

end of do-file

r(109);

[Nick Cox]

You mean subinstr() not substr().

[Aditi EPRI]

Hello Nick,

Thank you for your message.

I changed the code as you indicated however, get the "invalid syntax" error.

set more off
clear
local myfiles: dir "C:\Users\Eprifellow\Dropbox (EPRI)\EPRIProject2019UNICEFMontenegro\Deliverables\Part4\SWIS" files "*.xlsx"

foreach file of local myfiles {
    local subfile = subinstr("`file'"," ", "")
    shell rename "`file'" "`subfile'"
}

What would you suggest?

[Mike Lacy]

-help subinstr()- reads as follows:

"subinstr(s1,s2,s3,n)
Description: s1, where the first n occurrences in s1 of s2 have been replaced with s3"

You omitted the "n" specification. Note per the help that using "missing" for n will replace all occurrences, e.g., subinstr("`file'"," ", "", .)

[Aditi EPRI]

hello Mike Lacy, I did not get the chance to thank you. Apologies for the delay! Tried to run your code - it worked when i moved the files out of dropbox. Thank you so much!