LinuxQuestions.org - How to import CSV into MySQL

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

- - How to import CSV into MySQL (https://www.linuxquestions.org/questions/linux-server-73/how-to-import-csv-into-mysql-844559/)

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,

Quote:

Originally Posted by GreenSkyChris (Post 4160484)

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 :-)

Quote:

Originally Posted by NilsO (Post 4160564)

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

Please, can anyone help?

Thanks.

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.

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.