Page 1 of 1

Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 1:39 pm
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.

Re: Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 2:28 pm
by VladSun
Field count...

Code: Select all

mysql_query($query) or die (mysql_error());
and strip the double quotes from the CSV.

Re: Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 2:59 pm
by califdon
Also, use LOAD DATA LOCAL INFILE. Refer to: http://www.modwest.com/help/kb6-253.html

Re: Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 3:43 pm
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.

Re: Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 4:47 pm
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.

Re: Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 5:00 pm
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 ;)

Re: Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 5:07 pm
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

Re: Importing CSV data into MySQL table

Posted: Wed Oct 01, 2008 5:18 pm
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