Overview

To import CSV file into MySQL, you need to create the table first because MySQL can't know in advance what are the data types of each column.

Here is how I import my CSV file into MySQL. My CSV file looks like this:

"Ticker ID","Exchange ID",ID
379,2,29
7106,5,30
392,2,31

Create table first

DROP TABLE IF EXISTS stock;
CREATE TABLE stock (ticker_id INT,
                            name VARCHAR(50),
                            exchange_id INT, 
                            nid INT);

Import CSV into table

LOAD DATA LOCAL INFILE '/path/to/file.csv'
    INTO TABLE stock
    FIELDS TERMINATED BY ','
    ENCLOSED BY ''
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (ticker_id, exchange_id, nid);

FIELDS TERMINATED BY is the field separator.
ENCLOSED BY is empty for me but usually, it is double-quote(").
LINES TERMINATED BY is the newline.
IGNORE 1 LINES is to ignore the first header line.
(ticker_id, exchange_id, nid) is list of columns that matches the columns between CSV file and the table. I explicitly omit name column.

Result

SELECT * FROM stock;
+-----------+------+-------------+------+
| ticker_id | name | exchange_id | nid  |
+-----------+------+-------------+------+
|       379 | NULL |           2 |   29 |
|      7106 | NULL |           5 |   30 |
|       392 | NULL |           2 |   31 |

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.