Title

portel xl - port ms excel files

On Export

o- The data is written to the first sheet.

o- Variable names are placed in the first row

o- Variables with value labels are written to two columns: varname, holding the numeric value; and varname_vl, holding the labeled value.

o- Variable (name) labels, formats, and characteristics are written to a second sheet, named characteristics.
The first row has variable names; the first column has characteristic names, and the cells hold characteristic values.

Options

xls specifies writing the file in xls rather than xlsx format. The actual file extension triggering the port can be .xl, .xls, or .xlsx,
but the file will be written as .xlsx unless xls is specified.

colors specifies using certain data columns to set text and/or background colors, rather than exporting those columns as data:

For pairs of variables named varname and varname_bc, varname_bc will be used to set the background colors for varname, on the assumption that varname_bc holds rgb values.

The same goes for pairs of variables named varname and varname_tc, except that varname_tc will be used to set the text colors for varname.

datestring(), missing() and locale() can all be used as described in export excel.

On Import

o- The file extension .xl can be used as a synonym for .xlsx.

o- If a file is not found with the specified extension (ie, .xlsx or .xls), the alternate will be tried. If neither is found, an error will be generated.

o- Data is read from the first sheet, by default.

o- Variable names are read from the first row, by default.

o- Variable names are converted to lowercase, by default.

o- For pairs of numeric and string variables named varname and varname_vl, respectively, varname_vl will be used to create value labels for varname, and will not be imported as data.

o- If a sheet named characteristics is present, and is configured properly and matches the dataset variables, it will be used to set variable characteristics, including labels and formats.

Options

nonames specifies treating the first row as data, rather than variable names.

colors specifies importing the colors of cell text and backgrounds, as data columns of rgb values.

For each column (varname) with colored text and/or background, one or two additional data columns will be created:
One named varname_bc holding the background colors, and one named varname_tc holding the text colors.

sheet(), cellrange(), case(), allstring, and locale() can all be used as described under import excel. (But, as noted above, the default for case() is lower)

Remarks

Reading or writing colors could take an extremely long time for any substantial dataset. In the contexts in which it's likely to arise, however, it should be fine.