Sign inTry Free

Load Data from Local Files

On this page

The basic format for loading data into a table using is local file is:

LOAD DATA LOCAL INFILE '/example-directory/<file_name>'
    INTO TABLE <table_name>
    COLUMNS TERMINATED BY ',';

Example Using a Local CSV File

Using this table format:

CREATE TABLE employees(emp_id INT,
  emp_lastname VARCHAR(25),emp_firstname VARCHAR(25),
  emp_title VARCHAR(25),dept VARCHAR(25),emp_city VARCHAR(25),
  emp_state VARCHAR(5),emp_ccode VARCHAR(4));

Load data from a local file location:

LOAD DATA INFILE '/tmp/emp_data.csv' 
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

Verify the file loaded correctly:

SELECT * FROM employees;
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
| emp_id | emp_lastname | emp_firstname | emp_title     | dept      | emp_city     | emp_state | emp_ccode |
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+
|    102 |  Karras      |  Damien       |  Doctor       | R&D       | NYC          |  NY       |  US       |
|    110 |  Strode      |  Laurie       |  VP Sales     | Sales     | Haddonfield  |  IL       |  US       |
|     89 |  Whateley    |  Wilbur       |  CEO          | Sen_Mgmt  | Dunwich      |  MA       |  US       |
|    817 |  Eriksson    |  Oskar        |  Support      | IT        | Stockholm    |  NULL     |  SE       |
|    298 |  Denbrough   |  Bill         |  Salesperson  | Sales     | Bangor       |  ME       |  US       |
|    399 |  Torrance    |  Jack         |  PR Dir       | PR        | Estes Park   | CO        |  US       |
|    410 |  Wilkes      |  Annie        |  HR Mgr       | HR        | Silver Creek |  CO       |  US       |
|    312 |  Cady        |  Max          |  IT Dir       | IT        | New Essex    | FL        |  US       |
|    691 |  Grevers     |  Nick         |  Rep          | PR        | Grimnetz     |  NULL     |  CH       |
|     14 |  Bateman     |  Patrick      |  Prod_Mgr     | prod_dev  | NYC          |  NY       |  US       |
|     75 |  White       |  Carrie       |  Receptionist | HR        | Chamberlain  |  ME       |  US       |
|    263 |  MacNeil     |  Regan        |  R&D Mgr      | R&D       | Washington   |  DC       |  US       |
+--------+--------------+---------------+---------------+-----------+--------------+-----------+-----------+

CSV FILE DATA

Below is the contents of the csv file used in the examples above. 

014, Bateman, Patrick,Prod_Mgr, prod_dev, NYC, NY, US
102, Karras, Damien, Doctor,R&D, NYC, NY, US
298, Denbrough, Bill, Salesperson,Sales, Bangor, ME, US
399, Torrance, Jack, PR Dir, PR,Estes Park,CO, US
410, Wilkes, Annie, HR Mgr,HR,Silver Creek, CO, US
110, Strode, Laurie, VP Sales,Sales, Haddonfield, IL, US
312, Cady, Max, IT Dir, IT, New Essex,FL, US
089, Whateley, Wilbur, CEO, Sen_Mgmt, Dunwich, MA, US
075, White, Carrie, Receptionist, HR,Chamberlain, ME, US
263, MacNeil, Regan, R&D Mgr,R&D, Washington, DC, US
817, Eriksson, Oskar, Support, IT, Stockholm, NULL, SE
691, Grevers, Nick, Rep, PR, Grimnetz, NULL, CH

Last modified: September 26, 2023

Was this article helpful?

On this page

Was this article helpful?