Import Wizard Tab - SQLite Manager

Structure	Browse & Search	Execute SQL	DB Settings	Import	Export	On-Connect SQL

Files Formats Readable by SQLite Manager

CSV (Comma Separated Values)

Plain text file where the text is generally enclosed in quotes separated by commas or some other delimiter.
SQLite Manager is able to parse CSV files which use one of the following as a delimiter; commas, semicolons, pipes or tabs.

"Color_Name","HEX"
"Blue","#0000FF"
"Yellow","#FFFF00"
"Red","#FF0000"

SQL (Structured Query Language) Format

CREATE TABLE webcolor(Color_Name,HEX);
INSERT INTO webcolor (Color_Name,HEX) VALUES("Blue","#0000FF");
INSERT INTO webcolor (Color_Name,HEX) VALUES("Yellow","#FFFF00");
INSERT INTO webcolor (Color_Name,HEX) VALUES("Red","#FF0000");

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 of an XML text file, the opening and closing tags for the root element are <document> and </document>. Nested within the root element are three records from the "webcolor" table.

<document>
	<webcolor>
		<Color_Name>Blue</Color_Name>
		<HEX>#0000FF </HEX>
	</webcolor>
	<webcolor>
		<Color_Name>Yellow</Color_Name>
		<HEX>#FFFF00 </HEX>
	</webcolor>
	<webcolor>
		<Color_Name>Red </Color_Name>
		<HEX>#FF0000 </HEX>
	</webcolor>
</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 Text File - How To

Connect or attach the database to import to.
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 the format.
Select "Import" from the "Database" menu at the top of the screen which will open the Import Wizard tab.

Structure of the Import Tab

The Import Wizard tab has three sub tabs, one for each format that SQLite Manager supports.

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 a 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 and paste following into a text editor and save it as plain text with the file name of emailtable.sql

CREATE TABLE contactlist (FirstName TEXT,LastName TEXT,Email TEXT NOT NULL PRIMARY KEY);
INSERT INTO contactlist VALUES('Peter','Nelson','pnelson@oldmail.fake');
INSERT INTO contactlist VALUES('Alan','Reed','aj.reed@oldmail.fake');

Select the SQL sub tab from the Import Wizard Tab.
Browse for the emailtable.sql file and select it. Make sure that the BEGIN TRANSACTION/COMMIT statement box is unchecked since those statements are 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 next example we will be adding the records below to the contacts table. Copy the following text and paste it into a text file named email.csv

"Bob","Allen","robert.l.allen@email.fake"
"Martin","Kessel","kbird@myemail.fake"
"Ronald","Nelson","r.nelson@mymail.fake"
"Albert","Smith","allensmith@oldmail.fake"

From the CSV sub tab, browse for the email.csv file and select it.
Enter or select the following within the tab.

Name of table to import to - contactlist
Uncheck "First row contains column names"
"Fields separated by" radio buttons. Set value to Comma.
"Fields enclosed by" radio buttons. Set value to "Double quotes(") Always".

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. The first row of the CSV file does however contain column headings which exactly match the column names in the *contacts* table. The Import Wizard will use that first row as a guide to insert the records correctly provided that "First row contains column names" control is checked.

Paste the following in a text file named addemail.csv .

"Email","FirstName","LastName"
"gpmillford@mymail.fake","George","Millford"
"ka.simpson@mymail.fake","Kim","Simpson"

From the CSV sub tab, browse for the email.csv file and select it.
Enter or select the following within the tab.

Name of table to import to - contactlist
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.
"Fields separated by" radio buttons. Set value to Comma.
"Fields enclosed by" radio buttons. Set value to "Double quotes(") Always".

Click the "OK" button in the tab.

Create table from an Imported CSV File

Copy and paste the following into a text editor as save the file as newcontacts.csv

"Surname","Given Name","Email Address"
"Doe","James","james.doe@mymail.fake"
"Allen","Roberta","r.allen@mymail.fake"

By entering the following in the execute SQL tab, we can Copy the records from the newcontacts table to contacts table.

INSERT INTO contactlist(FirstName,LastName,Email) SELECT "Given Name","Surname","Email Address" FROM newcontacts;

Colin Riley --Updated February 2011--

Import Wizard Tab - For SQLite Manager