Quotes do not match error in putexcel command

[Andrew Wade]

Hello,
I'm having terrible trouble with quotes supposedly not matching in a putexcel command.
This is placing heading row titles into an Excel sheet, which is being replicated many times.

putexcel `point_est_col_1'`row_heading'="`col_label_1'" `point_est_col_2'`row_heading'="`col_label_2'" ///
`point_est_col_3'`row_heading'="`col_label_3'" `point_est_col_4'`row_heading'="`col_label_4'"///
 `se_col_1'`row_heading'="`col_label_1'" `se_col_2'`row_heading'="`col_label_2'" ///
 `se_col_3'`row_heading'="`col_label_3'" `se_col_4'`row_heading'="`col_label_4'"  , font(Calibri,11,black) bold

All of the column and row values, along with the labels to be put into Excel are working fine, as per the following:

di "`point_est_col_1'`row_heading' = `col_label_1'"
di "`point_est_col_2'`row_heading' = `col_label_2'"
di "`point_est_col_3'`row_heading' = `col_label_3'"
di "`point_est_col_4'`row_heading' = `col_label_4'"
di "`se_col_1'`row_heading' = `col_label_1'"
di "`se_col_2'`row_heading' = `col_label_2'" 
di "`se_col_3'`row_heading' = `col_label_3'" 
di "`se_col_4'`row_heading' = `col_label_4'"

Which generates respectively:

B7 = Level 1 or below
C7 = Level 2
D7 = Level 3
E7 = Level 4 or 5
V7 = Level 1 or below
W7 = Level 2
X7 = Level 3
Y7 = Level 4 or 5

An earlier version of my code, where I inserted each label into Excel using individual putexcel commands worked fine. But this one, where I place 8 separate insertions into the one command is not working. The reason for putting the 8 insertions into one command is simply trying to increase processing speed.

Any ideas on what is wrong?

Regards,

Andrew

[Mike Lacy]

"///" is always supposed to be preceded by one or more blank spaces, but such are not present on your second line of code above. Perhaps this will help.

[Andrew Wade]

Hello,
Thanks for your response.
The good news is that the "quotes do not match" error message is no more.
The bad news is that I get this error:
" C7 = ": invalid cell name.

The revised code is:

putexcel `point_est_col_1'`row_heading' = "`col_label_1'"  `point_est_col_2'`row_heading' = "`col_label_2'"  ///
 `point_est_col_3'`row_heading' = "`col_label_3'"  `point_est_col_4'`row_heading' = "`col_label_4'"  ///
`se_col_1'`row_heading' = "`col_label_1'"  `se_col_2'`row_heading' = "`col_label_2'" ///
`se_col_3'`row_heading' = "`col_label_3'"  `se_col_4'`row_heading' = "`col_label_4'"  , font(Calibri,11,black) bold

Regards,

Andrew

[William Lisowski]

The fact that Stata seems to have identified "C7 =" rather than "C7" as the cell names makes me wonder if, in the process of adding spaces around your equal signs, you inadvertently added some non-printing character other than a space following the equal sign. When I did that using a non-printing space (on macOS, the option-space key combination) I got the following.

. putexcel C7 = "foo"
C7 : invalid cell name
r(198);

which looks almost like the error message you reported (did you copy it correctly?). The only way I could get what you report was

. putexcel "C7 =" "foo"
"C7 =": invalid cell name
r(198);

[Andrew Wade]

Hello,
I've found the problem.
In my code where I define local macros, I had:

local col_label_1 "Level 1 or below""

Instead of:

local col_label_1 "Level 1 or below"

Yes, I am embarrassed.

Regards,

Andrew

[William Lisowski]

Thanks for closing the loop. Let me add some sample code with a discussion following.

. clear

. set obs 1
number of observations (_N) was 0, now 1

. generate str10 a = " This is A"

. generate str10 b = " This is B"

. local a "Level 1 or below""

. macro list _a 
_a:             Level 1 or below"

. display "`a'"
Level 1 or below

. display `"`a'"'
Level 1 or below"

. local c1  "a `a' b  whatever"

. local c2 `"a `a' b  whatever"'

. macro list _c1 _c2
_c1:            a Level 1 or below" b whatever
_c2:            a Level 1 or below" b whatever

. display "`c1'"
a Level 1 or below This is Btoo few quotes
r(132);

. display `"`c2'"'
a Level 1 or below" b  whatever

.

This demonstrates a few things about macros.

First, you cannot count on the display command to display the contents of a macro. The display command tries hard to interpret its arguments as Stata syntax, as

. display a
 This is A

(run following the code above) demonstrates by interpreting a as a variable name and displaying the value in the first observation. The macro list command doesn't apply any interpretation to the contents of a macro.

A second point is that when you are surrounding a string with quotation marks, using the compound double quotes

`" and "'

gives you a result that is robust to the presence of quotation marks within the string. See section 12.4.6 of the Stata User's Guide PDF included with your Stata installation and accessible through Stata's Help menu for a fuller discussion of these. In short, compound double quotes provide distinct "opening" and "closing" versions, allowing Stata to match them, like nested pairs of parentheses.

But the definitions of the local macros c1 and c2 show that compound double quotes aren't always necessary. Nevertheless, good Stata programming practices uses them whenever the string between the quotation marks contains macro references, because the confusion (as in your post #4) engendered by mismatched quotation marks can be profound.

So finally we see that

display "`c1'"

is interpreted as

display "a Level 1 or below" b whatever"

and Stata displays "a Level 1 or below" followed by the value of b in the first observation, and then stumbles on the construct whatever". With compound double quotes,

display "`c2'"

is displayed as a single string constant.

The bottom line is, don't be embarrassed by the typographical error - everyone makes them. I've shown some techniques that help figure out what's gone wrong.

When presenting on Statalist (or any other forum) a good idea (and one that is suggested in the Statalist FAQ) is to produce a minimal example that reproduces the problem. For post #4 this would have been something like

// fake data
clear
set obs 1
generate point_est_col_1 = 42
generate point_est_col_2 = 666
// problem begins here
local point_est_col_1 "B7"
local col_label_1 "Level 1 or below""
local point_est_col_2 "C7"
local col_label_2 "Level 2"
putexcel set test
putexcel `point_est_col_1'`row_heading'="`col_label_1'" `point_est_col_2'`row_heading'="`col_label_2'"

which yields

. putexcel `point_est_col_1'`row_heading'="`col_label_1'" `point_est_col_2'`row_heading'="`col_label_2'"
" C7=": invalid cell name
r(198);

And either (a) someone with a fresh set of eyes would have noticed the typographical error immediately, or (b) in the process of producing this lean example you would have noticed the typographical error.

[Andrew Wade]

Hi William,
Thanks for this exposition. The lesson for me is that I should always trace back the sources of a problematic line of code.
Regards,
Andrew