• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4553
  • Last Modified:

Import CSV into MySQL

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 2.9.1.1.

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!
0
mcgeorge40
Asked:
mcgeorge40
1 Solution
 
glcumminsCommented:
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."
0
 
m1tk4Commented:
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.
0
 
ygouthamCommented:
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 http://dev.mysql.com/doc/refman/5.0/en/load-data.html
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
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
mcgeorge40Author Commented:
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.
0
 
mcgeorge40Author Commented:
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:

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

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.

~Mike





0
 
m1tk4Commented:
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.
0
 
mcgeorge40Author Commented:
I changed hosting companies and re-posted this question at:

http://www.experts-exchange.com/?qid=22745633

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now