Import Access data to the Mysql database

Source: Internet
Author: User
The following article describes how to import Access-related data to the Mysql database. We all know that when creating a website, we often need to import and export some data to Mysql for processing. In Mysql databases, there are generally two ways to export data: 1. Use select * fromtable_nameintoou

The following article describes how to import Access-related data to the Mysql database. We all know that when creating a website, we often need to import and export some data to Mysql for processing. In Mysql databases, there are generally two ways to export data: 1. Use select * fromtable_nameintoou

The following article describes how to import Access-related data to the Mysql database. We all know that when creating a website, we often need to import and export some data to Mysql for processing. In Mysql databases, there are generally two ways to process data export:

1. Use

 
 
  1. select * from table_name into outfile “file_name”;

2. Use the mysqldump Utility

The following is an example:

Assume that one database in our database is samp_db, and one table is samp_table. Now we need to export the data of samp_table. We can use the following methods to achieve this:

Enter select * from samp_table into outfile "file_name" at the Mysql prompt ";

Enter mysqldump-u root samp_db samp_table> samp. SQL at the system command prompt.

(Of course, mysqldump has many options. For example,-d indicates that only the table structure is exported;-t indicates that only the table data is imported)

How to import data: Generally, we enter mysqlimport-u root samp_db samp_table.txt at the system command prompt (Note: The TXT file name must be named after the table name ). We can also use mysql-u root samp_db <file_name to import data exported by mysqldump. At the Mysql prompt, we use Load data infile "file_name" into table samp_table.

In addition to the above methods, we can import and export data to Mysql by copying files on the premise that the mobile database system is consistent with the original system. first, we can use mysqladmin-u root variables (at the system command prompt) or show variables (at the Mysql prompt) to find datadir. For example, in my environment, the directory is c: \ mysql \ data. Then copy some of the files. Everything is OK!

With the above knowledge, let's get started with the question: how to import ACCESS data into Mysql.

First, we export data from ACCESS as a text file. During the export process, select the field separator and text identifier, check the text file, and check whether a record is on the same line. If not, manually remove the Enter key. Assume that the exported text file is c: \ samp_table.txt. The content is as follows:

1. Zhang Xinhua, male

2. Jiang advanced, female

Next we use mysqlimport-u root-fields-terminated-by = "\," samp_db samp_table.txt, or load data infile "c: \ samp_table.txt "into table samp_table fields terminated by" \, "; Give It A Try (note the escape characters). Is everything OK! If not, take a closer look at the help of specific commands. Some options are listed below:

-- Fields-enclosed-by = char indicates that the column value should be included in the specified character. Quotation marks are usually used. The column value is not included in any character.

-- Fields-escaped-by = char indicates the escape character used to escape special characters. Lack of time-saving means no escape characters.

-- Fields-terminated-by = char specifies the characters of the columns to be separated. The missing time is assumed that the column values are separated by tabs.

-- Lines-terminated-by = str specifies the string that ends the input line (which can be multiple characters ). The missing time is assumed that the line is terminated by a line break. (Mobile Network pioneer)

The above content is an introduction to how to import Access data into Mysql.

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.