MySQL Import and Export Excel and TXT file data

MySQL batch Import and Export Excel and TXT data

1. Excel files

1.1 export data to an Excel file

select * into outfile "C:\\Users\\Administrator\\Desktop\\data_out.xls"from bi_admin; 

Note that the "\" contained in the storage path needs to be translated.

1.2 import data from an Excel file

Method 1

• The Excel data is processed. The formula is used to spell the required data in the SQL statement. Here, there are only two columns of data. The formula is: = concatenate ("insert into bi_admin (name, PSD) values ('", A1,"', '", B1 ,"');")

• Click the lower-right corner of cell C1 and drag it down to insert a formula for all rows to execute the SQL statement.
• Run all SQL statements in MySQL.

Method 2

Convert the Excel file to TXT format and then import it to MySQL.

• First, save the Excel file as a CSV file (separated by commas ).

• Open the saved. CSV file by using the original statement. We found that the columns and columns are separated by commas (,).

• Convert it to a. txt file and encode it to select a UTF-8

Finally, execute the following SQL statement.

load data local infile "C:\\Users\\Administrator\\Desktop\\data_in.txt"into table bi_admin(name,psd) fields terminated by ',' lines terminated by '\n'

2. txt files

2.1 export data to a TXT file

select * into outfile "C:\\Users\\Administrator\\Desktop\\data_out.txt"FIELDS TERMINATED BY "\t"lines terminated by "\r\n"from bi_admin; 

By default, the separation between fields and records (rows) is \ t (Tab) and \ n. But it can be changed, such:
Fields terminated by ',' -- use, to separate fields
Lines terminated by ';' -- records are separated;

2.2 import data from a TXT file

load data local infile "C:\\Users\\Administrator\\Desktop\\data_in.txt"into table bi_admin(name,psd); 

The TXT data format meets the requirements. each item is separated by a tab key. If this field is null, \ n is used for representation.