current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a .csv file contains data like this:

Username   Password   Email
test123    123        [email protected]

I'm currently using MySQL, and I have a table named user with some columns like this:

UserID    Username    Password    Email

What i'm looking for is how can I import my csv data into user table and convert password column with MD5 method so what I have at the last is

UserID    Username    Password                          Email
1          test123     202cb962ac59075b964b07152d234b70  [email protected]

NB: I have a hundred record in my csv file. Any help please. Thanks for advance.

share|improve this question
    
You shouldn't use md5 for password hashing. –  kelunik Jan 29 at 2:13
    
@kelunik Why I shouldn't? Any explanation please? –  Hendry Tanaka Jan 29 at 2:16
    
Because it's unsafe, there are a lot of resources out there, just use your favorite search engine. One example: security.stackexchange.com/a/19908 –  kelunik Jan 29 at 2:28
    
Thanks for advice. Maybe I'll make a change for my system later. –  Hendry Tanaka Jan 29 at 3:10

2 Answers 2

up vote 0 down vote accepted

The easiest solution would be to write a small script that reads that file and executes some queries to import these data sets.

If you want to use PHP (just one example, more information here) it would be something like:

    <?php

    $db = new PDO($connectStr, $username, $password);
    $q = $db->prepare("INSERT INTO table (...) VALUES (...)");

    $file = file_get_contents(...);
    $lines = explode("\n", $file);

    foreach ($lines as $line) {
        $data = explode(";", $line);
        // manipulate password here, for PHP, use password_hash instead of md5
        $q->execute([$data]);
    }
    ?>

Another promising solution could be LOAD DATA INFILE. You should have a look at http://dev.mysql.com/doc/refman/5.1/en/load-data.html for more information.

share|improve this answer
    
Thanks, refference from your answer I found my way. –  Hendry Tanaka Jan 29 at 3:32
up vote 1 down vote

What's below works for your data - be careful of any variation in the field width - that's why delimited files are better - csv, or better still, if you can get it, pipe-delimited (|). It also successfully deals with the header line.

I did the following:

My code will do for both MD5 and SHA2 passwords - uncomment the relevant bits.

CREATE TABLE my_user
(
  user_id int not null auto_increment primary key, 
  username varchar(11), 
  p_word varchar(64), -- VARCHAR(32) for MD5
  e_mail varchar(75)
);

using a sample based on your data (users.csv)

$> more users.csv;
Username   Password   Email
test123    123        [email protected]
test456    567        [email protected]
$>

And then after some Googling, I came up with this

LOAD DATA 
INFILE '/mysql/mdb5.5/inst/users.csv'
INTO TABLE my_user
IGNORE 1 LINES
(@row)
SET username = TRIM(SUBSTR(@row,  1, 11)),
    -- p_word   = MD5(TRIM(SUBSTR(@row, 12, 22))), 
    p_word = SHA2(TRIM(SUBSTR(@row, 12, 11)), 256),
    e_mail  = TRIM(SUBSTR(@row, 23, 80))
;

And voilà!

MariaDB [test]> select * from my_user;
+---------+----------+------------------------------------------------------------------+-----------------+
| user_id | username | p_word                                                           | e_mail          |
+---------+----------+------------------------------------------------------------------+-----------------+
|       1 | test123  | a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3 | [email protected] |
|       2 | test456  | 97a6d21df7c51e8289ac1a8c026aaac143e15aa1957f54f42e30d8f8a85c3a55 | [email protected]  |
+---------+----------+------------------------------------------------------------------+-----------------+
2 rows in set (0.01 sec)
share|improve this answer
    
Impressive!! +1 pal, another solution! –  Hendry Tanaka Jan 30 at 1:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.