Importing and Exporting Records Within SQLite Manager

Compatible File Formats

SQLite Manager is able import and export plain text files in three different formats; CSV, SQL and XML. These file formats are explained below. For the purpose of comparison the same table, npa_list has been written out in each format.

CSV (Comma Separated Values)

CVS text files can be parsed by virtually all spreadsheet and database programs. Generally the text will be divided into columns enclosed in quotes and delimited by commas. Other delimiter characters which can be used in SQLite Manager are semicolons, pipes or tabs.

"Area_Code","State"
"201","New Jersey"
"803","South Carolina"
"914","New York"

SQL(Structure Query Language) Format

A SQL text file will generally contain SQL statements which will allow the recreation of a table in another sql database including data type criteria. The SQL statements will be processed just as if you had typed them into the Enter SQL tab.

CREATE TABLE npa_list(Area_Code,State);
INSERT INTO npa_list (Area_Code,State) VALUES("201","New Jersey");
INSERT INTO npa_list (Area_Code,State) VALUES("803","South Carolina");
INSERT INTO npa_list (Area_Code,State) VALUES("914","New York");

XML (Extensible Markup Language) Format

At it's most basic, an XML file which can be parsed by SQLite Manager will have a root element consisting of an opening and closing tag in which one or more records are nested. In so far as the application is concerned, it is not important what that root element is, only that the opening and closing tags for the root are present and that they contain no blank spaces. Each record is enclosed within an opening and closing tag containing the name of the table that the record belongs to. Each field within a record is enclosed by an opening and closing tag with the column name. In the following example, the opening and closing tags for the root element are <document> and </document>. Nested within the root element are three records from the npa_list table.

<document> 		
	<npa_list> 	
		<Area_Code>201</Area_Code>
		<State>New Jersey </State>
	</npa_list> 	
	<npa_list> 	
		<Area_Code>803</Area_Code>
		<State>South Carolina </State>
	</npa_list> 	
	<npa_list> 	
		<Area_Code>914 </Area_Code>
		<State>New York </State>
	</npa_list>
</document> 

The XML example shown above can be successfully imported into SQLite Manager as is, however well formed XML files will start with a document type declaration such as <?xml version="1.0" encoding="utf-8" ?>

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.

Import File - How To

To import a text file, select Import from the Database drop down menu at the top of the screen. This will open the Import Wizard tab. Open the file to be imported in a text editor to examine it formatting so that you will be able to select the appropriate options within the Import Wizard Tab. .

It is important to understand is that SQLite Manager will not display an error message if it is unable to parse a file. The absence of any action on the part of the application after clicking OK in the Import Wizard tab is a good indication that there is something wrong with the formatting of the file to be imported. If the Import Wizard is able to parse the file then a dialog box will pop up which will prompt you to make any necessary changes to the column names, datatypes, etc. before building the table.

Import SQL File

Generally a SQL file will contain a CREATE TABLE statement for any INSERT statements contained in the file. Otherwise the table named in the INSERT statement must already exist in the database. If that is not the case then the file will not be successfully imported until a table is created into which the records can be inserted. Copy the following SQL statements and paste them into a text file with the name, contact.sql

CREATE TABLE contacts(FirstName TEXT,LastName TEXT,PhoneNum TEXT PRIMARY KEY);
INSERT INTO contacts(FirstName,LastName,PhoneNum) VALUES("Jane","Smith","201-555-4156");
INSERT INTO contacts(FirstName,LastName,PhoneNum) VALUES("Ronald","Simpson","201-555-5894");

# Select the SQL sub tab from the Import Wizard Tab. # Browse for the file and select it. Make sure that the BEGIN TRANSACTION/COMMIT statement box is unchecked since those statements will not be contained in this file. # A dialog box will ask you to confirm the operation. # You should be able to see the two records contained in the file, listed in the Browse & Search tab.

Import CSV File with out Headings into Existing Table

In the following example we will be adding the records below to the contacts table. Copy the following and paste it into a text file named contact.csv

"Alan","Allen","202-555-1323"
"Ellen","Rodgers","202-555-2432"
"Elizabeth","Martin","202-555-4532"

From the CSV sub tab. Browse for the contact.csv file and select it.

1. Name of table to import to - contacts
2. Uncheck First row contains column names
3. Fields separated by radio buttons. Set value to Comma.
4. Fields enclosed by radio buttons. Set value to Double quotes(") Always.
5. Click the OK button in the tab.

Import CSV File with Column Headings into Existing Table

In this example the columns in the CSV text are not arranged in the same order as they appear in the contacts table, however the first row of the CSV file does contain column headings which exactly match the column names in the contacts table. The Import Wizard will use that first row to insert the records correctly provided that the First row contains column names control is checked. Paste the following text into a text file named addcontact.csv.

"PhoneNum","LastName","FirstName"
"803-555-5741","Spitz","Mary"
"803-555-5735","Miller","Joyce"
"803-555-6425","Johnson","Raymond"

From the CSV sub tab. Browse for the contact.csv file and select it.

1. Name of table to import to - contacts
2. Check First row contains column names. If you fail to do so, the data in the records will not be inserted into the correct columns.
3. Fields separated by radio buttons. Set value to Comma.
4. Fields enclosed by radio buttons. Set value to Double quotes(") Always.
5. Click the OK button in the tab.

Import XML file into an Existing table

If a table with the same name as the second level tags in the XML file does not exist, then a table will be created and you will prompted to modify it. You will have the option of specifying the data type for each column and to identify a primary key. However when importing a XML file, you will not be able to change column names or select a table name to import records to within SQLite Manager. To do that, you must open the file first in a text editor and use the replace function to change the tags containing the table name(s) and or column name(s)

Copy and paste the following XML code into a text file with the filename addcontact.xml

<demo.db>
	<contacts> 
		<FirstName>Myron</FirstName>
		<LastName>Johnson</LastName>
		<PhoneNum>803-555-8978</PhoneNum>
	</contacts>
	<contacts> 
		<FirstName>Peter</FirstName>
		<LastName>Lamb</LastName>
		<PhoneNum>803-555-8925</PhoneNum>
	</contacts>
	 <contacts> 
		<FirstName>Janet </FirstName>
		<LastName>Roberts</LastName>
		<PhoneNum>803-555-1758</PhoneNum>
	</contacts> 		
</demo.db>

1. From the XML sub tab browse and select the addcontact.xml file.
2. Uncheck Use/Include type as attribute and click OK.
3. You will be then be prompted to confirm the operation.

Three more records should have been added to the contacts table.

Import CSV File Into New Table

Copy the following and past it into a text file with the filename of newcontacts.csv.

"given_name","surname","telephone"
"Robert","Sims","914-555-5254"
"Matthew","Thomas","914-555-5842"
"Karen","Thompson","914-555-2582"

Select CSV tab within Import tab

1. Name of table to import to - newcontacts
2. Check First row contains column names
3. Fields separated by radio buttons. Set value Comma
4. Fields enclosed by radio buttons. Set value to Double quotes(") Always.

Copy Records from Imported table to Existing Table

In the Execute SQL tab run the following SQL statement to copy the records to the contacts table.

INSERT INTO contacts(FirstName,LastName,PhoneNum) SELECT given_name,surname,telephone FROM newcontacts;

The records contained in the newcontacts table should have been copied to the contacts table.

Export

At the present time, there is no provision in SQLite Manager for printing tables or the result set for queries and views. It is however possible to export the data as plain text and to use it in an another application where it can then be formatted and printed.

Export a Table or View

Select the table or view to be Exported from the Database Objects Panel. Click on the Structure tab and click on the Export button to bring up the Export Wizard tab.

CSV Sub Tab

Within the CSV sub tab of the Export Wizard tab there is a check box to add the column names of the table or view as the first row of the file.

SQL Sub Tab

SQL statements which will allow the recreation of a table in another sql database including data type criteria. If necessary the data types for the table columns can be edited or corrected in the Structure tab before exporting the table.

XML Sub Tab

Format of Exported XML File Created by SQLite Manager

<?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 a table created from a CREATE AS SELECT statement containing one or more expressions such as the one shown below.

CREATE TABLE payroll AS 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.

CREATE TABLE payroll AS SELECT LastName||', '||FirstName "Name", hours*payrate "Gross_Pay" FROM employee, timecard WHERE employee.id = timecard.id;