[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Import CSV into MySQL

Posted on 2007-07-30
7
Medium Priority
?
4,537 Views
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 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
Comment
Question by:mcgeorge40
7 Comments
 
LVL 24

Accepted Solution

by:
glcummins earned 2000 total points
ID: 19595607
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
 
LVL 15

Expert Comment

by:m1tk4
ID: 19595637
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
 
LVL 14

Expert Comment

by:ygoutham
ID: 19598102
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:mcgeorge40
ID: 19600400
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
 

Author Comment

by:mcgeorge40
ID: 19601168
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
 
LVL 15

Expert Comment

by:m1tk4
ID: 19606439
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
 

Author Comment

by:mcgeorge40
ID: 19642977
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

873 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