Import CSV into MySQL

Posted on 2007-07-30
Last Modified: 2013-12-12
I have a very simple database.  Only two tables.  The data is currently in .csv format and it looks as though it is only seperated by commas.

My web host (GoDaddy) offers a simple Windows server, with ASP.  All I want to do is run a couple of simple queries using ASP.

GoDaddy allows me to create a MySQL DB from my hosting control panel.  I did that.  The version of MySQL is 5.0.

To run commands in MySQL, I have a hosting control panel function called phpMyAdmin

I was able to create two tables that are exactly what I need.  However, I have tried different methods-- inlcuding LOAD LOCAL-- to get the CSV data into the MySQL tables.  I am absolutely stuck.

Bottom line:  I have been trying for days to populate the MySQL tables with my CSV files and all I get are errors.

Is there not a simple way to do this?!?  I thank you in advance!
Question by:mcgeorge40
    LVL 24

    Accepted Solution

    PHPMyAdmin already has an import function build in. Just browse to your table, click the Import tab, and use the Browse button to find your CSV file. Make sure you select the option that says "CSV", and click "Go."
    LVL 15

    Expert Comment

    Open the CSV in Excel, verify that everything is ok there. If it is, create the MySQL table with matching fields, see that the field types match the data. The safest way is to create them all as varchar or text.

    Once done, use something like this:

    load data infile '(((your uploaded file name)))' into table MyTable fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n' (field1, field2, field3...)

    If your top line in CSV are field names, add

    ignore 1 lines

    before the field list.
    LVL 14

    Expert Comment

    if it is a comma separated value that you have and please note that all the fields must be marked with a separator and you cannot have some inbetween data where username is something like "smith, john"

    but if the field values are enclosed by a double quote then it is a simple question of loading the data straight into the database using

    load data infile

    command straight at the sql prompt.

    more at
    The only catch being that the csv file should have similar data structure as that of your table.  if not then you need to specify the list of columns in a similar order as that of your csv file

    Author Comment

    I'm getting some errors.  I'll post them here in a little while.  I have to make a conference call and then I'll be back.  Thank you so much for the reponses so far.

    Author Comment

    Okay, using myPHPadmin, I created two tables successfully like so:

          CREATE DATABASE georgemc;
          USE georgemc;
          CREATE TABLE blocks (
                startIpNum int(10) unsigned NOT NULL,
                endIpNum int(10) unsigned NOT NULL,
                locId int(10) unsigned NOT NULL,
                PRIMARY KEY (endIpNum)

          DROP TABLE IF EXISTS location;
          CREATE TABLE location(
                locId int(10) unsigned NOT NULL,
                country char(2) NOT NULL,
                region char(2) NOT NULL,
                city varchar(50),
                postalCode char(5) NOT NULL,
                latitude float,
                longitude float,
                dmaCode integer,
                areaCode integer,
                PRIMARY KEY (locId)

    So far so good.  Then I try to import the CSV data.  The data is on the root of the web server.  I also tried these command lines with a "C:\" file path-- that didn't work either.  So with the CSV on the root:

          LOAD DATA LOCAL INFILE '/tmp/GeoLiteCity-Blocks.csv'
                INTO TABLE geoip.blocks
                FIELDS TERMINATED BY ','
                OPTIONALLY ENCLOSED BY '\"'
                LINES TERMINATED BY '\n';
          LOAD DATA LOCAL INFILE '/tmp/GeoLiteCity-Location.csv'
                INTO TABLE geoip.location
                FIELDS TERMINATED BY ','
                OPTIONALLY ENCLOSED BY '\"'
                LINES TERMINATED BY '\n';

    I get the following error:


    MySQL said:  
    #1148 - The used command is not allowed with this MySQL version

    My server is using MySQL 5.0.  I also tried this with version 4.0 and I got the same error.

    I've been trying this for several days.  I am completely at my wits end.  Any help is greatly appreciated.


    LVL 15

    Expert Comment

    LOCAL works only if your server and your client both have been enabled to allow it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 5.6.4, Security Issues with LOAD DATA LOCAL.

    Please make sure you read the ENTIRE page recommended by ygoutham. You'll find 4 or 5 different things that may be wrong and need to be checked out.

    Author Comment

    I changed hosting companies and re-posted this question at:

    Thank you all for your help, but after switching hosts, I think I'm a lot closer than I was.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    The viewer will learn how to dynamically set the form action using jQuery.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 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

    25 Experts available now in Live!

    Get 1:1 Help Now