Input Worksheet Options
Previous Topic  Next Topic 

You have several options to specify what part of an input worksheet to read and how to read variable names.

Data Range

You can choose different ranges to be read in input worksheets by using the drop-down menu for Data Range.

AutoSense  This option is the default selection.  When it is selected, Stat/Transfer will read to the first non-blank cell and use that as the upper left corner of the data range.  By default, it will then read data until it encounters an entirely blank line.  This default behavior with regard to blank rows can be changed using the Blank Rows options given below.

Specify Named Range   You can change the default Autosense behavior by specifying a named range.  If you select the option Specify Named Range, then the Range line will become active and you must enter the name of a "named range" in your worksheet. 

Specify Explicit Range   You can also change the default behavior by specifying an explicit range.  If you select this option, then the Range line will become active and you must enter explicit coordinates that define the range (such as C3:F280).

When a range has been specified by either one of these methods, Stat/Transfer's default treatment of entirely blank rows will also be overridden.  They will be returned in your output data and, in addition, blank rows at the end, through the last row of the specified range, will also be returned.  Note that because this option generally only applies to specific worksheets and because Stat/Transfer's defaults usually work fine, the setting is not saved between sessions.

Field Name Row

By default, Stat/Transfer will attempt to autosense whether or not the data in the first non-blank row (or the first row of a specified range) are variable names or the first row of data.  It does so by looking for at least one column in which there is a string in the first row and a number in the second.  If this behavior is inappropriate for your worksheet (for example, if you have only string data), you can override it.  Note that the setting is not saved between sessions.

You can specify one of the following options from the Field Name Row drop-down menu:

AutoSense  The default behavior.

First Non-Blank Row  This option will take the values in the first non-blank row as the field names even if there is no change to a numeric type in the second row.

No Names in Worksheet  This option will treat the first non-blank row as data.  Stat/Transfer will assign variable names 'col1' through 'coln'.

Specify Row Explicitly  This option will take the field names from a specified row.  If you select Specify Row Explicitly from the drop-down menu, then the Row line will become active and you must enter a row.

Read Variable Labels from Second Row

If this option is checked, variable labels will be read from the row immediately following the variable name row, typically the second row.

Blank Rows

This option menu is used when Autosense is chosen for the Data Range option.

Stop Reading  This is the default behavior.  After finding the beginning of the data by identifying the first non-blank cell, Stat/Transfer will read data until it encounters an entirely blank line.  It will end the transfer at this point.

Skip Blank Rows  If the option Skip Blank Rows is chosen, Stat/Transfer will read the entire worksheet page, searching for and returning further non-blank rows.  However, any blank rows that it finds will not be written out.

Return Blank Rows  If this option is selected, then all rows of the input worksheet page including blank ones will be written to the output file.  This option may return unexpected blank rows from the end of a worksheet page that contains formatting but no data.

Numeric Missing Value

If there is a string in your worksheet, such as 'NA',  that you would like to have treated as a numeric missing value, enter it here.  Note that a single '.' is treated as missing by default.

Concatenate Worksheet Pages

The option Concatenate Worksheet Pages allows you to combine worksheet pages into a single output file.  This option is appropriate if your worksheet contains many sheets that are identical in structure.  These can be then be combined into a single output file of any type.

For example, you may have a workbook that has 50 sheets, with one sheet for each state and the same variables on each sheet.  If you check this box, Stat/Transfer will effectively combine the sheets into one large input file, dropping the field names, if necessary, on the second and higher sheets. You will end up with the data from all of your worksheet pages in a single output file.

Transfer Blank Columns

By default, columns with variable names that have no data will be transferred.  This option allows totally blank columns to be transferred as well, with the variable name set to "coln" where n is the column number.

Write A Fieldname Row in Worksheet Output

This check box controls whether or not your field names will be written as the first row of the output worksheet.  By default it is checked.

Write Variable Labels to the Second Row

This option will write variable labels (if present) to the second row of the worksheet.  If there are no variable labels present in the file, this option will have no effect.

Write Value Labels Instead of Numeric Values

If this option is checked, variables with value labels will be written as strings.  If a value label is missing for a variable with any value labels, the numeric value will be formatted as a string and written to the worksheet.