View source for Importing and Exporting Data

==Importing and Exporting Data==
The Navigational Data Access commands [[APPEND FROM|append from]] and [[COPY|copy to]] can respectively be used to import and export Recital data. 

The SQL statements [[CREATE TABLE|create table]] and [[INSERT|insert]] allow data to be imported from XML files and the ''into'', ''save as'' and ''to'' clauses supported by the [[SQL SELECT|SQL select]] statement offer a wide range of export formats for the selected rows.

===Import From Text Files===

The [[APPEND FROM|append from]] command imports data into the currently [[SELECT|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.

<pre>
append from <filename> 
[while <condition as logical>] [for <condition as logical>]
[type] sdf | fixed | delimited | delimited with blank | delimited with <delimiter> | csv 
</pre>

'''Examples'''

<code lang="recital">
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
</code>

===Import from XML===
The [[CREATE TABLE|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.

<pre>
create table <table>
  [from] xml <.xml file> [load]
</pre>

'''Example'''

<code lang="recital">
create table customer2;
  from xml cust.xml load
</code>

The [[SQL INSERT|SQL insert]] statement supports the import of data from an XML file:

<pre>
insert into <table> from [xml] <xml filename>
</pre>

'''Example'''

<code lang="recital">
insert into products;
  from xml prices.xml
</code>

===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'''

<code lang="recital">
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
</code>

===Export to Text Files===
The [[COPY|copy to]] command exports data from the currently [[SELECT|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.

<pre>
copy to <filename> 
[while <condition as logical>] [for <condition as logical>]
[type] sdf | fixed | delimited | delimited with blank | delimited with <delimiter> | csv 
</pre>

'''Examples'''

<code lang="recital">
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
</code>

The [[SQL SELECT|SQL select]] statement can export the selected data in text file format.

<pre>
select <column-definition> from <table-definition> to file <filename>
</pre>

'''Examples'''

<code lang="recital">
select * from shippers to file shiptxt
</code>

===Export to XML===
The [[COPY|copy to]] command can export data from the currently [[SELECT|selected]] table to an XML file.  The file extension of the exported file is assumed to be ''.xml'' unless included.

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

'''Example'''

<code lang="recital">
use example
copy to custxml xml
</code>

The [[SQL SELECT|SQL select]] statement can export the selected data in XML file format.

<pre>
select <column-definition> from <table-definition> into xml <xml filename>
</pre>

or

<pre>
select <column-definition> from <table-definition> save as xml <xml filename>
</pre>

'''Examples'''

<code lang="recital">
select employeeid, lastname, firstname;
  from employees into xml emp
select employeeid, lastname, firstname;
  from employees save as xml emp
</code>

===Export to HTML===
The [[SQL SELECT|SQL select]] statement can export the selected data in HTML file format.

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

'''Example'''

<code lang="recital">
select employeeid, lastname, firstname;
  from employees into html emp
</code>

===Export to JSON===
The [[SQL SELECT|SQL select]] statement can export the selected data in JSON file format.

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

'''Example'''

<code lang="recital">
select employeeid, lastname, firstname;
  from employees into json emp
</code>