Import the excel file into the database

Reference: blog.csdn.net/jayxujia123/article/details/13684313

Reference: kevin850115.iteye.com/blog/578142

MySQL official website: dev.mysql.com/doc/refman/5.7/en/load-data.html

Junior brother has an interview question, in the group asked how to import excel files into the database. I haven't tried it before. I happen to be learning MySQL. Practice it.

First, save the excel file as a comma-separated csv file and transfer it to the linux server, assuming it is called test.csv.

Use sed to remove the column name from the first row sed -i '1p' test.csv.

Then the command to build the database and table has to be manually carried out. According to the specific table structure, the general command is as follows:

> CREATE DATABASE `demo`;

> USE demo;

> CREATE TABLE `tb1` (

`id` int(4) NOT NULL PRIMARY KEY ,

`Name` char(10) DEFAULT NULL,

`Age` smallint(3) DEFAULT NULL,

`Sex` varchar(10) DEFAULT NULL,

`Mobile` bigint(15) DEFAULT NULL,

`Touzijingli` varchar(10) DEFAULT NULL,

`Job` varchar(10) DEFAULT NULL,

`Uname` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(Random test, so field type selection is a bit random, please ignore)

> desc tb1;

After ensuring that the table structure is consistent with our original EXCEL, you can import data as follows:

> load data local infile '/root/test.csv' into table tb1 fields

terminated by ','

optionally enclosed by '"'

escaped by '"'

lines terminated by '\r\n';

Parameter Description:

Terminated by is followed by a delimiter between fields.

Optionally enclosed by '"' means ignoring these double quotes.

escaped by character escape, default is backslash (backslash: \)

lines terminated by '\r\n'; newline flag. The new line under windows is\r\n

The official website has a more detailed format:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[PARTITION (partition_name,...)]

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS}

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number {LINES | ROWS}]

[(col_name_or_user_var,...)]

[SET col_name = expr,...]