skip to Main Content

LOAD DATA LOCAL INFILE – Example

How to import data from csv, txt or other export files into MYSQL

Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_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 | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Example:

LOAD DATA LOCAL INFILE 'C:/Users/adamos/Desktop/data.csv' 
 INTO TABLE ak_data
 CHARACTER SET utf8
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n' 
 (`code`, `name`, etc);

* Notes:

  1. When using “CHARACTER SET utf8” make sure before the import to edit the imported text file and re-save it with encoding utf8.
  2. To escape column names (in case where a column name matches a reserved keyword), use the “`” character.
This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *



Back To Top