MySQL Import and Export Excel and TXT file data

Source: Internet
Author: User
Tags mysql import

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.