Import CSV file directly into MySQL

Contributor Icon Contributed by katy8439 Date Icon May 8, 2007  
Tag Icon Tagged: MySQL

Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.


To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

You can then import it into a MySQL table by running:

load data local infile 'uniq.csv' into table tblUniq
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)

The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

Previous recipe | Next recipe |
 
  • Rafael
    Thank you. Short and sweet. Very straight to the point.
  • Ng
    Thanks, buddy!
  • robert
    thanks pal! finally somethin' working :)
  • ashikur
    Thank you!!! it really works !!!

    Copied the csv file into /var/lib/mysql/mydb at my ubuntu box.

    *mydb is the name of my database
  • Chris
    Thank you Thank you. Very nice.
  • priyadarshan
    very very superb 1 line query.. i love it !!!!!!!!!!!!!!
  • ir
    help!! can this be possible??
    (idnum,username,md5(password))
    how about if i used md5 in my password??
    and i want my password field converted into md5.
    email me pls. ir_bs756@yahoo.com
  • ashish
    hey
    please send full code of importing file into mysql database. at my email (ashish@tiainterweb.com)
    thanks
  • Okpali D Great
    the above code is can do it. You need to specify the url of the csv file with / slash and not \ .
  • zild
    Can MySQL automatically assign the column names using the column headers from the csv file?
  • vasu
    where to place the csv file...
  • Partha Kar
    Thank you very much.
  • @ir:
    If the passwords in your CSV file are not already MD5() then just import them as per the instructions above then run this query:

    UPDATE `users` SET `password` = MD5(`password`);

    @ashish:
    That is the "full code". There is no more. The only step you have to do before this is to create a table that matches the CSV file you already have. No one can tell you how to do that without having your CSV file. Don't forget to modify the query above to match the table you have just created.

    @zlid:
    No it can't. Fields are given names and types at the same time when the table is created. To assign the names automatically, MySQL would have to also assign the column types automatically which would mean scanning down through the entire CSV file to make sure that there were no letters in a column that started with numbers and finding the longest string in a column that started with a string and so on. There are too many options in table creation to allow it to be left up to software. NULL or NOT NULL ? DATE, DATETIME, TIME, CHAR, VARCHAR or TEXT ? What if you have a column full of numbers but one Excel error message that looks like "#value" ?

    You will have to create the table yourself before importing the CSV file.
  • Okpali D Great
    thanks very much
  • Ok, I must be missing something here. Are there more explicit instructions for this? Do I paste this code using phpmyadmin and putting it into the sql box? I'm a little lost here.
  • I wanted to add the fact that I already have my database set up in MySQL with all the appropriate tables. I created a test CSV file and messed around with it a bit but wasn't sure where and how I was supposed to implement it using the code you have given.
  • Gopi
    Thanks kaly...
  • jaky
    Thank you mate!
  • Sari
    Thank You!!! I have 7 MB CSV file successfully imported to Mysql in 1.6 seconds!
  • eqcc
    Thanks for the info! It worked perfectly! Thanks again!
  • akkhra
    Thanks, it is so important. But, I have 5 columns, it doesn't complet, it left the last column. It emty.Could you help me.
  • ecalvillo
    Thank you very much for this excelent recomendation, it works good.

    Ecalvillo.
  • sas12
    what if, we have two excel sheets in a excel file. How do we import ?
  • zedpe
    works fine. thanks a lot!!! do you know how can I schedule the sql statement once per day? i want to execute it every day at 3pm for example... thanx
  • saplamaev
    yes nice but i get an error message:

    File 'omg.csv' not found (Errcode: 2)

    Where to locate my csv file? in which directory?

    any help

    thanks
  • saplamaev
    where to place the csv file ?

    Couse i get an error message
    File 'omg.csv' not found (Errcode: 2)

    thanks
  • nancyeb
    saplamaev, I put the csv file into the same directory as my database. See if you have a ProgramData directory just below your Programs directory, probably on C drive. Inside that I have a MySQL directory. Keep digging until you find your database directory. Hope that helps. I also removed the word 'local' from the script. I have other problems. It keeps telling me fields are too long. :-(
blog comments powered by Disqus