Exporting and preserving parentheses

[Anne Todd]

Hi Statalist, I have a dataset that I need to export to csv. For a few variables, the values are numbers enclosed in () (so like (0.43) or (1.45)), and they are saved as strings.

But my issue is that the numbers that are enclosed in () do not actually have the () around them after exporting, it just exports the number alone when I open the csv file. I've tried a few various export options to no avail. Any ideas?


EDIT: I've realized this may be more of an Excel problem--it seems like Excel's default is to treat numbers in () as negative numbers, for some reason. But trying to figure out if I can get Stata to tell Excel that these aren't numbers when exporting, basically.

[Andrew Musau]

You do not show your commands, but you want to output (-199.50) as

="(-199.50)"

[Hemanshu Kumar]

As you said, this is really an Excel problem. It can be fixed if you use the "Import" option in Excel's File menu, then in the Text Import Wizard dialogue where you can set the data format for each column, select each of these variable columns in turn, and set each of their formats to "Text".

[William Lisowski]

If you are using export delimited to create your csv file, then you want the quote option to ensure that all strings are surrounded with quotation marks, even if they don't contain spaces or commas.

help export delimited

The reason Excel treats numbers surrounded in parentheses as negative is because as Wikipedia tells us

In bookkeeping, amounts owed are often represented by red numbers, or a number in parentheses, as an alternative notation to represent negative numbers.

and Excel was developed as a bookkeeper's tool, not a scientists's tool.

[Hemanshu Kumar]

William Lisowski I tested this earlier, and even if the CSV has quotation marks surrounding a column of numbers, some of which are in parentheses, the ones in parentheses get converted into negative numbers in Excel if you directly open the CSV rather than going through the Import option as I suggested above in #3.

I don't know if this is different across platforms. I am using MS Excel for Mac version 16.65.

The solution in #2 also works, however.

[Andrew Musau]

If your command is export delimited, then specify the option

export delimited myfile.csv, replace datafmt

[Hemanshu Kumar]

Andrew Musau when I try the following

clear
input str3 x byte y
"(1)" 2
"2" 4
"3" 6
"4" 8
"5" 10
"6" 12
end

export delimited myfile.csv, replace datafmt

and open the resulting CSV file in Excel directly, the (1) is still read as -1.

[William Lisowski]

My post #4 crossed with post #3 from Hemanshu Kumar and I agree entirely with the conclusion that this is an Excel problem, not a Stata problem. Or more generally, it seems to be a convention about the handling of CSV data including numbers surrounded by parentheses observed by bookkeeping programs, because Numbers displays the same behavior as Excel. Bookkeeping 2 Science 0.

With that said, if the objective is to prepare a file to be used by Excel, then perhaps a better option is

export excel

[Andrew Musau]

Andrew Musau when I try the following

clear
input str3 x byte y
"(1)" 2
"2" 4
"3" 6
"4" 8
"5" 10
"6" 12
end

export delimited myfile.csv, replace datafmt

and open the resulting CSV file in Excel directly, the (1) is still read as -1.

You are correct. Applying my suggestion in #2, the following should work:

clear
input str3 x byte y
"(1)" 2
"2" 4
"3" 6
"4" 8
"5" 10
"6" 12
end

replace x= `"=""' + x +`"""'
export delimited myfile.csv, replace

Res.:

[William Lisowski]

Post #7 demonstrates more than it claims, because not only is "(1)" read as -1 by Excel, but so are all the other numbers enclosed in quotation marks.

I think this shows where our thoughts are not aligned with spreadsheets. To Excel and Numbers, the quotation marks in the CSV are meant not to indicate strings, but to surround a token that may contain the character being used as a delimiter.

The solution from Andrew Musau works because it creates an Excel formula that evaluates to the desired string rather than to a number.