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, Uncategorized. Bookmark the permalink.

22 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

    • alex says:

      This is great, this is exactly what I have needed as I made a million insert statements over the last 2 days. I also assume to can make a php script to loop through a directory of CSV files and upload each one.

      Thanks!

  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.

  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.

  16. Alenoosh Baghumian says:

    Thank you very much, I finally was able to import a 98MB file 🙂

  17. John says:

    Keep receiving syntax error (Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL version for the right syntax to use near ‘mysqlimport –ignore-lines=1 –fields-terminated-by=, –columns=’COM_NAME, MASTER’ at line 1

    Here is the code:

    mysqlimport –ignore-lines=1 –fields-terminated-by=, –columns=’COM_NAME,MASTER_KEY,REPORT_DESCRIPTION,RPT_YEAR,REPORT_KEY,SHORT_DESCRIPTION,FIRST_NAME,MIDDLE_NAME,LAST_NAME,SUFFIX_NAME,NON_INDIVIDUAL,PAC_REG_NO,ADDRESS,CITY,STATE,ZIP,FILE_DATE,AMOUNT,EVENT_DATE,EMP_OCCUPATION,INKIND_DESCRIPTION,OTHER_INCOME,RCV_EVENT,CANDIDATE_FIRST_NAME,CANDIDATE_LAST_NAME,OFFICE,DISTRICT,PARTY’ –local -u root -p mapchange /Users/johntyler/Dropbox/Campaign Contribution Research/OHIO_2017-18/ohio_contributions_raw.csv;

    Any ideas?

  18. Gowtham says:

    –local

    It defines that the .csv should be available and read from the Local File System.
    So, if you placed your csv file in one system and executing this mysqlimport utility in another system, the file will be not be read and display “File not available in the specified path.”

  19. chris says:

    Pretty sure this is not longer supported as described; have tried every which way possible but get same error:

    mysqlimport: Error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”ID,Name,Phone,Address’)’ at line 1, when using table: testdata

  20. Pingback: How to import really huge CSV files very fast in one shot in remote MYSQL database server? | Unifav's space

  21. If you got this error:
    “mysqlimport: Error: 1054, Unknown column ‘column_name’ in ‘field list’, when using table: table_name”
    try to change the MySQL config or if you have root privilegies:
    SET GLOBAL local_infile = 1;

Leave a Reply

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