Mysql – import csv file into thesql with custom data change

csvimportMySQL

I have a .csv file contains data like this:

Username   Password   Email
test123    123        test@foobar.com

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  test@foobar.com

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

Best Answer

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.

Related Question