LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 11-16-2010, 04:17 AM   #1
GreenSkyChris
LQ Newbie
 
Registered: Nov 2010
Posts: 3

Rep: Reputation: 0
How to import CSV into MySQL


Hi, im quite a newbie when it comes to MySQL but i heard that its possible to import data from a CSV into a mysql table.

Can anyone enlighten me on how to do it please? Im running CentOS and usiong MySQL Workbench to connect and run queries.

I have my CSV formatted so that the first row has the names of my columns on the table that I want to import it to and the following rows are the data that needs importing.

Cheers,

Chris,
 
Old 11-16-2010, 05:53 AM   #2
NilsO
LQ Newbie
 
Registered: Oct 2010
Posts: 2

Rep: Reputation: 0
Quote:
Originally Posted by GreenSkyChris View Post
Hi, im quite a newbie when it comes to MySQL but i heard that its possible to import data from a CSV into a mysql table.

Can anyone enlighten me on how to do it please? Im running CentOS and usiong MySQL Workbench to connect and run queries.

I have my CSV formatted so that the first row has the names of my columns on the table that I want to import it to and the following rows are the data that needs importing.
Heres something I used for some addressbook export and import I did with mysql and squirrelmail, address being the table name and if I dont remmeber wrong I used: use databasename first before attempting these commands:

output to file:
select * from address into outfile '/tmp/filename.csv' fields terminated by ',' ;

load data infile '/tmp/filename.csv' into table address fields terminated by ',';

I usually tries things like this on a testdatabase first time
I do it and not on production servers :-)
 
Old 11-16-2010, 06:04 AM   #3
prayag_pjs
Senior Member
 
Registered: Feb 2008
Location: Pune - India
Distribution: RHEL/Ubuntu/Debian/Fedora/Centos/K3OS
Posts: 1,159
Blog Entries: 4

Rep: Reputation: 149Reputation: 149
Hi,

See if this link helps

http://www.tech-recipes.com/rx/2345/...ly_into_mysql/
 
Old 11-16-2010, 07:14 AM   #4
GreenSkyChris
LQ Newbie
 
Registered: Nov 2010
Posts: 3

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by NilsO View Post
Heres something I used for some addressbook export and import I did with mysql and squirrelmail, address being the table name and if I dont remmeber wrong I used: use databasename first before attempting these commands:

output to file:
select * from address into outfile '/tmp/filename.csv' fields terminated by ',' ;

load data infile '/tmp/filename.csv' into table address fields terminated by ',';

I usually tries things like this on a testdatabase first time
I do it and not on production servers :-)
Thanks, it looks like that will work but it is saying access denied to "myuser@my.ip.address.0" and I can not log into mysql workbench as root. Any ideas on how to set it up so I can access the root account through mysql workbench?

Ive tried the following from http://benrobb.com/2007/01/15/howto-...cess-to-mysql/

Code:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
IDENTIFIED BY 'password' WITH GRANT OPTION;

FLUSH PRIVILEGES;

EXIT;
and then is say to edit "my.cnf" and comment out

Code:
bind-address = 127.0.0.1
but that bit does not appear on my "my.cnf"... the following bit is what is in my "my.cnf"

Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1



[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Last edited by GreenSkyChris; 11-16-2010 at 07:47 AM.
 
Old 11-17-2010, 03:30 AM   #5
GreenSkyChris
LQ Newbie
 
Registered: Nov 2010
Posts: 3

Original Poster
Rep: Reputation: 0
Please, can anyone help?

Thanks.

Last edited by GreenSkyChris; 11-17-2010 at 04:15 AM.
 
Old 11-18-2010, 01:20 AM   #6
NilsO
LQ Newbie
 
Registered: Oct 2010
Posts: 2

Rep: Reputation: 0
I'm not familiar with CentOS and what version of mysql it contains but
this is for mysql 5

http://dev.mysql.com/doc/refman/5.0/...rmissions.html

Nils.
 
Old 11-23-2010, 06:20 AM   #7
rylan76
Senior Member
 
Registered: Apr 2004
Location: Potchefstroom, South Africa
Distribution: Fedora 17 - 3.3.4-5.fc17.x86_64
Posts: 1,552

Rep: Reputation: 103Reputation: 103
This might be precisely what you don't want, but on my development server I simply become root, then copy the .csv file (usually saved out from OOCalc with default oocalc CSV options) into the same folder where my running MySQL instance stores its .myd etc. files.

Then I get into mysql (mysql -uuser -ppassword database) and execute

Code:
load data infile 'psms_contacts.csv' into table psms_contacts fields terminated by ',' enclosed by '"' lines terminated by '\n';
where

psms_contacts.csv is the file I copied into my MySQL data directory, and psms_contacts is the table that I want this .csv to go into.

After of course first making sure the table mentioned has columns that are of types compatible with the content of each relevant column in the .csv file.

Note that this is a shortcut, and risky (some would say godawfully stupid) way of doing it - you're not supposed to ever do stuff like this as root. But heck it works, and its quick, and I don't have wrestle as much. Plus my system is a dev system, completely isolated, and all the data on it is expendable.

Then I just delete the .csv file from the MySQL data directory. Note this is risky too, delete the wrong file and you can end up crashing your MySQL instance and losing data. For example, in the above, I need to delete

psms_contacts.csv

NOT EVER:

psms_contacts.myd (etc.)

as those are MySQL datafiles.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to import redhat users to ClearOS using CSV file format. alfaedh Linux - Server 1 01-31-2010 03:48 PM
Importing a csv file into a mysql database kaplan71 Linux - Server 13 09-18-2009 06:13 PM
LXer: Import CSV files into Drupal CCK nodes LXer Syndicated Linux News 0 02-09-2008 04:50 PM
LXer: Import CSV files into Drupal CCK nodes LXer Syndicated Linux News 0 02-09-2008 04:10 PM
Mysql CSV problems farmerjoe Linux - General 2 11-13-2004 11:21 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 11:50 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration