txt file into mysql

Hits: 0

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

MySQL usually uses the [insert] statement to write data, such as

But sometimes in order to insert large batches of data or exchange data more quickly, it is necessary to import data from or export data to text.
1. Create a test table and prepare data
First, create a table that represents student information for testing. The fields include id, name, age, city, and salary. Id and Name cannot
be empty.

create table person( id int not null auto_increment, name varchar(40) not null, city varchar(20), salary int, primary key(id) )engine=innodb charset=gb2312;

The screenshot of creating the table is as follows: Then write a text file for import: c:/data.txt. Zhang San 31 Beijing 3000 Li Si 25 Hangzhou 4000 Wang Wu 45 /N 4500 Xiao Ming 29 Tianjin /N Each item is separated by the Tab key. If the field is NULL, it is represented by /N. 2. Import data Enter the command to import.

load data local infile “c:/data.txt” into table person(name,age,city,salary);
The screenshot of imported data is as follows: where local means local. After execution, you can see that NULL data is also imported correctly. 3. Export data Now this table as a text file: c:/data_out.txt.

select name,age,city,salary into outfile “c:/data_out.txt” lines terminated by “/r/n” from person;
The screenshot of the exported data is as follows: where lines terminated by “/r/n” means that each line (that is, each record) is separated by /r/n, and /r/n is the newline character of the window system . The exported data_out.txt has exactly the same content as data.txt. 4. Operating environment Windows vista home basic MySQL 5.1.34-community 5. Note The separation between fields and the separation between records (lines) is /t (ie Tab) and /n by default. But it can be changed, such as: FIELDS TERMINATED BY ‘,’ — fields are separated by LINES TERMINATED BY ‘;’ — records are separated

You may also like...

Leave a Reply

Your email address will not be published.