[Android Application Development]-(22) Importing Excel Data to SQLite

Hello everyone, I haven't written a blog for a long time. Today, I will share with you how to import Excel Data to SQLite. Recently, I made a dial-up program and needed the call reminder function. There are several implementation methods for incoming call reminders, the first method is to use some developed interfaces to submit numbers for queries. This is impossible if no network connection is available. Finally, you can build a local database with an access number segment for query. There are up to 0.25 million telephone segments in China. The data is stored in an Excel table. This large amount of data can be used in SQL Server or Oracle. What can be done in SQLite? There must also be an import function, so after some learning. It turned out to be very simple, okay. Let's take a look at the operation.

I. Prepare environment settings for Import

First, check whether sqlite3 exists in the system and check the system/xlib directory.

# Ls/system/xbin/sqlite3

If not, download (including two files, sqlite3, libncurses. So), copy sqlite3 to the/system/xbin directory, and copy libncurses. So to the/system/lib directory.

At this point, create a database:

# Sqlite3/mnt/sdcard/test. DB

We can see that SQLite is now in progress. Next, enter. Help to view all the commands and instructions. You can move the information you are just interested in to the appendix after this article.

2. Prepare the data source

I originally saved an Excel file as a CVS file by saving it as a file. Pay special attention to this !! Use NotePad to open the CVS file and save it as a UTF-8, because SQLite is UTF-8 encoded by default, if the encoding is inconsistent, it will be garbled.

Create a table: Create Table phones (ID integer primary key, mobilenumber INTEGER (7), mobilearea varchar (16), mobiletype varchar (15), areacode varchar (4 ));

Set split string

#. Separator ","

Iii. Import Data

#. Import/mnt/sdcard/all.csv phones

For the preceding data import method, the sqlite3 command is required in the Android system. The simpler way is to use the visual database tool sqlitebrowser for import, which is simple and quick.

After the import is successful, perform related operations and find that there is no pressure on the retrieval of 0.25 million pieces of data, which can be basically done in several hundred milliseconds, in addition, the 13 m database is compressed and only a little more than 2 m after being connected .....

Iv. Appendix

SQLite>. Help

. Backup? DB? File backup dB (default "Main") to file

. Bail On | off stop after hitting an error. Default off

. Databases list names and files of attached Databases

. Dump? Table? ... Dump the database in an SQL text format

If table specified, only dump tables matching

Like pattern table.

. Echo on | off turn command echo on or off

. Exit exit this program

. Explain? On | off? Turn output mode suitable for explain on or off.

With no ARGs, it turns explain on.

. Genfkey? Options? Options are:

-- No-drop: Do not drop old fkey triggers.

-- Ignore-errors: Ignore tables with fkey errors

-- Exec: Execute generated SQL immediately

See File tool/genfkey. readme in the source

Distribution for further information.

. Header (s) on | off turn display of headers on or off

. Help show this message

. Import file table import data from file into table

. Indices? Table? Show names of all indices

If table specified, only show indices for tables

Matching like pattern table.

. Load file? Entry? Load an extension Library

. Log File | off turn logging on or off. file can be stderr/stdout

. Mode mode? Table? Set output mode where mode is one:

CSV comma-separated values

Column left-aligned columns. (See. width)

HTML <Table> code

Insert SQL insert statements for table

Line one value per line

List values delimited by. Separator string

Tabs tab-separated values

TCL list elements

. Nullvalue string print string in place of null values

. Output Filename send output to filename

. Output stdout send output to the screen

. Prompt main continue Replace the standard prompts

. Quit exit this program

. Read filename Execute SQL in Filename

. Restore? DB? File restore content of dB (default "Main") from File

. Schema? Table? Show the create statements

If table specified, only show tables matching

Like pattern table.

. Separator string change Separator Used by output mode and. Import

. Show show the current values for various settings

. Tables? Table? List names of tables

If table specified, only list tables matching

Like pattern table.

. Timeout MS try opening locked tables for MS milliseconds

. Width num1 num2... Set column widths for "column" Mode

. Timer on | off turn the CPU timer measurement on or OFF