Task : Export the rows from a Table or View to a text file.
- Click on the name of the Table or View in the Database Objects Panel.
- Select the Structure tab and click on the Export button to bring up the Export Wizard Tab.
- Select the appropriate sub tab of the Export Wizard for the file type to be created
CSV
CVS text files can be parsed by virtually all spreadsheet and database programs. Generally the text from the tables columns will be enclosed in quotes and delimited by commas however other available delimiters in SQLite Manager are semicolons,pipes or tabs. There is also an option on the CSV sub tab to add the column names as the first row of the file.
SQL
SQL statements which will allow the recreation of table in another sql database including datatype criteria. If necessary the datatypes for the table columns can be edited or corrected in the Structure tab before exporting the table.
XML
XML element names may begin with an alphabetical character, an underscore or a colon. They may contain alphabetical characters,numbers, periods, underscores, colons or dashes. The latter two, colons and dashes are generally discouraged as some applications may misinterpret element names containing these characters as name spaces or as an equation to be evaluated. They must not begin with the letters "XML"
Format of Exported XML File
<?xml version="1.0" encoding="utf-8" ?>
<!--
GUID: sqlite-manager@sqlite-manager.googlecode.com
Homepage: http://sqlite-manager.googlecode.com
Generation Time: Fri, 4 Feb 2011 15:46:34 GMT
SQLite version: 3.7.1
-->
<!-- Database: databasename.db -->
<databasename.db>
<tablename>
<column_name_1>Record_1_Field_1_value</column_name_1>
<column_name_2>Record_1_Field_2_value</column_name_2>
<column_name_3>Record_1_Field_3_value</column_name_3>
</tablename>
<tablename>
<column_name_1>Record_2_Field_1_value</column_name_1>
<column_name_2>Record_2_Field_2_value</column_name_2>
<column_name_3>Record_2_Field_3_value</column_name_3>
</tablename>
</databasename.db>
Special considerations for XML
The presence of spaces or special characters in a column name will prevent the generation of markup within the exported XML text file. The file will be created but without the content. This commonly happens with views containing one or more expressions or table created from a select statement containing one or more expressions such as the one shown below.
SELECT LastName||', '||FirstName, hours*payrate FROM employee, timecard WHERE employee.id = timecard.id;
The above sql statement will create a result set with the column names of "LastName||, ||FirstName" and "hours*payrate", both of which would be unacceptable as XML element names.
To avoid this, column names that are not XML compliant should be followed by an alias enclosed with double quotes, which will generate a appropriate element name as shown below.
The following should create the desired result.
SELECT LastName||', '||FirstName "Name", hours*payrate "Gross_Pay" FROM employee, timecard WHERE employee.id = timecard.id;
Colin Riley --Updated February 2011--