Importing CSV data into MySQL table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dirk Breeuwer
Forum Newbie
Posts: 6
Joined: Mon Sep 29, 2008 10:30 am

Importing CSV data into MySQL table

Post by Dirk Breeuwer »

I am creating a script so I can fill a MySQL table, from a CSV file created in Excel.

The CSV contains this:

Code: Select all

"1","Dirk Breeuwer","d78c03d72e72b44a131d255aec3c8a11","0","00:00:00",
"2","Pedro","d78c03d72e72b44a131d255aec3c8a11","0","00:00:00
"3","Javier","d78c03d72e72b44a131d255aec3c8a11","0","00:00:00",
The MySQL table has the following fields:
  • ID, username, password, voted, time, partido
My import script is the following:

Code: Select all

<?php
 
//database variables
$dbHost = "localhost";
$dbUser = "*******";
$dbPass = "*****";
$dbDatabase = "******";
 
//conect to database
 
$db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");
 
$query = "LOAD DATA INFILE 'users.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(ID, username, password, voted, time, partido)";
mysql_query($query) or die ('Error, query failed');   
   
 ?>  
When I run the import.php, I get the "Error, query failed" error. Anyone knows what I am doing wrong?

Also, by the way, does anyone know how to encrypt an Excel column? (With MD5 encryption). For example, for the passwords column. I need it to be encrypted.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Importing CSV data into MySQL table

Post by VladSun »

Field count...

Code: Select all

mysql_query($query) or die (mysql_error());
and strip the double quotes from the CSV.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Importing CSV data into MySQL table

Post by califdon »

Also, use LOAD DATA LOCAL INFILE. Refer to: http://www.modwest.com/help/kb6-253.html
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Importing CSV data into MySQL table

Post by VladSun »

Dirk Breeuwer wrote:Also, by the way, does anyone know how to encrypt an Excel column? (With MD5 encryption). For example, for the passwords column. I need it to be encrypted.
You don't need to encrypt it before the import ... Encrypt after the import by using UPDATE query and md5() SQL function.
There are 10 types of people in this world, those who understand binary and those who don't
Dirk Breeuwer
Forum Newbie
Posts: 6
Joined: Mon Sep 29, 2008 10:30 am

Re: Importing CSV data into MySQL table

Post by Dirk Breeuwer »

VladSun wrote:
Dirk Breeuwer wrote:Also, by the way, does anyone know how to encrypt an Excel column? (With MD5 encryption). For example, for the passwords column. I need it to be encrypted.
You don't need to encrypt it before the import ... Encrypt after the import by using UPDATE query and md5() SQL function.

I feel so ashamed of my self for asking this: How do I make it so the MD5 will aply to all passwords. I've been trying with this:

Code: Select all

UPDATE users SET password = MD5( '' ) WHERE S = 0
S is 0 for all users. So that is OK, and my problem is that it encrypts sets the same encrypted password for all users:

Code: Select all

d41d8cd98f00b204e9800998ecf8427e
What do I need to change.

Thanks for all the help. You've all helped me alot.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Importing CSV data into MySQL table

Post by VladSun »

Dirk Breeuwer wrote:I feel so ashamed of my self for asking this ...
You should be ;)

Here you are two SQL queries:

[sql]UPDATE    mytableSET    mycolumn = 1[/sql]
It will make ALL records to have field "mysqcolumn" set to 1. No WHERE clause

[sql]UPDATE    mytableSET    mycolumn = mycolumn + 1[/sql]
If the previous query has been executed, then thi one will make all records to have field "mycolumn" set to 2 ;)

Now you try ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Importing CSV data into MySQL table

Post by Christopher »

Did you get the error fixed? I suspect it was a path or permissions problem. Also the password SQL should be:

Code: Select all

UPDATE users SET password = MD5(password) WHERE S = 0
(#10850)
Dirk Breeuwer
Forum Newbie
Posts: 6
Joined: Mon Sep 29, 2008 10:30 am

Re: Importing CSV data into MySQL table

Post by Dirk Breeuwer »

Great.

Code: Select all

UPDATE users SET password = MD5(password) WHERE S = 0
That worked just fine. Thanks for all your help. Really appreciated.

Dirk Breeuwer
Post Reply