Using MySQL LOAD DATA for Importing Text and Fixed‑Length Files

When operating MySQL databases, importing textual data is a common task; this guide demonstrates the use of the LOAD DATA statement for various import scenarios.

Basic parameters – The command typically uses a comma as the field delimiter and double quotes as the enclosure character. Example syntax:

-- Export basic parameters
select * into outfile '/data/mysql/3306/tmp/employees.txt'
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
from employees.employees limit 10;

-- Import basic parameters
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6);

Sample data and table structure – The article provides a CSV excerpt and the corresponding demo.emp table definition, showing fields such as emp_no , birth_date , first_name , last_name , fullname , gender , hire_date , modify_date , and delete_flag .

Scenario 1: File has more columns than the table – Create a temporary table with only the needed columns and map the file columns to them:

-- Create temporary table with two columns
create table emp_tmp select emp_no, hire_date from emp;

-- Load data into temporary table
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp_tmp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6)
set hire_date=@C6, emp_no=@C1;

Scenario 2: File has fewer columns than the table – Map the available columns and ignore the extra table columns:

load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6)
set emp_no=@C1,
    birth_date=@C2,
    first_name=@C3,
    last_name=@C4,
    gender=@C5,
    hire_date=@C6;

Scenario 3: Generating custom field data – Use MySQL functions to transform or generate values during import, such as converting names to upper/lower case, concatenating fields, setting timestamps, or applying conditional logic:

load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6)
set emp_no=@C1,
    birth_date=@C2,
    first_name=upper(@C3),
    last_name=lower(@C4),
    fullname=concat(first_name,' ',last_name),
    gender=@C5,
    hire_date=@C6,
    modify_date=now(),
    delete_flag=if(hire_date<'1988-01-01','Y','N');

Scenario 4: Fixed‑length data – Extract substrings based on known character positions and trim whitespace to populate table columns:

load data infile '/data/mysql/3306/tmp/employees_fixed.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@row)
set emp_no=trim(substr(@row,1,10)),
    birth_date=trim(substr(@row,11,19)),
    first_name=trim(substr(@row,30,14)),
    last_name=trim(substr(@row,44,16)),
    fullname=concat(first_name,' ',last_name),
    gender=trim(substr(@row,60,2)),
    hire_date=trim(substr(@row,62,19)),
    modify_date=now(),
    delete_flag=if(hire_date<'1988-01-01','Y','N');

Summary and best practices

The default import order follows column‑wise left‑to‑right and row‑wise top‑to‑bottom.

If the file structure differs from the table, assign explicit column mappings to avoid misplacement.

For large files, split them into smaller chunks (e.g., using split ) before loading.

After import, check Warning and ERROR counts and verify row counts with GET DIAGNOSTICS .

When extensive transformation is required, consider dedicated ETL tools or a preliminary MySQL import followed by processing.