Difference between revisions of "Importing and Exporting Data"
Yvonnemilne (Talk | contribs) (→Export to Text Files) |
Yvonnemilne (Talk | contribs) (→Export to Text Files) |
||
| Line 87: | Line 87: | ||
copy to custcsv csv | copy to custcsv csv | ||
</code> | </code> | ||
| + | |||
| + | The [[SQL SELECT|SQL select]] statement can export the selected data in text file format. | ||
<pre> | <pre> | ||
Revision as of 12:09, 14 January 2010
Contents
Importing and Exporting Data
The Navigational Data Access commands append from and copy to can respectively be used to import and export Recital data.
The SQL statements create table and insert allow data to be imported from XML files and the into, save as and to clauses supported by the SQL select statement offer a wide range of export formats for the selected rows.
Import From Text Files
The append from command imports data into the currently selected table from the specified file of the stated format. The file extension of the imported file is assumed to be .csv for type csv and .txt for all other types unless included.
append from <filename> [while <condition as logical>] [for <condition as logical>] [type] sdf | fixed | delimited | delimited with blank | delimited with <delimiter> | csv
Examples
use example // Standard data file append from custsdf sdf // Fixed length fields append from custfix fixed // Delimited (',' separated, "" around character fields) append from custdel delimited // Delimited with blank (single space separates fields) append from custblank delimited with blank // Delimited with <delimiter> (',' separated, <delimiter> around character fields) append from custpipe delimited with | // Microsoft Excel compatible comma-separated append from custcsv csv
Import from XML
The SQL create table statement allows a new table to be created from metadata stored in an XML file and the data to be optionally loaded.
create table <table> [from] xml <.xml file> [load]
Example
create table customer2; from xml cust.xml load
The SQL insert statement supports the import of data from an XML file:
insert into <table> from [xml] <xml filename>
Example
insert into products; from xml prices.xml
Import Live Data using SQL Connectivity Functions
Export to Text Files
The copy to command exports data from the currently selected table to the specified file in the stated format. The file extension of the exported file is assumed to be .csv for type csv and .txt for all other types unless included.
copy to <filename> [while <condition as logical>] [for <condition as logical>] [type] sdf | fixed | delimited | delimited with blank | delimited with <delimiter> | csv
Examples
use example // Standard data file copy to custsdf sdf // Fixed length fields copy to custfix fixed // Delimited (',' separated, "" around character fields) copy to custdel delimited // Delimited with blank (single space separates fields) copy to custblank delimited with blank // Delimited with <delimiter> (',' separated, <delimiter> around character fields) copy to custpipe delimited with | // Microsoft Excel compatible comma-separated copy to custcsv csv
The SQL select statement can export the selected data in text file format.
select <column-definition> from <table-definition> to file <filename>
Examples
select * from shippers to file shiptxt
Export to XML
select <column-definition> from <table-definition> into xml <xml filename>
or
select <column-definition> from <table-definition> save as xml <xml filename>
Examples
select employeeid, lastname, firstname; from employees into xml emp select employeeid, lastname, firstname; from employees save as xml emp
Export to HTML
select <column-definition> from <table-definition> into html <html filename>
Export to JSON
select <column-definition> from <table-definition> into json <filename>
