help odbc dialogs: odbc load odbc insert
-------------------------------------------------------------------------------
Title
[D] odbc -- Load, write, or view data from ODBC sources
Syntax
List ODBC sources to which Stata can connect
odbc list
Retrieve available names from specified data source
odbc query ["DataSourceName" , verbose schema connect_options]
List column names and types associated with specified table
odbc describe ["TableName" , dsn("DataSourceName") connect_options]
Read ODBC table into memory
odbc load [extvarlist] [if] [in] ,
{table("TableName")|exec("SqlStmt")}
[load_options connect_options]
Write data to an ODBC table
odbc insert [varlist] , table("TableName") dsn("DataSourceName")
[insert_options connect_options]
Allow SQL statements to be issued directly to ODBC data source
odbc exec("SqlStmt") , dsn("DataSourceName") [connect_options]
Batch job alternative to odbc exec
odbc sqlfile("filename") , dsn("DataSourceName") [loud
connect_options]
Specify ODBC driver manager (Unix only)
set odbcmgr {iodbc|unixodbc} [, permanently]
where
DataSourceName is the name of the ODBC source (database, spreadsheet,
etc.)
TableName is the name of a table within the ODBC data source
SqlStmt is an SQL SELECT statement
filename is pure SQL commands separated by semicolons
and where extvarlist contains
sqlvarname
varname=sqlvarname
connect_options description
-------------------------------------------------------------------------
user(UserID) user ID of user establishing connection
password(Password) password of user establishing connection
dialog(noprompt) do not display ODBC connection-information
dialog, and do not prompt user for
connection information
dialog(prompt) display ODBC connection-information dialog
dialog(complete) display ODBC connection-information dialog
only if there is not enough information
dialog(required) display ODBC connection-information dialog
only if there is not enough mandatory
information provided
*dsn("DataSourceName") name of data source
-------------------------------------------------------------------------
*dsn("DataSourceName") is not allowed with odbc query. It is required
with odbc insert, odbc exec, and odbc sqlfile.
load_options description
-------------------------------------------------------------------------
* table("TableName") name of table stored in data source
* exec("SqlStmt") SQL SELECT statement to generate a table to be
read into Stata
clear load dataset even if there is one in memory
noquote alter Stata's internal use of SQL commands;
seldom used
lowercase read variable names as lowercase
sqlshow show all SQL commands issued
allstring read all variables as strings
datestring read date-formatted variables as strings
-------------------------------------------------------------------------
* Either table("TableName") or exec("SqlStmt") must be specified with
odbc load.
insert_options description
-------------------------------------------------------------------------
* table("TableName") name of table stored in data source
create create a simple ODBC table
overwrite clear data in ODBC table before data in memory
is written to the table
insert default mode of operation for the odbc insert
command
quoted quote all values with single quotes as they
are inserted in ODBC table
sqlshow show all SQL commands issued
as("varlist") ODBC variables on the data source that
correspond to the variables in Stata's
memory
-------------------------------------------------------------------------
* table("TableName") is required with odbc insert.
Menu
odbc load
File > Import > ODBC data source
odbc insert
File > Export > ODBC data source
Description
odbc allows you to load, write, and view data from Open DataBase
Connectivity (ODBC) sources into Stata. ODBC is a standardized set of
function calls for accessing data stored in both relational and
nonrelational database-management systems. By default on Unix platforms,
iODBC is the ODBC driver manager Stata uses, but you can use unixODBC by
using the command set odbcmgr unixodbc.
ODBC's architecture consists of four major components (or layers): the
client interface, the ODBC driver manager, the ODBC drivers, and the data
sources. Stata provides odbc as the client interface. The system is
illustrated as following:
+-------------------+ +----------+
| | +---------+ +---------+ | |
| Client interface | | | | | | |
| (Stata) | | | | | | |
| | | ODBC | | ODBC | | ODBC |
| odbc list | --- | driver | --- | driver | --- | data |
| odbc query | | manager | | | | Source |
| odbc describe | | | | | | |
| odbc load | +---------+ +---------+ | |
| odbc insert | | |
| odbc exec | | |
| odbc sqlfile | | |
+-------------------+ +----------+
odbc list produces a list of ODBC data source names to which Stata can
connect.
odbc query retrieves a list of table names available from a specified
data source's system catalog.
odbc describe lists column names and types associated with a specified
table.
odbc load reads an ODBC table into memory. You can load an ODBC table
specified in the table() option or load an ODBC table generated by an SQL
SELECT statement specified in the exec() option. In both cases, you can
choose which columns and rows of the ODBC table to read by specifying
extvarlist and if and in conditions. extvarlist specifies the columns to
be read and allows you to rename variables. For example,
. odbc load id=EmployeeID LastName, table(Employees) dsn(Northwind)
reads 2 columns, EmployeeID and LastName, from the Employees table of the
Northwind data source. It will also rename variable EmployeeID to id.
odbc insert writes data from memory to an ODBC table. The data can be
appended to an existing table, replace an existing table, or be placed in
a newly created ODBC table.
odbc exec allows for most SQL statements to be issued directly to any
ODBC data source. Statements that produce output, such as SELECT, have
their output neatly displayed. By using Stata's ado language, you can
also generate SQL commands on the fly to do positional updates or
whatever the situation requires.
odbc sqlfile provides a "batch job" alternative to the odbc exec command.
A file is specified that contains any number of any length SQL commands.
Every SQL command in this file should be delimited by a semicolon and
must be constructed as pure SQL. Stata macros and ado-language syntax
are not permitted. The advantage in using this command, as opposed to
odbc exec, is that only one connection is established for multiple SQL
statements. A similar sequence of SQL commands used via odbc exec would
require constructing an ado-file that issued a command and, thus, a
connection for every SQL command. Another slight difference is that any
output that might be generated from an SQL command is suppressed by
default. A loud option is provided to toggle output back on.
set odbcmgr iodbc specifies that the ODBC driver manager is iODBC (the
default). set odbcmgr unixodbc specifies that the ODBC driver manager is
unixODBC.
Options
user(UserID) specifies that the user ID of the user attempting to
establish the connection to the data source. By default, Stata
assumes the user ID is the same as the one specified in the previous
odbc command or is empty if user() has never been specified in the
current session of Stata.
password(Password) specifies the password of the user attempting to
establish the connection to the data source. By default, Stata
assumes the password is the same as the one previously specified or
is empty if the password has not been used during the current session
of Stata. Typically, the password() option will not be specified
apart from the user() option.
dialog(noprompt|prompt|complete|required) specifies the mode the ODBC
Driver Manager uses to display the ODBC connection-information dialog
to prompt for more connection information.
noprompt is the default value. The ODBC connection-information
dialog is not displayed, and you are not prompted for connection
information. If there is not enough information to establish a
connection to the specified data source, an error is returned.
prompt causes the ODBC connection-information dialog to be displayed.
complete causes the ODBC connection-information dialog to be
displayed only if there is not enough information, even if the
information is not mandatory.
required causes the ODBC connection-information dialog to be
displayed only if there is not enough mandatory information provided
to establish a connection to the specified data source. You are
prompted only for mandatory information; controls for information
that is not required to connect to the specified data source are
disabled.
dsn("DataSourceName") specifies the name of a data source, as listed by
the odbc list command. If a name contains spaces, it must be
enclosed in double quotes. By default, Stata assumes that the data
source name is the same as the one specified in the previous odbc
command.
table("TableName") specifies the name of an ODBC table stored in a
specified data source's system catalog, as listed by the odbc query
command. If a table name contains spaces, it must be enclosed in
double quotes. Either the table() option or the exec() option -- but
not both -- is required with the odbc load command.
exec("SqlStmt") allows you to issue an SQL SELECT statement to generate a
table to be read into Stata. An error message is returned if the
SELECT statement is an invalid SQL statement. The statement must be
enclosed in double quotes. Either the table() option or the exec()
option -- but not both -- is required with the odbc load command.
clear permits the data to be loaded, even if there is a dataset already
in memory, and even if that dataset has changed since the data were
last saved.
noquote alters Stata's internal use of SQL commands, specifically those
relating to quoted table names, to better accommodate various
drivers. This option has been particularly helpful for DB2 drivers.
lowercase causes all the variable names to be read as lowercase.
sqlshow is a useful option for showing all SQL commands issued to the
ODBC data source from the odbc insert or odbc load command. This can
help you debug any issues related to inserting or loading.
allstring causes all variables to be read in as string data types.
datestring causes all date- and time-formatted variables to be read in as
string data types.
create specifies that a simple ODBC table be created on the specified
data source and populated with the data in memory. Column data types
are approximated based on the existing format in Stata's memory.
overwrite allows data to be cleared from an ODBC table before the data in
memory are written to the table. All data from the ODBC table are
erased, not just from variable columns that will be replaced.
insert appends data to an existing ODBC table and is the default mode of
operation for the odbc insert command.
quoted is useful for ODBC data sources that require all inserted values
to be quoted. This option specifies that all values be quoted with
single quotes as they are inserted into an ODBC table.
as("varlist") allows you to specify the ODBC variables on the data source
that correspond to the variables in Stata's memory. If this option
is specified, the number of variables must equal the number of
variables being inserted, even if some names are identical.
loud specifies that output be displayed for SQL commands.
verbose specifies that odbc query list any data source alias, nickname,
typed table, typed view, and view along with tables so that you can
load data from these table types.
schema specifies that odbc query return schema names with the table names
from a data source. NOTE: The schema names returned from odbc query
will also be used with the odbc describe and odbc load commands.
When using odbc load with a schema name, you might also need to
specify the noquote option because some drivers do not accept quotes
around table or schema names.
permanently (set odbcmgr only) specifies that, in addition to making the
change right now, the setting be remembered and become the default
setting when you invoke Stata.
Examples
Some of the following examples are default samples that are available
when installing Microsoft Office. Depending on the version of Microsoft
Office, your results may vary.
. odbc list
Data Source Name Driver
---------------------------------------------------------------------------
Visual FoxPro Database Microsoft Visual FoxPro Driver
Visual FoxPro Tables Microsoft Visual FoxPro Driver
dBase Files - Word Microsoft dBase VFP Driver (*.dbf)
FoxPro Files - Word Microsoft FoxPro VFP Driver (*.dbf)
MS Access Database Microsoft Access Driver (*.mdb)
Excel Files Microsoft Excel Driver (*.xls)
Northwind Microsoft Access Driver (*.mdb)
dBASE Files Microsoft dBase Driver (*.dbf)
DeluxeCD Microsoft Access Driver (*.mdb)
ECDCMusic Microsoft Access Driver (*.mdb)
---------------------------------------------------------------------------
. odbc query "Northwind"
DataSource: Northwind
---------------------------------------------------------------------------
Categories
Customers
Employees
Order Details
Orders
Products
Shippers
Suppliers
Test
---------------------------------------------------------------------------
. odbc desc "Employees"
DataSource: Northwind (query)
Table: Employees (load)
---------------------------------------------------------------------------
Variable Name Variable Type
---------------------------------------------------------------------------
EmployeeID COUNTER
LastName VARCHAR
FirstName VARCHAR
Title VARCHAR
TitleOfCourtesy VARCHAR
BirthDate DATETIME
HireDate DATETIME
Address VARCHAR
City VARCHAR
Region VARCHAR
PostalCode VARCHAR
Country VARCHAR
HomePhone VARCHAR
Extension VARCHAR
Photo LONGBINARY
Notes LONGCHAR
ReportsTo INTEGER
---------------------------------------------------------------------------
. odbc load ID=EmployeeID LastName Title in 1/5, table("Employees")
dsn("Northwind")
. list
+-------------------------------------------+
| ID LastName Title |
|-------------------------------------------|
1. | 1 Davolio Sales Representative |
2. | 2 Fuller Vice President, Sales |
3. | 3 Leverling Sales Representative |
4. | 4 Peacock Sales Representative |
5. | 5 Buchanan Sales Manager |
+-------------------------------------------+
. odbc load, exec("SELECT EmployeeID, LastName, Title FROM Employees
WHERE EmployeeID <= 5") dsn("Northwind") clear
. list
+----------------------------------------------+
| Employ~D LastName Title |
|----------------------------------------------|
1. | 1 Davolio Sales Representative |
2. | 2 Fuller Vice President, Sales |
3. | 3 Leverling Sales Representative |
4. | 4 Peacock Sales Representative |
5. | 5 Buchanan Sales Manager |
+----------------------------------------------+
Also see
Manual: [D] odbc
Help: [D] fdasave, [TS] haver, [D] infix (fixed format), [D] insheet
