Import and Export Mysql database in EXCEL format

I. Import

(1) select the data block to be imported (multiple columns)

(2) copy the selected data block to another new table workbook, and then "save as"-> "text file (*. txt)".

(3) Create a mysql database and data table, and enter

Create a MySql database and table based on the data to be imported. The structure is consistent with the excel structure. Then, enter the command prompt and use the command

Mysql> load data infile 'd: \ data.txt 'into table X (fileds terminated by '\ t ');

Remove the carriage return symbol of the last column: UPDATE tablename SET lastcolumnname = REPLACE (lastcolumnname, '\ r ','');
 
II. Export

(1) Command Line input:

Mysql> select * into outfile 'd: \ data.txt 'from X (where colname = 'y ');

Or

SELECT * into outfile '/test.xls' FROM table1;

(2) open the excel file, select "data"> "import external data"> "import data", select data.txt, and click "OK ".

 
Appendix:

1. Clear the table content:

Delete from tablename;
Truncate table tablename;

(Non-recoverable data, fast efficiency)

2. Add a column to a position in the table:

Alter table 'tablename' add col int (10) after colname not null;