Formatting confidence intervals in .csv using esttab

[anny fenton]

Hi All, This is an issue I've had when using esttab for several years that I've never been able to figure out: How to get the confidence interval results to post in a single cell when using esttab to export regression results to a .csv file. Instead, my confidence intervals values are broken up and posted into two cells (image below). I use the below code and can't find any mention of others dealing with this issue. Thanks in advance for solving a pet peeve!

esttab using "Regression tables.csv", b(2) ci(2) compress label replace

[Leonardo Guizzetti]

This isn't a problem with esttab or the resulting csv file, but rather is a conflict with how your spreadsheet program (probably, Excel) thinks the CSV should be interpreted and what it actually should be interpreted as.

For example

sysuse auto
logit foreign
esttab using "Regression tables.csv", b(2) ci(2) compress label replace
type "Regression tables.csv"

The last command simply types out the contents of the CSV file as text to the results window.

. type "Regression tables.csv"

="",="(1)"
="",="Car type"

="Car type",=""
="Constant",="-0.86***"
="",="[-1.36,-0.36]"

="Observations",="74"

="95% confidence intervals in brackets"
="* p<0.05, ** p<0.01, *** p<0.001"

Unfortunately, Excel doesn't play nice with standard CSV files, because it ignores that each cell is formatted as an equation (="contents here") and reads each line, character by character, until it encounters a delimiting character. The easiest trick I've been able to come up with is the save out the -esttab- using semicolon formatted CSV (-scsv- option). Then,open the .csv file in your favourite text editor and insert "sep=;" as the very first line. Excel will read this as an instruction to use the semicolon as the default separator and it will import the data correctly. This trick is described on the SuperUser forum here.

Alternatively, you may have better luck importing the CSV using commas with LibreOffice Calc, then saving out as an Excel file as needed. I'm not sure if this will work, but it's an idea to try.

[anny fenton]

Dear Leonardo,

Thank you for such a clear and helpful response! I thought it might be an Excel issue. I've tried your approach and now the confidence intervals are reported in a single cell! Unfortunately, however, now the variable and beta are also reported in a single cell, I assume because they are separated by commas rather than semicolon. Any ideas on how to fix this unintended consequence?

Thank you again!

[Leonardo Guizzetti]

You're welcome. If you have opted for export using -scsv- and not edited the resulting CSV file except the addition of the top line, I'm not sure why variable and beta are in the same cell, as I think they would be in separate cells and thus separated by a semicolon (again, guessing without a reproducible example). You can always manually edit the CSV file in a text editor to change it, but I'm not aware of an easier solution.