Importing Excel spreadsheet that contains a "$" symbol.

[Justin Niakamal]

Hi Everyone,

I'm trying to write a loop to bring in some Excel spreadsheets, but I'm having issues with the "$" symbol and I don't want to have to rename a bunch of spreadsheets manually.

I have something along the lines of US$SF2007.xls, US$SF2008.xls, ... , US$SF2017.xls (this is for example's sake, I actually have a bunch of areas and am creating a vector of areas to loop through within a forvalues loop). I think the $ is getting interpreted as a global. Does anyone have any solutions to this?

[Carole J. Wilson]

If you were to type out the command with the filename containing "$" (which you don't want to do!), you would do the following:

import excel using US\$SF2007.xls

You can have Stata do this for you in your loop:

dir *.xls
local files: dir . files "*.xls"
foreach f of local files {
    clear
    local x: subinstr local f "$" "\\$", all
    noi di "`x'"
    import excel using "`x'"
    }

You could also choose to rename all of your files to remove the "$" using a similar logic:

local files: dir . files "*.xls"
foreach f of local files {
    clear
    local x: subinstr local f "$" "\\$", all
    local y: subinstr local f "$" "", all
    !ren "`x'" "`y'"
    }

[Justin Niakamal]

Thanks Carole,

I'm still getting errors with either of those.

With the first batch I get the error "file US.xls not found"

With the second loop I get "/bin/bash: rename: command not found"

Any thoughts?

[William Lisowski]

With the second loop I get "/bin/bash: rename: command not found"

So you're not working on a Windows system? On linux and macOS, mv is the system command that is the equivalent of the Windows rename command. Change

!ren "`x'" "`y'"

to

!mv "`x'" "`y'"

and let us know what the result is.

[Justin Niakamal]

I'm getting this message (for each spreadsheet) , but the files aren't any different. Am I doing something wrong?

Example:

mv: rename Hanf.xls to HanfSF2012.xls: No such file or directory

[Justin Niakamal]

Update, I've figured it out. Thank you both for your help!

[William Lisowski]

No, you are not doing anything wrong. I think the problem may be that Carole's approach was developed on Windows (I'm guessing) but when run on unix-based systems like linux or macOS, the dollar sign is not only meaningful to Stata, but then it is meaningful to the shell as well. So if you sneak the dollar sign past Stata into the command to be executed, the shell then treats it exactly as Stata would.

I have the odd feeling I walked down a path like this before, and did not achieve a satisfactory answer. I have no idea where your data came from, but is there any possibility you could somehow recreate it without the dollar sign? Meanwhile, I'll experiment further.

[William Lisowski]

We're glad you figured it out. To thank us more helpfully, please post the solution so it is available to others.

The Statalist Forum is not a help desk. It is a community of Stata users who are sharing what they know and don't know and learning together. So when we find a solution to our own problem, the norm is to continue the thread and show the solution.

[Calvin Chen]

Thanks Carole,

I'm still getting errors with either of those.

With the first batch I get the error "file US.xls not found"

With the second loop I get "/bin/bash: rename: command not found"

Any thoughts?

Hi, I have met the same error with the first batch, how did you solve it? Thank you

[Justin Niakamal]

This was an old thread that I never closed out, which was my mistake. I was a "new" poster here and didn't read the FAQ! Unfortunately the error I encountered occurred in Stata 12, but not 15 (I was using 12 because it was MP). Are you using the latest version of Stata?

[Calvin Chen]

This was an old thread that I never closed out, which was my mistake. I was a "new" poster here and didn't read the FAQ! Unfortunately the error I encountered occurred in Stata 12, but not 15 (I was using 12 because it was MP). Are you using the latest version of Stata?

Yes, I am using Stata 16 IC, I faced with the exact output which is "file US.xls not found" (mine is "file .csv not found"), did you just worked out well?

[Justin Niakamal]

Here's one solution using filelist (from SSC). You can list the files with a "$" and split them and create a new complete filename. Might be an easier way (I don't have access to the old code I was using), but here's a workable example

clear all

!curl -l 'https://www.labormarketinfo.edd.ca.gov/file/occup$/oeswages/alloes2010.zip' > alloes2010.zip
unzipfile alloes2010.zip

filelist, pattern(*xls)
keep if regexm(filename, "\\$")

split filename, parse("$")

forvalues x = 1/`=_N' {

    local fixed_name = filename1[`x'] + "\\$" +  filename2[`x']
    import excel using "`fixed_name'", clear
    

}

[Calvin Chen]

Here's one solution using filelist (from SSC). You can list the files with a "$" and split them and create a new complete filename. Might be an easier way (I don't have access to the old code I was using), but here's a workable example

clear all

!curl -l 'https://www.labormarketinfo.edd.ca.gov/file/occup$/oeswages/alloes2010.zip' > alloes2010.zip
unzipfile alloes2010.zip

filelist, pattern(*xls)
keep if regexm(filename, "\\$")

split filename, parse("$")

forvalues x = 1/`=_N' {

local fixed_name = filename1[`x'] + "\\$" + filename2[`x']
import excel using "`fixed_name'", clear


}

Thank you for the codes, unfortunately, it didn't work in my Mac, the error is "filenmae1 not found"

One of my file name is $ROPE.csv

dir *.csv
local files: dir . files "*.csv"
foreach f of local files {
clear
local x: subinstr local f "$" "\\$", all
noi di "`x'"
insheet using "`x'", clear
}

the error is "\.csv" is not found, just like yours, the str after $ all disappeared

[Justin Niakamal]

I am also using a mac. Note you'll want to use import delimited in place of insheet.

Here's an example where I create a $ROPE.csv file and read it in:

clear all

sysuse "auto.dta", clear
outsheet using "\$ROPE.csv", replace

clear
filelist, pattern(*ROPE.csv)

split filename, parse("$")

forvalues x = 1/`=_N' {

    local fixed_name = filename1[`x'] + "\\$" + filename2[`x']
    import delimited using "`fixed_name'", clear
}
(12 vars, 74 obs)

From the list above:

. filelist, pattern(*ROPE.csv)
Number of files found = 1

. list

     +-----------------------------+
     | dirname   filename    fsize |
     |-----------------------------|
  1. | .         $ROPE.csv   4,761 |
     +-----------------------------+

[Calvin Chen]

I am also using a mac. Note you'll want to use import delimited in place of insheet.

Here's an example where I create a $ROPE.csv file and read it in:

clear all

sysuse "auto.dta", clear
outsheet using "\$ROPE.csv", replace

clear
filelist, pattern(*ROPE.csv)

split filename, parse("$")

forvalues x = 1/`=_N' {

local fixed_name = filename1[`x'] + "\\$" + filename2[`x']
import delimited using "`fixed_name'", clear
}
(12 vars, 74 obs)

From the list above:

. filelist, pattern(*ROPE.csv)
Number of files found = 1

. list

+-----------------------------+
| dirname filename fsize |
|-----------------------------|
1. | . $ROPE.csv 4,761 |
+-----------------------------+

First, I would like to thank you for your effect, please see that I edited my posts.

But still, it is not working, the error is "file \.csv not found", if it can be done in your Mac, maybe it was my personal problem.

Even though it didn't work out, but still, thank you a lot.