MySQL Import export CSV file

Question: Why import Export to CSV file?

(1) The so-called Big data processing, only concerned about some columns of data, rather than the entire table structure, the data needs to be saved as CSV Common storage format, not only can be processed as a text file under Widows, but also can be processed on the Hadoop distributed system;

(2) MySQL database migrated to Oracle, SQL Server database, or conversely, due to their respective design, can not directly import files exported to DMB SQL format, may need to import data export into CSV, and the design of the data table structure may be through other means.

One: MySQL how to export text files (backup for Csv,txt, etc., more useful)

(1): The external arbitrary file type of the data exported to the specified format under MySQL mysql>

Use dbname

Database Changed

SELECT * from pollution into outfile ' g:\\arcgisworkspace\\zypdoc\\text.txt '; (Guide the data, note the escape character OH)

SELECT * from pollution to OUTFILE ' g:\\arcgisworkspace\\zypdoc\\text.csv ' fields TERMINATED by ' \, '; (output format Control)

The result is:

1, auto Exhaust, 200

2, building sand,

3, car painting, 160

4, coal,

5, others, 80

(2) Knowledge Supplement:

General form: SELECT [column name] from TableName [where]

into OUTFILE ' target file path ' [option]

Among the 5 options commonly used in the option parameter

Field TERMINATED by ' string ': Sets the string as the delimiter for the fields, the default value is \ t;

field enclosed by ' character ': sets the string to enclose a character type such as char, varchar text, and the default value is no symbol;

Fields optionally enclosed by ' character ': Sets the value of the field in the string, the default value is no symbol;

LINES starting by ' string ': Sets the character at the beginning of each line, and the default value is no character;

Fields escaped by ' character ': Sets the escape character, the default value is \;

LINES TERMINATED by ' string ': Sets the end of each line, the default value is \ n;

Such as:

SELECT * from pollution to OUTFILE ' g:\\arcgisworkspace\\zypdoc\\text2.csv ' fields TERMINATED by ' \, ' optionally enclose D by ' \ ' ' LINES starting by ' \> ' TERMINATED by ' \ r \ n ';

The result is:

>1, "car exhaust", "the "
>2, "Building the Sand", " the"
>3, "car painting", " the"
>4, "Coal", " the"
>5, "Other", " the"

(3): mysqldump Any file type that is exported as data in the specified format C:\Program files\mysql\mysql Server 5.5\bin>

Mysqldump-u root-p-T G:\arcgisworkspace\zypdoc\ ABC pollution "--fields-terminated-by=," (remember not to have any extra spaces or transfer characters;- P do not have to write password behind;

The directory is the folder, the file name is the table name, the suffix is the txt file)

(4) Knowledge Supplement:

Mysqldump-u root-p-T target directory dbname tablename [option]

option and the same as above MySQL, just change to

"--fields-terminated-by= character" (do not have any extra space, that is, fields-terminated-by tightly connected)

Two: MySQL How to import text files (more useful)

(1)Any external file type exported as data in the specified format under MySQL mysql>

First step: Create a data table for the corresponding field

CREATE TABLE Csv_test2 (ID int (8) Primary key,name varchar (), value Int (32));

(2) Insert: The type of the added value starts with the wrong design, how to change: Alter TABLE CSRV_TEST2 MODIFY column value varchar (32);

(3) Step two: Import external data

LOAD DATA INFILE ' d:\\tjdata_metro\\test\\mysql_infile3.csv ' into TABLE csv_test2fields TERMINATED by ' \, ' optionally Enclosed by ' \ ' ' LINES TERMINATED by ' \ r \ n ' Ignore 1 LINES (Id,name,value);

The above lines terminated by ' \ r \ n ' is a newline symbol that requires line breaks for Windows
The above ignore 1 lines is the header row that ignores the first row.

(3) More detailed database import and export see: MySQL Database Import and Export method summary (it is time to summarize)

MySQL Import export CSV file