How to Import a Large CSV file to MySQL

I recently had to import a large csv file into mysql. Here’s how I did it. Also be sure that you have an ssh account/access to the terminal on the computer with the sql server.

1. Create the table you will store the items in.

Take a look at the top few lines of your csv file (using the head command makes this really easy). You might see something like this

Some Large Database
ID,Name, Phone, Address
1,James,505-234-2123,3456 Super St.
2,Mark,254-342-6732,7351 Roger Rd.
...

 

Notice that the csv file is set up with four columns.The first step is to create a table in a mysql database that has the three columns as fields in the table. I’m not going to show the specifics, but if you don’t know how to create a table in mysql google it. Remember if your database is big, be sure to put the index/primary/unique keys on before you upload the database.

2. Use the mysqlimport utility

Now comes the magic, here’s the command to upload your file.

mysqlimport  --ignore-lines=1 --fields-terminated-by=,
--columns='ID,Name,Phone,Address' --local -u root -p
Database /path/to/csvfile/TableName.csv 

Ignore-lines skips the first line “Some Large Database.” The –fields-terminated by tell the utility that the commas separate the columns. The –columns is used to map the order of the data in the csv file to the SQL database table. –local is very important. I’m not sure why it’s there but importing a csv file won’t work without it. The Database is the name of the database in which your table is stored. You must put the absolute path of the csv file for it to register with the utility. The “TableName.csv” has to match the name of the table in your mysql database.

For more information on the mysqlimport utility take a look at it’s reference page.
http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Hopefully this makes someone’s life easier. Leave a comment below if it does.

This entry was posted in Everything Penguin Related. Bookmark the permalink.

9 Responses to How to Import a Large CSV file to MySQL

  1. LouisNguyen says:

    Thank for sharing … it was very helpful
    I just import 210k line csv file to my server and it was hella fast.

  2. James says:

    Thanks a lot for this .. it saved my day!!

  3. Nestor de Haro says:

    Wow it’s incredible fast…thanks for your suggestion!

  4. I also am not exactly sure but I suppose that –local is for localhost

  5. Vladislav Krakhalev says:

    You told what

    –local is very important. I’m not sure why it’s there but importing a csv file won’t work without it.”

    It’s beacuse this option tells to mysqlimport what file what you load should be in a local host. (read here)

  6. Sanath says:

    LOAD DATA command in MySQL is the best and fastest way to import a huge CSV

    LOAD DATA LOCAL INFILE filename.csv
    INTO TABLE table name
    FIELDS TERMINATED by ’,’
    LINES TERMINATED BY ’\n’

    I have written a php script which can help you in uploading a csv file . You can download it from here

    Link to My Blog post for more info about the script

  7. Zach Lau says:

    This was very nice. Thank you for sharing. Imported a 22Gb csv file.

  8. Joshua Jacobson says:

    Chris, you wrote “be sure to put the index/primary/unique keys on before you upload the database.” From a performance perspective, it’s actually better to create these after you load the data. Otherwise, the database has to keep calculating the indices as it loads the data. If you’re data doesn’t have a primary key, it does make sense to create that (usually with AUTO_INCREMENT) before loading the data. If your table already has indices, you can disable them before loading like this


    ALTER TABLE TABLE_NAME_HERE DISABLE KEYS;
    SET FOREIGN_KEY_CHECKS = 0;
    SET UNIQUE_CHECKS = 0;
    SET AUTOCOMMIT = 0;

    and then re-enable them after load like this


    ALTER TABLE TABLE_NAME_HERE ENABLE KEYS;
    SET UNIQUE_CHECKS = 1;
    SET FOREIGN_KEY_CHECKS = 1;
    COMMIT;
    SET AUTOCOMMIT = 1;

  9. Very useful information. Thank you. But I tried using HeidiSQL UI. Was equally good.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>