@display
GET DATA
- /TYPE=@{GNM,TXT@}
- /FILE=@{'file-name',file_handle@}
+ /TYPE=@{GNM,PSQL,TXT@}
@dots{}additional subcommands depending on TYPE@dots{}
@end display
@item GNM
Spreadsheet files created by Gnumeric (@url{http://gnumeric.org}).
+@item PSQL
+Relations from PostgreSQL databases (@url{http://postgresql.org}).
+
@item TXT
Textual data files in columnar and delimited formats.
@end table
-The FILE subcommand is mandatory for all implemented file types.
-Specify the file to be read as a string file name or (for textual data
-only) a file handle (@pxref{File Handles}).
-
Each supported file type has additional subcommands, explained in
separate sections below.
@menu
* GET DATA /TYPE=GNM::
+* GET DATA /TYPE=PSQL::
* GET DATA /TYPE=TXT::
@end menu
determined from the length of the string it contains, unless the
ASSUMEDVARWIDTH subcommand is given.
+
+The FILE subcommand is mandatory. Specify the name of the file
+to be read.
+
The SHEET subcommand specifies the sheet within the spreadsheet file to read.
There are two forms of the SHEET subcommand.
In the first form,
If omitted, the default value is determined from the length of the
string in the first spreadsheet cell for each variable.
+
+@node GET DATA /TYPE=PSQL
+@subsection Postgres Database Queries
+
+@display
+GET DATA /TYPE=PSQL
+ /CONNECT=@{connection info@}
+ /SQL=@{query@}
+ [/ASSUMEDVARWIDTH=n]
+ [/UNENCRYPTED]
+ [/BSIZE=n].
+@end display
+
+@cindex postgres
+@cindex databases
+
+The PSQL type is used to import data from a postgres database server.
+The server may be located locally or remotely.
+Variables are automatically created based on the table column names
+or the names specified in the SQL query.
+Postgres data types of high precision, will loose precision when
+imported into PSPP.
+Not all the postgres data types are able to be represented in PSPP.
+If a datum cannot be represented a warning will be issued and that
+datum will be set to SYSMIS.
+
+The CONNECT subcommand is mandatory.
+It is a string specifying the parameters of the database server from
+which the data should be fetched.
+The format of the string is given in the postgres manual
+@url{http://www.postgresql.org/docs/8.0/static/libpq.html#LIBPQ-CONNECT}.
+
+The SQL subcommand is mandatory.
+It must be a valid SQL string to retrieve data from the database.
+
+The ASSUMEDVARWIDTH subcommand specifies the maximum width of string
+variables read from the database.
+If omitted, the default value is determined from the length of the
+string in the first value read for each variable.
+
+The UNENCRYPTED subcommand allows data to be retrieved over an insecure
+connection.
+If the connection is not encrypted, and the UNENCRYPTED subcommand is not
+given, then an error will occur.
+Whether or not the connection is
+encrypted depends upon the underlying psql library and the
+capabilities of the database server.
+
+The BSIZE subcommand serves only to optimise the speed of data transfer.
+It specifies an upper limit on
+number of cases to fetch from the database at once.
+The default value is 4096.
+If your SQL statement fetches a large number of cases but only a small number of
+variables, then the data transfer may be faster if you increase this value.
+Conversely, if the number of variables is large, or if the machine on which
+PSPP is running has only a
+small amount of memory, then a smaller value will be better.
+
+
+The following syntax is an example:
+@example
+GET DATA /TYPE=PSQL
+ /CONNECT='host=example.com port=5432 dbname=product user=fred passwd=xxxx'
+ /SQL='select * from manufacturer'.
+@end example
+
+
@node GET DATA /TYPE=TXT
@subsection Textual Data Files
@cindex text files
@cindex data files
When TYPE=TXT is specified, GET DATA reads data in a delimited or
-fixed columnar format, much like DATA LIST (@pxref{DATA LIST}). The
-FILE subcommand must be specified indicate the name or the file handle
-of the file to be read.
+fixed columnar format, much like DATA LIST (@pxref{DATA LIST}).
+
+The FILE subcommand is mandatory. Specify the file to be read as
+a string file name or (for textual data
+only) a file handle (@pxref{File Handles}).
The ARRANGEMENT subcommand determines the file's basic format.
DELIMITED, the default setting, specifies that fields in the input
[/IMPORTCASE=@{ALL,FIRST max_cases,PERCENT percent@}]
/DELIMITERS="delimiters"
- [/QUALIFIER="quote"]
+ [/QUALIFIER="quotes" [/ESCAPE]]
[/DELCASE=@{LINE,VARIABLES n_variables@}]
/VARIABLES=del_var [del_var]@dots{}
where each del_var takes the form:
which each field appears on a separate line, specify the empty string
for DELIMITERS.
-The optional QUALIFIER subcommand names a character that can be used
-to quote values within fields in the input. A field that begins with
-the specified quote character ends at the next match quote.
-Intervening delimiters become part of the field, instead of
-terminating it.
+The optional QUALIFIER subcommand names one or more characters that
+can be used to quote values within fields in the input. A field that
+begins with one of the specified quote characters ends at the next
+matching quote. Intervening delimiters become part of the field,
+instead of terminating it. The ability to specify more than one quote
+character is a PSPP extension.
+
+By default, a character specified on QUALIFIER cannot itself be
+embedded within a field that it quotes, because the quote character
+always terminates the quoted field. With ESCAPE, however, a doubled
+quote character within a quoted field inserts a single instance of the
+quote into the field. For example, if @samp{'} is specified on
+QUALIFIER, then without ESCAPE @code{'a''b'} specifies a pair of
+fields that contain @samp{a} and @samp{b}, but with ESCAPE it
+specifies a single field that contains @samp{a'b}. ESCAPE is a PSPP
+extension.
The DELCASE subcommand controls how data may be broken across lines in
the data file. With LINE, the default setting, each line must contain
Consider the following information on animals in a pet store:
@example
-"Pet Name", "Age", "Color", "Date Received", "Price", "Needs Walking", "Type"
+'Pet''s Name', "Age", "Color", "Date Received", "Price", "Height", "Type"
, (Years), , , (Dollars), ,
-"Rover", 4.5, Brown, "12 Feb 2004", 80, True, "Dog"
-"Charlie", , Gold, "5 Apr 2007", 12.3, False, "Fish"
-"Molly", 2, Black, "12 Dec 2006", 25, False, "Cat"
-"Gilly", , White, "10 Apr 2007", 10, False, "Guinea Pig"
+"Rover", 4.5, Brown, "12 Feb 2004", 80, '1''4"', "Dog"
+"Charlie", , Gold, "5 Apr 2007", 12.3, "3""", "Fish"
+"Molly", 2, Black, "12 Dec 2006", 25, '5"', "Cat"
+"Gilly", , White, "10 Apr 2007", 10, "3""", "Guinea Pig"
@end example
@noindent
@c If you change this example, change the regression test in
@c tests/command/get-data-txt-examples.sh to match.
@example
-GET DATA /TYPE=TXT /FILE='pets.data' /DELIMITERS=', ' /QUALIFIER='"'
+GET DATA /TYPE=TXT /FILE='pets.data' /DELIMITERS=', ' /QUALIFIER='''"' /ESCAPE
/FIRSTCASE=3
/VARIABLES=name A10
age F3.1
color A5
received EDATE10
price F5.2
- needs_walking A5
- type A10.
+ height a5
+ type a10.
@end example
@node GET DATA /TYPE=TXT /ARRANGEMENT=FIXED
projects / pspp-builds.git / blobdiff