A simple solution to import Access data to the MySQL database

This article describes how to import Access data to the MySQL database. We all know that the actual operation process of website construction is as follows, it is often necessary to import and export some data to the MySQL database.

In MySQL databases, there are generally two ways to process data export:

1. Use 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 by copying files on the premise that the mobile database system is consistent with the original system. first, use MySQLadmin-u root variables at the system command prompt) or use 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 the MySQL database.

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 that separate columns. The missing time is assumed that the column values are separated by tabs.

Lines-terminated-by = str specifies that the string that ends the input line 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 importing Access data into the MySQL database. I hope you will have some gains.

Original article title: How to import Access data into MySQL

Connection: http://www.cnblogs.com/me115/archive/2010/05/07/1729843.html