Load data in txt file into database : Load Data Into Table « Backup Load « SQL / MySQL






  1. SQL / MySQL
  2. Backup Load
  3. Load Data Into Table

Load data in txt file into database

 

Drop table Bird;

CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
  
/* Suppose that your Bird records can be described as shown here. 
(Observe that MySQL expects dates in 'YYYY-MM-DD' format; 
this may be different from what you are used to.
*/

name    owner   species  sex   birth       death
Blue   Joe   cat      f      1993-02-04    
Yellow  Yin     cat      m      1994-03-17    
Red     Richard dog      f      1989-05-13    
    

/*
Note that if you created the file on Windows with an editor that uses 
\r\n as a line terminator, you should use:
*/

LOAD DATA LOCAL INFILE '/path/Bird.txt' INTO TABLE Bird
LINES TERMINATED BY '\r\n';








Related examples in the same category

1.load text data into table
2.Set division with
3.Line starting string
4.Importing CSV Files
5.Specifying the Datafile Format
6.To specify a file format explicitly, use a FIELDS clause to describe the characteristics of fields within a line
7.IGNORE is often useful with files generated by external sources.
8.Skipping Datafile Lines
9.Specifying the Location of Files on the Client Host
10.The syntax for a command to use a comma-separated text file, saved in a Windows format
11.IGNORE lines.
12.If you are using a Linux system, replace the '\r\n' in the LINES TERMINATED BY syntax command with just '\n'.