Recording the name of the excel spreadshee​t and worksheet

[Damian_F]

Hi,

I am importing a series of excel docs into stata. Each excel doc has worksheets (two to be exact).

The code I have come up with does not work - and is below. It am trying to generate one variable that records the name of the excel document and another for the name of the sheet. ~~Any assistance would be much appreciated.

cd "~"
local allfiles : dir "." files "*.xls"
tempfile name
save `name', emptyok
foreach f of local allfiles {
import excel using `f', clear
gen source = "`f'"
local n_sheets `r(N_worksheet)'

forvalues j = 1/`n_sheets' {
local sheet`j' `r(worksheet_`j')'
}
forvalues j = 1/`n_sheets' {
gen source1 = `"`sheet`j''"'
append using `name'
save `"`building'"', replace
}

append using `name'
save `name', replace
}
Thanks,
Damian

[Nick Cox]

On "does not work", please see FAQ Advice Sections 12 (and 17). (Section 6 also applies.)

One possible problem here is that your (full) filenames include spaces. If so, you will need to include them in double quotation marks. That is likely to apply to uses of

save
import excel
append

[Damian_F]

Apologies for the vague post - it is my first on this forum.

As mentioned, I am trying to import a range of excel files which form part of a time series. Each worksheet contains data by the distribution channel. (E.g. Worksheet 1 has data for Distribution channel A, Worksheet 2 has data for Distribution channel B). The names of the worksheets have spaces in them. The (full) filenames of the excel files have no spaces.

I am using Stata 13.1

Unfortunately, I am not able to use the code to generate the variables: source or source 1. This is because when I run the above code on Stata, the error code received is:
invalid syntax
r(198);


Many thanks,
Damian

[Nick Cox]

But which command provokes that error message?

Looking at your code, I see other possible problems. Second time around this loop,

forvalues j = 1/`n_sheets' {
gen source1 = `"`sheet`j''"'
append using `name'
save `"`building'"', replace
}

doesn't source1 already exist?

Where is the local macro building defined in your code?

[Clyde Schechter]

One other problem with your code is that -import excel using `f', clear- will always read in the first worksheet in `f'. So if you got this code to run, instead of reading in each of the worksheets, it would read in only the first in each file and repeatedly append it to the file you are trying to build up, though it will (mis)identify the information as coming from the various worksheets based on the variable source1. It's possible, I suppose, that this is what you want to do, but it seems unlikely.

It isn't completely clear where your syntax error is coming from. But Nick's observation that you appear to be using an undefined local macro `building' in your -save- statement is a good possibility. You probably meant to use `name' there. If that doesn't solve the syntax error problem, try running it with trace set on so you can see where it's breaking.