Load Data from Local Files

Example Using a Local CSV File

Using this table format:

SQL

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:

SQL

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

Verify the file loaded correctly:

SQL

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.

CSV

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

Load Data from Local Files

On this page

Example Using a Local CSV File

CSV FILE DATA

Was this article helpful?

On this page

Was this article helpful?