import csv file to table

Posted on 2006-04-23
Last Modified: 2013-12-30
Hello Experts. I have an xls (excell) spreadsheet that I exported to csv. I want to create a table in MySQL and populate it with the data from this spreadsheet, accordingly. What's the easiest way to do this?

Many thanks in advance,
Question by:John Account
    LVL 12

    Expert Comment

    you can easily import these data into mysql table with a piece of PHP code. I would go that way, easy and quick.
    LVL 12

    Expert Comment

    // connect to your database
    $handle = fopen("data_file.csv", "r");
    $data = fgetcsv($handle, 1000, ",");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    // collect all data in tha line by 0,1,2 etc

    // form insert query  to insert into mysql


    LVL 8

    Accepted Solution

    Hi. Try

    mysql_query("LOAD DATA INFILE 'filename.csv' INTO TABLE `your_table`");
    LVL 4

    Expert Comment

    What KennyTM said would work

    you said that you have Excel CVS; Excel CVS is a MS™ CVS, meaning that it's almost like it should be but it's like 5% off.
    so there is a small chance that it will not work, because Excel saves the CVS "wrong". 1 of the things it doesn't do is adding "" between string fields. It does it only for fields which have a " (double quote) or the seperator in the sentence.


    1;this is a string
    2;"this is a ""string"""
    3;"this is a; string"

    see what i mean here ?
    now, mysql isn't stupid and can live with situation 1 & 3. However, it can not live with situation 2; an escaped double quote (") in microsoft land is "". In the rest of the world, an escaped double quote is \". This means we'll have to do some search&replacing in the CVS file before mysql LOAD DATA INFILE can use the Excel CVS file.

    A small (prolly not the fastest) function i wrote for this when i had to deal with this exact situation:

    $inputcsv = "input.csv";
    $outputcsv = "output.csv";

    $fp = fopen($inputcsv, "r");
    $fp2 = fopen($outputcsv, "w");
    $teller = 0;
    while ($stream = fgets($fp, 4096)){

            $outputline = str_replace("\"\"", "\\\"", $stream);
            fwrite($fp2, $outputline);



    your output.csv file will be usable in the query KennyTM just showed.

    hope this helps,

    LVL 8

    Expert Comment

    AndyAelbrecht, thanks for pointing that out. However, situation 2 can in fact be overcome using the FIELD ENCLOSE BY keywords as you can see in . Also in the same page there is already a solution:


    (Search: Posted by Norman Santos on September 14 2004 8:53am)

    Hope this helps.
    LVL 4

    Expert Comment

    that does take care of situation 1 & 3 but not for situation 2 afaik ?

    the optionally enclosed by lets the MySQL CVS parser know that strings can be enclosed by "" but it doesn't tell the mysql cvs parser that it should treat "" as \" ?

    well, it could work ofcoz, and it's been some time since i wrote this script (like 9 months or so); i'm pretty sure there was a good reason for me to do so, but i could be wrong and can't test your solution right now :(
    LVL 8

    Expert Comment

    In fact it isn't "my" solution; the solution is just copied from the comments in the MySQL documents.
    (I can't test it either because my host's MySQL server and web server are different, that LOAD DATA INFILE cannot access (at least I don't know how to) cross two servers. But I believe that the writer of that solution have tested it before posting...)

    Author Comment

    by:John Account
    As far as the "Qoutations" goes, that isn't an issue with me. I did a search & resplace for all of them--so now there aren't any in my csv file. However, in order to run the following query, how do I indicate the path to my csv file?--

    mysql_query("LOAD DATA INFILE 'filename.csv' INTO TABLE `your_table`");
    LVL 8

    Expert Comment

    The safest option is to use the full path...

    mysql_query("LOAD DATA INFILE '/tmp/file.csv' INTO TABLE `your_table`");


    mysql_query("LOAD DATA INFILE 'C:/path/to/the/file.csv' INTO TABLE `your_table`");

    Author Comment

    by:John Account
    That's exactly what I had tried before, but still getting the following error:
    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 us...

    Author Comment

    by:John Account
    The columns are-- ID (auto_increment), Yr, Title, Description
    (Nulls are not allowed)
    Here's a sample of some of my csv file:

    Skydive,1,Skydive 1,John goes out for his civillian skydiving certification. This is his 1st jump thereof.*LearningToFly1.htm
    Skydive,2,Skydive 3,His 3rd jump towards certification.*LearningToFly3.htm
    Skydive,3,Guantanamera,No Description Available.*Guantanamera.htm
    Skydive,4,Wonderful,A wonderful day to be skydiving.*Wonderful.htm
    Skydive,5,Skydive Flip,John doesn't just like to fall through the sky--he likes to have fun falling. Here he does a flip.*SkydiveFlip.htm
    Skydive,6,1st HoolaHoops,John, out in the sky with a group of other skydivers doing hoolahoops. This is the 1st one of that day.*HoolaHoops1.htm
    Skydive,7,2nd HoolaHoops,John, out in the sky with a group of other skydivers doing hoolahoops. This is the 2nd one of that day.*HoolaHoops2.htm
    Skydive,8,Both HoolaHoops,John, out in the sky with a group of other skydivers doing hoolahoops. This is a video of both hoolahoop jumps of that day.*BothSkydiveHoola.htm
    Skydive,9,Roland Skydives,Roland needed a good adrenalin fix--and that's exactly what he got from his brother, John, for Christmas. He was scared. But he aint no joke--he leaps right into fear.*RolandSkydives.htm
    2004p,10,A day at the BioDome,John (Gabe) & Family. spend a day at the world reknown BioDome in Montreal, in 2004*BioDom.html
    2004p,11,Winter Scenes in Montreal 04,A Montreal Winter in 2003*WinterScenes.html
    2004p,12,Robert Medina in Montreal,Robert Medina visits Gabe in Montreal*RobertMontreal.html
    2004p,13,Gabe visiting from Montreal, 04,No Description Available*BackFromMontreal.html
    2004p,14,Camping Trip-7/31/04,No Description Available*CampingJuly31.html
    2004p,15,Circus Trip 04,No Description Available*MikeCirucus.html
    2004p,16,Camping Trip 04,No Description Available*Camping04.html
    2004p,17,Donna's son, James,No Description Available*James.jpg
    2004p,18,Mike--The Professional,No Description Available*ProMike.html
    2004p,19,Mike & Family. At Monster Inc 04,No Description Available*MonsterInc04.html
    2004p,20,Mike & Family. Christmas 2003,No Description Available*Mike-Xmas-2003.html
    2004p,21,Birthday Party 04,No Description Available*Rolands35B-Day.html
    2004p,22,Roland & His New Car,No Description Available*RolandsLexis.html
    2004p,23,LouisBday04,No Description Available*LouisB-Day04.html
    2004p,24,Christmas Wonderland,No Description Available*WinterWonderland.html
    LVL 8

    Expert Comment

    Several things you can do...

    1) Try using LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.
    2) There may be errors in the CSV file after you've done the search & replace stuff. Try using the original CSV without any modification.
    3) Try some simple CSV and see whether this is the statement's error or the CSV's error. For example:


    4) Check if you has enough priviledge to LOAD DATA. (I don't think this is the cause though, because the error is "syntax error".)

    Author Comment

    by:John Account
    Yes, I have enough priveledges. Still, nothing is working as per your instructions. So, just to let you know, in case this makes any difference, MySQL is on a dedicated server--Not on a computer here at my desk. I connect to mysql Remotely via MySQL Control Center 0.9.4-beta. Does that make any difference?
    LVL 12

    Expert Comment

    yes, mysql cannot be accesed from a remore machine by default, you will have to setup that.
    LVL 12

    Expert Comment

    if this is the case you may get access denied error, try connecting to the server with a sample connection script...

    If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

    Host ... is not allowed to connect to this MySQL server

    You can fix this by setting up an account for the combination of client hostname and username that you are using when trying to connect.

    If you do not know the IP number or hostname of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. (Then change the '%' in the user table row to the actual hostname that shows up in the log. Otherwise, your system is left insecure because it allows connections from any host for the given username.)

    Login to MySQL Server(host on which MySQL Server is running)

    [root@mmkserv test]# mysql -usqladmin -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4 to server version: 4.0.20-log

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> GRANT ALL ON *.* TO 'EdwardPeter'@'%' IDENTIFIED BY '123';
    Query OK, 0 rows affected (0.14 sec)

    Query OK, 0 rows affected (0.16 sec)

    mysql> \q

    Hope this helps!

    Expert Comment

    I' m trying to import a file with primefaces . i m using jpa

    That's ok.

    I'm having dificulties with read the data to put into my table (Items) on MySQL.

    i have done the import , but still tryed read with java poi , jtable csvreader and others ... all of that  i ve gor erros.

    i will put a example that Works witout merge in table.

    public class FileBean {
          private String diretorioDestino = "c:\temp\";

          private UploadedFile file;

          public UploadedFile getFile() {
                return file;

          public void setFile(UploadedFile file) {
                this.file = file;

          public void send() throws IOException {
                FacesContext context = FacesContext.getCurrentInstance();
                if (getFile() == null) {
                      context.addMessage(null, new FacesMessage("Erro",
                                  "Escolha um arquivo"));
                } else {
                      String nomeArquivo = file.getFileName().substring(
                                  file.getFileName().lastIndexOf("\") + 1);
                      Upload(nomeArquivo, file.getInputstream());
                      context.addMessage(null, new FacesMessage("Sucesso",
                                  "Arquivo importado com sucesso"));

          private void Upload(String fileName, InputStream in) {
                try {
                      OutputStream out = new FileOutputStream(new File(diretorioDestino
                                  + fileName));
                      int reader = 0;
                      byte[] bytes = new byte[(int) getFile().getSize()];
                      while ((reader = != -1) {
                            out.write(bytes, 0, reader);
                } catch (IOException e) {



    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now