Exporting from stata to excel

[wesso dadoyan]

Hi ,

I am exporting a dta file to excel. I have 506861 rows and 30 columns.
when i choose the command export data to excel I receive "too many or no observations specified" R(198)
what could have went wrong?

[Joseph Coveney]

At Stata's command line, type

help export excel

and scroll down to the section titled "Technical note: Excel data size limits".

Change the target Excel file type to .xlsx, and see whether that helps. (It might take a while to export unless you've got a lot of memory available.)

[wesso dadoyan]

but it should work since the size limit is 1 million rows and 16348 columns. I have less rows and columns

[wesso dadoyan]

solved. i had to put under with the file name xlsx. it seems the default is xls

[Dirk Enzmann]

Trying to export Stata data with 704 rows and 6 columns to excel using -export excel- into an .xlsx-file I encountered the error

         _xlshwritestr():  9901  Stata returned error
export_excel_write_file():     -  function returned error
export_excel_export_file():     -  function returned error
                 <istmt>:     -  function returned error
r(9901);

The limits in -help export excel- says that for an Excel .xlsx-type workbook the string size limit is 32,767 characters. In my case the maximum number of characters of one record of a string variable is 2,143 which is less than the limit of 32,767. Did I encounter another limit not specified in -help export-?

I can't show a data example using -dataex- because the data width exceeds the maximum linesize allowed by -dataex-. Below a data example created by hand:

clear
input strL labstr
`""level 1 (US)" "not available (US)" "level 1 (RU)" "not available (RU)" "lower vocational (NL)" "VMBO (NL)" "VMBO theoretical (NL)" "VMBO practical (NL)" "AVO (NL)" "HAVO (NL)" "VWO (NL)" "not available (NL)" "A general 7 (BE)" "B pre-vocational 7 (BE)" "A general 8 (BE)" "B pre-vocational 8 (BE)" "ASO general 9 (BE)" "BSO vocational 9 (BE)" "TSO technical/art. 9 (BE)" "not available (BE)" "special education school (FR)" "non special education school (FR)" "not available (FR)" "secondary compulsory (ES)" "college (ES)" "vocational training (ES)" "not available (ES)" "general (primary) school (HU)" "technical school (HU)" "vocational secondary school (HU)" "general secondary school (HU)" "not available (HU)" "scuola media (IT)" "istituto professionale (IT)" "mag./sociopiscopedagogico (IT)" "istituto tecnico (IT)" "liceo classico, scientifico (IT)" "altri licei (escl.class,scient) (IT)" "not available (IT)" "not available (CH)" "lower secondary (AT)" "higher secondary (AT)" "pre-vocational (AT)" "higher tech & voc (AT)" "intermed. tech & voc (AT)" "not available (AT)" "type 1 (DK)" "not available (DK)" "grundskola (SE)" "not available (SE)" "type 1 (NO)" "not available (NO)" "level 1 (PL)" "not available (PL)" "Hauptschule (DE)" "IHR (DE)" "Regelschule (DE)" "Realschule (DE)" "Gesamtschule (DE)" "Gymnasium (DE)" "not available (DE)" "type 1 (VE)" "not available (VE)" "HAVO/VWO (AW)" "MAVO (AW)" "not available (AW)" "col�gio (PT)" "b�sica 2� e 3� (PT)" "b�sica integrada (PT)" "secundaria com 3� (PT)" "externato (PT)" "instituto (PT)" "not available (PT)" "level 1 (IE)" "not available (IE)" "type 1 (IS)" "not available (IS)" "not available (CY)" "level 1 (FI)" "not available (FI)" "gimnazija (LT)" "pagrindine (LT)" "vidurine (LT)" "not available (LT)" "level 1 (EE)" "not available (EE)" "level 1 (AM)" "level 2 (AM)" "not available (AM)" "level 1 (SI)" "not available (SI)" "not available (BA)" "level 1 (CZ)" "level 2 (CZ)" "not available (CZ)" "Atheneum (SR)" "Mulo (SR)" "LBGO (SR)" "ETS/LTS (SR)" "ETO/ENO/VSO (SR)" "not available (SR)" "HAVO (AN)" "HAVO/VWO (AN)" "VSBO (AN)" "VWO (AN)" "not available (AN)""'
end
export excel "test.xlsx", first(var) replace

Unfortunately, my example does not reproduce the error I encounter when using my original data.

[Joseph Coveney]

. . . I encountered the error
r(9901);

. . . Did I encounter another limit not specified in -help export-?

According to the user manual's entry for errors, a return code like that would be more serious:

"9xxx. Various messages, all indicating an unexpected system failure. You should never see such a message. If one occurs,
save your data, and exit Stata immediately. Please email [email protected] to report the problem."

[Dirk Enzmann]

The problem described in #5 is solved. The dataset contained invalid UTF-8 characters. After running -unicode translate- the data can be exported into Excel without errors.