Exporting to a Database

You can use the Export to Database Wizard to:

  • Replace values in existing database table fields (columns) or add new fields to a table.
  • Append new records (rows) to a database table.
  • Completely replace a database table or create a new table.

To export data to a database:

  1. From the menus in the Data Editor window for the dataset that contains the data you want to export, choose:

    File > Export > Database

  2. Select the database source.
  3. Follow the instructions in the export wizard to export the data.

Creating Database Fields from IBM® SPSS® Statistics Variables

When creating new fields (adding fields to an existing database table, creating a new table, replacing a table), you can specify field names, data type, and width (where applicable).

Field name. The default field names are the same as the IBM SPSS Statistics variable names. You can change the field names to any names allowed by the database format. For example, many databases allow characters in field names that aren't allowed in variable names, including spaces. Therefore, a variable name like CallWaiting could be changed to the field name Call Waiting.

Type. The export wizard makes initial data type assignments based on the standard ODBC data types or data types allowed by the selected database format that most closely matches the defined IBM SPSS Statistics data format--but databases can make type distinctions that have no direct equivalent in IBM SPSS Statistics, and vice versa. For example, most numeric values in IBM SPSS Statistics are stored as double-precision floating-point values, whereas database numeric data types include float (double), integer, real, and so on. In addition, many databases don't have equivalents to IBM SPSS Statistics time formats. You can change the data type to any type available in the drop-down list.

As a general rule, the basic data type (string or numeric) for the variable should match the basic data type of the database field. If there is a data type mismatch that cannot be resolved by the database, an error results and no data are exported to the database. For example, if you export a string variable to a database field with a numeric data type, an error will result if any values of the string variable contain non-numeric characters.

Width. You can change the defined width for string (char, varchar) field types. Numeric field widths are defined by the data type.

By default, IBM SPSS Statistics variable formats are mapped to database field types based on the following general scheme. Actual database field types may vary, depending on the database.

Table 1. Format conversion for databases
IBM SPSS Statistics Variable Format Database Field Type
Numeric Float or Double
Comma Float or Double
Dot Float or Double
Scientific Notation Float or Double
Date Date or Datetime or Timestamp
Datetime Datetime or Timestamp
Time, DTime Float or Double (number of seconds)
Wkday Integer (1–7)
Month Integer (1–12)
Dollar Float or Double
Custom Currency Float or Double
String Char or Varchar

User-Missing Values

There are two options for the treatment of user-missing values when data from variables are exported to database fields:

  • Export as valid values. User-missing values are treated as regular, valid, nonmissing values.
  • Export numeric user-missing as nulls and export string user-missing values as blank spaces. Numeric user-missing values are treated the same as system-missing values. String user-missing values are converted to blank spaces (strings cannot be system-missing).