Load of data from text file with comma-delimited or tab-delimited structure.

Basic command:

LOAD DATA INFILE '' INTO TABLE 

Assumptions:

  • mySQL has to have read permitions for directory / file
  • NULL values in the must have format: \N  (\=escape character)
  • comma separated format requires CR (carriage return) line separator, tab delimited format requires LF (line feed)

Command for CSV format:

LOAD DATA INFILE '...' INTO TABLE ::::
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r'
  • If numeric fields are not enclosed in “” then we must use: OPTIONALLY ENCLOSED BY ‘”‘
  • if escape character is not ‘\’ we must specify it: ESCAPED BY ‘@’  (or different character
  • if file has a header line: IGNORE 1 LINES
  • if columns in file have different order we must specify column list: INTO TABLE … ( <columns in order present in file> )  – simillar like in INSERT command
  • if we need to import only some columns and discard others we use simillar command as above with column list if we want to not use some columns we use special variable like @SKIP or @DISCARD in their place in columns list – but we must specify full list of columns present in the file
  • if we need to do some operations with some columns during import we can also use @ variables in column list and specify formulas for other columns like: SET name=(@FIRST, ‘ ‘, @LAST)
  • date/ time columns we usually also have to treat as described above: SET dateofbirth= STR_TO_DATE(@IMPORTDATE, ‘%… %… %… ‘)