SQLite Expert import Excel tables (including usage issues, and inserting data from one table into another)

First, SQLite import Excel

1. Open the Excel you want to import---Save As (2007 click on the Office icon in the upper left corner)---Save type: CSV type

(Hint what is incompatible with what, all points OK.) )

2. Open SQLite Expert, select the database file (either an existing DB file that was imported or create a new. db database file yourself).

Right-click the file name---Select the last import text file (CSV,TSV)

3, (if not built and this Excel name the same table) to the right destination select the first item New table, the name is XXX.

If you choose the second item, make sure that you have other tables in your database (or create your own), which means you can insert data from an Excel table into an existing table. (Make sure that the column name, which is the best field name.) ）

Of course you can also browse to the CSV file you want to add by looking at the "..." icon in the top right.

4. Finally click on the bottom left corner start to begin importing data. When you're done, the newly created table XXX will appear on the left, and you can see the imported data by tapping the right-hand column in the middle of the screen. (indicates how many lines have been imported.)

second, may be reported errors.

Situation one: field is missing (that is, the name is missing)

The reason is that the first row of your Excel table adds a field (that is, the column name), such as what age name type or other Chinese.

My workaround:

Delete all fields from the first line of the CSV, create a new field in SQLite expert directly with the SQL statement, click SQL, enter the following statement to create the field, press the Execute sq button. (Represents the creation of a table persons, with 4 field names, types, number of characters that can be entered) the desired field has been established successfully, so just follow the first step to import the CVS data into the table.

Situation two: Too much data can lead to incomplete (natural bug)

because there are more than 8W of data, can only guide more than 1W at a time, still can't find the reason. It's really annoying to get more than four o'clock in the afternoon in the morning. I remember a colleague who told me half a month ago that I had to do it several times before I thought of it. It's really nice to be here to thank my co-workers for Lee Shing.

My workaround:

The first guide may only have more than 1W data, and then you open your CSV file, delete the imported data, save the CSV file, continue to import with SQLite expert, the next time may be 8k, may be 1W, repeat your steps until fully imported into the table. (that is, the CSV file does not add the first row of fields, direct to the existing table, import once, to see how much data column guide.) Then delete the import in the CSV to continue repeating.)

third, (the same. db database) Inserts data from one table into another table.

Save your Excel as a CSV file as described above (see the first step)

Add the following two lines of SQL statements directly to the SQLite Expert's SQL module (add Table B's data to table a later)

Statement

INSERT INTO Table A

SELECT * FROM Table B

Statement

It is important to note that the role here is primarily applicable to table B (no first row field) and is added directly to table A. (So there is a field that becomes the data inserted in table a)

And if the field name is different, inserting the data will result in an error. (e.g. inserting data from [123] tables into the persons table)

Personal experience, I hope to help you solve the same problem.

# # # Writer:jiayi < thankyou! > # # #

SQLite Expert import Excel tables (including usage issues, and inserting data from one table into another)