How do I import ACCESS data into Mysql
- 2020-05-06 11:47:31
- OfStack
How do I import ACCESS data into Mysql and
Com 2001-10-6 extreme technology net
In the process of building a website, often has to deal with the import and export of some data. In the Mysql database, there are two ways to handle the export of data (generally).
1. USES select * from table_name into outfile "file_name";
2. USES the mysqldump utility
:
assumes that our database has a library of samp_db and a table of samp_table. Now you want to export the data for samp_table. Then we can use the following method to achieve:
At the Mysql prompt, type select * from samp_table into outfile "file_name";
At the system command prompt, type mysqldump , u root samp_db samp_table > samp.sql
(of course mysqldump has many options. For example, -d means that only the table structure is exported. -t means only import table data)
how to handle data import: generally we type mysqlimport , u root db samp_table.txt at the system command prompt. For the data exported by mysqldump, we can also use mysql , u root samp_db < file_name to import. At the Mysql prompt we use Load data infile "file_name" into samp_table.
In addition to the above methods, on the premise that the database system after moving is consistent with the original system, we can import and export data by copying files. First we use mysqladmin u root variables (at system command prompt) or show variables; (at the Mysql prompt) to find datadir. If in my environment, this directory is in c:\mysql\data. Some of these files are then copied. All ok!
Now that has this knowledge, let's get to the point: how to import ACCESS data into Mysql.
first we export the data from ACCESS to a text file. Select the field separator and text identifier during the export process, and check the text file to see if a record is on the same line, if not, manually remove the enter key. Suppose we export the text file c:\ samp_table.txt. It reads:
1, zhang xinhua, male
2, jiang xianjin, female
next we use mysqlimport root
root or load data infile "c:\\ samp_table.txt "\";
go try (note the escape character), is not everything ok! If not, take a closer look at specific commands for help. Here are some options:
-- fields-enclosed-by =char indicates that the column value should be included in the specified character. Usually in quotes. By default, column values are assumed not to be included in any character.
-- fields-escaped-by =char represents an escape character used to escape special characters. By default, there is no escape
-- fields-terminated-by =char specifies the character that separates columns. By default, column values are assumed to be separated by tabs.
-- lines-terminated-by =str specifies the end of the input line string (which can be multi-character). The default assumes that a line is terminated by a newline character
Com 2001-10-6 extreme technology net
In the process of building a website, often has to deal with the import and export of some data. In the Mysql database, there are two ways to handle the export of data (generally).
1. USES select * from table_name into outfile "file_name";
2. USES the mysqldump utility
:
assumes that our database has a library of samp_db and a table of samp_table. Now you want to export the data for samp_table. Then we can use the following method to achieve:
At the Mysql prompt, type select * from samp_table into outfile "file_name";
At the system command prompt, type mysqldump , u root samp_db samp_table > samp.sql
(of course mysqldump has many options. For example, -d means that only the table structure is exported. -t means only import table data)
how to handle data import: generally we type mysqlimport , u root db samp_table.txt at the system command prompt. For the data exported by mysqldump, we can also use mysql , u root samp_db < file_name to import. At the Mysql prompt we use Load data infile "file_name" into samp_table.
In addition to the above methods, on the premise that the database system after moving is consistent with the original system, we can import and export data by copying files. First we use mysqladmin u root variables (at system command prompt) or show variables; (at the Mysql prompt) to find datadir. If in my environment, this directory is in c:\mysql\data. Some of these files are then copied. All ok!
Now that has this knowledge, let's get to the point: how to import ACCESS data into Mysql.
first we export the data from ACCESS to a text file. Select the field separator and text identifier during the export process, and check the text file to see if a record is on the same line, if not, manually remove the enter key. Suppose we export the text file c:\ samp_table.txt. It reads:
1, zhang xinhua, male
2, jiang xianjin, female
next we use mysqlimport root
root or load data infile "c:\\ samp_table.txt "\";
go try (note the escape character), is not everything ok! If not, take a closer look at specific commands for help. Here are some options:
-- fields-enclosed-by =char indicates that the column value should be included in the specified character. Usually in quotes. By default, column values are assumed not to be included in any character.
-- fields-escaped-by =char represents an escape character used to escape special characters. By default, there is no escape
-- fields-terminated-by =char specifies the character that separates columns. By default, column values are assumed to be separated by tabs.
-- lines-terminated-by =str specifies the end of the input line string (which can be multi-character). The default assumes that a line is terminated by a newline character