MySql import CSV file or tab-delimited file

Sometimes we need to import some data from another library into another library, and this data is stored in a CSV file. For MySQL database, we have two ways: one is to use commands to import, and the other is that many MySQL clients (such as Heidisql, Sequel pro) provide us with such functions. Below we will introduce two import methods respectively.

MySql command import

Preparation:

First create a table:

The table creation statement is as follows:

CREATE TABLE `city_china` (
 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
 `ename` VARCHAR(12) NULL DEFAULT NULL COMMENT 'Abbreviation of the province',
 `ecityname` VARCHAR(12) NULL DEFAULT NULL COMMENT 'City name',
 `ccityname` VARCHAR(12) NULL DEFAULT NULL COMMENT 'City name in Chinese',
 PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=81
;

Prepare the CSV file for import:

First, we create a new Excel file with the following content:

Then we convert the excel file to csv file. Operation: File ----> Save as:

Note: Fields are separated by commas by default. The primary key in the document can be left blank, and the order must be consistent with the order of the fields in the database. In addition, we noticed that the encoding format of our database is UTF-8, and our CSV file contains Chinese. If we import it directly, the Chinese may not be imported into the database. We need to change the encoding format of the CSV file to UTF-8. We have a simple way to convert the CSV format. The CSV file can be edited with Notepad. Open it with Notepad for editing, then select some encoding format when saving it.

Import Command

LOAD DATA INFILE is followed by the file path to be imported, INTO TABLE table name FIELDS TERMINATED BY field separation method (the default is comma , which can be changed to other separation methods) ENCLOSED BY semicolon separation, the default is line LINES TERMINATED BY line break IGNORE 1 ROWS ignore the first row (in this example, the first row is the corresponding field in the database. If the first row in your file is data, do not ignore the first row).

MySql Client

Here I use the HeiDiSql client as an example. After connecting to the database, there is a button on the upper left menu (to the left of the two little men), as shown in the figure:

Click and the following page will pop up:

Click Import and then OK.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.