Difference between revisions of "Importing and Exporting Data"

Latest revision as of 12:52, 6 July 2011

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 stated.

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

The remote data connectivity functions are used to handle gateway connections to third party data sources. When an SQL SELECT statement is sent to the data source, the results are automatically loaded into a Recital cursor. This cursor operates as a temporary table and can be processed as a standard Recital table.

For complete details on the connectivity functions, please see the separate Remote Data Connectivity chapter.

Example

nStatHand=sqlstringconnect("mys@localhost:user1/pass1-mysql.tcpip",.T.)
if nStatHand < 1
    messagebox("Cannot make connection", 16, "SQL Connect Error")
else
    messagebox('Connection made', 48, 'SQL Connect Message')
    sqlexec(nStatHand, "select * from user", "restab")
    // restab cursor can now be treated like any Recital temporary table
    select * from restab into xml temp
    sqldisconnect(nStatHand)
endif

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

The copy to command can export data from the currently selected table to an XML file. The file extension of the exported file is assumed to be .xml unless included.

copy to <filename> 
[while <condition as logical>] [for <condition as logical>]
[type] xml

Example

use example
copy to custxml xml

The SQL select statement can export the selected data in XML file format.

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

The SQL select statement can export the selected data in HTML file format.

select <column-definition> from <table-definition> into html <html filename>

Example

select employeeid, lastname, firstname;
  from employees into html emp

Export to JSON

The SQL select statement can export the selected data in JSON file format.

select <column-definition> from <table-definition> into json <filename>

Example

select employeeid, lastname, firstname;
  from employees into json emp