How to import data from Access into MySQL

access|mysql| data in the process of building a website, often to deal with some data import and export. In a MySQL database, there are generally two ways to process the export of data:

1. Use SELECT * FROM table_name into outfile "file_name";

2. Using the Mysqldump utility

Here are some examples of how to:

Suppose there is a library in our database for samp_db, and a table for samp_table. Now you want to export the samp_table data. We can use the following methods to implement:

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.) As-D indicates only the table structure is exported;-T indicates that only table data is imported

How to handle the import of data: Generally we enter mysqlimport–u root samp_db samp_table.txt at the system command prompt (Note: This txt filename must be named after the table). For mysqldump exported data, we can also import it with Mysql–u root samp_db < file_name. At the MySQL prompt we use the load data infile "file_name" into table samp_table.

In addition to the above methods, for the mobile database system and the original system consistent with the premise, we can through the copy of the file to achieve data import and export. First we use Mysqladmin–u root variables (at the system command prompt) or with show variables (at the MySQL prompt) to find DataDir. As in my environment, this directory is in C:\mysql\data. Some of these files are then copied. Everything ok!

With the above knowledge, we get to the point: How to import data from Access into MySQL.

First, we export the data from access to a text file. During the export process, be careful to select a good field delimiter and text identifier, and look at the text file to determine whether a record is on the same line, if not, and then manually remove the ENTER key. If we export the text file as C:\samp_table.txt. Its contents are as follows:

1. Zhang Xin, male
2. Jiang Advanced, female

Next we use the Mysqlimport–u root–fields-terminated-by= "\," samp_db samp_table.txt, or use the load data infile "C:\\samp_table.txt" Into table samp_table fields terminated by "\,"; To try (note the escape character), is not everything ok! If not, please take a closer look at the help of the specific command. Some options are listed below:

--fields-enclosed-by=char indicates that the column value should be included in the specified character. usually in quotes. The default is to assume that column values are not included in any character.

--fields-escaped-by=char represents the escape character used to escape special characters. By default, no escape characters are represented.

--FIELDS-TERMINATED-BY=CHAR Specifies the character of the delimited column. By default, the column values are assumed to be delimited by tabs.

--LINES-TERMINATED-BY=STR Specifies the string that ends the input line (can be multiple