import excel — Import and export Excel files

1 Excel Import and export Excel filesDescriptionQuick startMenuSyntaxOptions for Import excelOptions for export excelRemarks and examplesStored resultsReferencesAlso seeDescriptionimport excelloads an Excel file, also known as a workbook, into excelfilename,describelists available sheets and ranges of an Excel excelsaves data in memoryto an Excel file. Excel 1997/2003 (.xls) files and Excel 2007/2010 (.xlsx) files can be imported,exported, and described usingimport Excel , export Excel , andimport Excel , excelandexport excelare supported on Windows, Mac, and excelandexport excellook at the file extension,. , to determine whichExcel format to read or performance, Import excelimposes a size limit of 40 MBfor Excel 2007/2010 (.xlsx)files. Be warned that importing can severely affect your machine s Excel autofirst looks then looks notfound in the current default file extension forexport a file extension is not startCheck the contents of Excel importingimport Excel mydata, describeAs above, but Excel , describeLoad data Excel mydataAs above, but load data from cells A1:G10 ofmysheetimport Excel mydata, cellrange(A1:G10) sheet(mysheet)Read first row as lowercase variable namesimport Excel mydata, firstrow case(lower) Import onlyv1andv2import Excel v1 v2 using mydataSave data in memory Excel mydataAs above, but export variablesv1,v2, andv3export Excel v1 v2 v3 using mydata12 Import Excel Import and export Excel filesMenuimport excelFile> Import > Excel spreadsheet (*.)

2 Xls;*.xlsx) export excelFile> export >Data to Excel spreadsheet (*.xls;*.xlsx)SyntaxLoad an Excel fileimport Excel [using]filename[,importexceloptions ]Load subset of variables from an Excel fileimport excelextvarlistusingfilename[,importexce loptions]Describe contents of an Excel fileimport Excel [using]filename, describeSave data in memory to an Excel fileexport Excel [using]filename[if][in][,exportexce loptions]Save subset of variables in memory to an Excel fileexport Excel [varlist]usingfilename[if][in][,exp ortexceloptions]importexceloptionsDescri ptionsheet("sheetname") Excel worksheet to loadcellrange([start][:end]) Excel cell range to loadfirstrowtreat first row of Excel data as variable namescase(preserve|lower|upper)preserve the case (the default) or read variable namesas lowercase or uppercase when usingfirstrowallstring[("format")] Import all Excel data as strings.

3 Optionally, specify thenumeric display formatclearreplace data in memorylocale("locale")specify the locale used by the workbook; has noeffect on Microsoft Windowsallstring("format")andlocale()do not appear in the dialog Excel Import and export Excel files 3exportexceloptionsDescriptionMainsheet( "sheetname"[, modify|replace])save to Excel worksheetcell(start)start (upper-left) cell in Excel to begin saving tofirstrow(variables|varlabels)save variable names or variable labels to first rownolabelexport values instead of value labelskeepcellfmtwhen writing data, preserve the cell style andformat of existing worksheetreplaceoverwrite Excel fileAdvanceddatestring("datetimeformat") save dates as strings with adatetimeformatmissing("repval")save missing values asrepvallocale("locale")specify the locale used by the workbook; has noeffect on Microsoft Windowscollectis allowed withimport Excel .

4 See[U] Prefix ()does not appear in the dialog variable names of imported columns. Anextvarlistis one or more of any of thefollowing:varnamevarname=columnnameEx ample: Import Excel make mpg weight price using , clearimports columnsA, B, C, and D from the Excel : Import Excel make=A mpg=B price=D using , clearimports columnsA, B, and D from the Excel Column C and any columns after D are for Import excelsheet("sheetname")imports the worksheet namedsheetnamein the workbook. The default is toimport the first ([start][:end])specifies a range of cells within the worksheet to specified using standard Excel cell notation, for example,A1,BC2000, that the first row of data in the Excel worksheet consists of variable names. Thisoption cannot be used the first row of the cell range for variablenames ifcellrange()is exceltranslates the names in the first row to validStata variable names.

5 The original names in the first row are stored unmodified as variable (preserve|lower|upper)specifies the case of the variable names read when using thefirstrowoption. The default iscase(preserve), meaning to preserve the variable name theASCII letters in names are changed to lowercase or uppercase. Unicode characters beyondASCII range are not [("format")]forcesimport excelto Import all Excel data as string data. You canspecify the numeric display format used to convert the numeric data to string using the optionalargumentformat. See [D] data in memory before loading data from the Excel Import Excel Import and export Excel filesThe following option is available withimport excelbut is not shown in the dialog box:locale("locale")specifies the locale used by the workbook.

6 You might need this option whenworking with extendedASCII character sets. This option has no effect on Microsoft Windows. Thedefault locale for export Excel Main sheet("sheetname"[, modify|replace])saves to the worksheet namedsheetname. If there is noworksheet namedsheetnamein the workbook, a new sheet namedsheetnameis created. If thisoption is not specified, the first worksheet of the workbook is used. Ifsheetnamedoes exist in theworkbook, you can eithermodifyorreplacethe data to the worksheet without changing the cells outside the exported range. Thisoption cannot be specified withreplace, nor when overwriting the Excel the worksheet before the data are exported to be specifiedwithmodify, nor when overwriting the Excel (start)specifies the start (upper-left) cell in the Excel worksheet to begin saving to.

7 By default, export excelsaves starting in the first row and first column of the (variables|varlabels)specifies that the variable names or the variable labels be savedin the first row in the Excel worksheet. The variable name is used if there is no variable label fora given the underlying numeric values instead of the value that, when writing data, export excelshould preserve the existing worksheet scell style and format. By default, export exceldoes not preserve a cell s style or an existing Excel be specified when modifyingor replacing a given worksheet: export , sheet("", modify)orexport ("", replace). Advanced datestring("datetimeformat")exports all datetime variables as strings formatted bydate-timeformat. See [D]Datetime display ("repval")exports missing values be either string or numeric.

8 Withoutspecifying this option, export excelexports the missing values as empty following option is available withexport excelbut is not shown in the dialog box:locale("locale")specifies the locale used by the workbook. You might need this option whenworking with extendedASCII character sets. The default locale and demonstrate the use ofimport excelandexport Excel , we will first it as an Excel file :. use (1978 automobile data). export Excel auto, firstrow(variables) Excel Import and export Excel files 5 Now we can Import from we just created, telling Stata to clear the current datafrom memory and to treat the first row of the worksheet in the Excel file as variable names:. Import Excel , firstrow clear(12 vars, 74 obs). describeContains dataObservations: 74 Variables: 12 Variable Storage Display Valuename type format label Variable labelmake str17 %17s makeprice int % pricempg byte % mpgrep78 byte % rep78headroom double % headroomtrunk byte % trunkweight int % weightlength int % lengthturn byte % turndisplacement int % displacementgear_ratio double % gear_ratioforeign str8 %9s foreignSorted by:Note.

9 Dataset has changed since last can also Import a subrange of the cells in the Excel file:. Import Excel , cellrange(:D70) firstrow clear(4 vars, 69 obs). describeContains dataObservations: 69 Variables: 4 Variable Storage Display Valuename type format label Variable labelmake str17 %17s makeprice int % pricempg byte % mpgrep78 byte % rep78 Sorted by:Note: Dataset has changed since last are supported byimport excelandexport Excel . If a file extensionis not specified withexport Excel ,.xlsis assumed, because this format is more common and iscompatible with more applications that also can read from Excel files. To save the data in memoryas , specify the extension.

10 Use , clear(1978 automobile data). export Excel Import Excel Import and export Excel filesTo export a subset of variables and overwrite the file, specify a variablelist and thereplaceoption:. export Excel make mpg weight using auto, additional examples illustratingimport excelandexport Excel , see Mitchell (2020,chap. 2 3).Technical note: Excel data size limitsFor an workbook, the worksheet size limits are 65,536 rows by 256 string size limit is 255 an workbook, the worksheet size limits are 1,048,576 rows by 16,384columns. The string size limit is 32,767 note: Dates and timesExcel has two different date systems, the 1900 Date System and the 1904 Date System . Excelstores a date and time as an integer representing the number of days since a start date plus a fractionof a 24-hour the 1900 Date System, the start date is 00 Jan1900; in the 1904 Date System, the start date is01 Jan1904.