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.

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

This entry was posted in Uncategorized. Bookmark the permalink.

15 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

    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


    and then re-enable them after load like this


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

  10. lando says:

    –local means that it’s pulling from the local filesystem of the client(your machine). If the file were on the server that it’s being run on then it wouldn’t need that flag. Most cases based on this tutorial would need –local as you are likely pushing a file to a remote db from your machine.

  11. Joe Masters says:

    Just used this to import 2.5 million rows in less than 4 minutes. Thanks!

  12. tehreem sarfraz says:

    when csv file import through sql after each step when query is generated then message shows that size of file is greater then the sql size.

  13. Anand Chhatpar says:

    I had to also add –fields-optionally-enclosed-by=” because some of my values in the CSV were like “ABC Company, Inc.” and without this option the import was getting messed up due to the comma in the value.

  14. Leye Odumuyiwa says:

    Thanks for this. Worked like a charm 🙂

  15. Gaurang Bhargava says:

    How to Write the data from local to particular table in MySQL.

Leave a Reply

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