How can I insert data from csv file into mySQL database (big data,fast way)?

Question

I want to insert data from a csv file, which is already on my server into my database.

$myfile = "./files/data.csv";

Here is my connection:

class Database 
{
    private static $dbName = 'dbname' ; 
    private static $dbHost = 'localhost' ;
    private static $dbUsername = 'dbuser';
    private static $dbUserPassword = 'dbpassword';

    private static $cont  = null;

    public function __construct() {
        exit('Init function is not allowed');
    }

    public static function

 connect()
    {

   if ( null == self::$cont )
   {      
    try 
    {
      self::$cont =  new PDO( "mysql:host=".self::$dbHost.";"."dbname=".self::$dbName, self::$dbUsername, self::$dbUserPassword);  
      self::$cont->query("SET NAMES utf8");
      self::$cont->query("SET CHARACTER SET utf8");
    }
    catch(PDOException $e) 
    {
      die($e->getMessage());  
    }
   } 
   return self::$cont;
}

public static function disconnect()
{
    self::$cont = null;
}

}

And this is how I insert the data into the database:

error_reporting(E_ALL); ini_set('display_errors', 1);

try {
   $pdo = Database::connect();
   $sql = "LOAD DATA INFILE '$myfile'
   INTO TABLE `animals`
   FIELDS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY '\"'
   LINES TERMINATED BY '\n'
   (id,name,animal)";
   $q = $pdo->query($sql);
   $q->execute();
} catch (PDOException $q) {
  echo 'Connection failed: ' . $q->getMessage();
}

My csv file looks like this:

12,fred,cat,
13,tom,dog,

And this is my animals table in my database

╔════╦═════════╦════════╦════════╗
║ id ║  name   ║  age   ║ animal ║ 
╠════╬═════════╬════════╬════════╣
║    ║         ║        ║        ║
║    ║         ║        ║        ║
║    ║         ║        ║        ║
║    ║         ║        ║        ║
╚════╩═════════╩════════╩════════╝

Unfortunately it is not working, this means no data is inserted into my database. I hope you can help :)

Is there any reason you can't just load it in directly? http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table — jeff carey, Nov 19 '15 at 18:27
Possible duplicate of [Importing CSV data using PHP/MySQL](http://stackoverflow.com/questions/11448307/importing-csv-data-using-php-mysql) — Rehmat, Nov 19 '15 at 18:27
`INTO TABLE 'animals'` remove the quotes or use ticks `\`` and your columns too `('id','name','animal')`. PDO error checking would have helped you out here http://php.net/manual/en/pdo.error-handling.php — Funk Forty Niner, Nov 19 '15 at 18:27
@Fred -ii- Thank you very much, I changed in my question everything you suggested. But unfortunately it is still not working — peace_love, Nov 19 '15 at 18:33
@Fred -ii- What is also strange that I do not get any error messages — peace_love, Nov 19 '15 at 18:34
http://php.net/manual/en/pdo.error-handling.php after you connected and http://php.net/manual/en/function.error-reporting.php for your PHP/file. Something should be throwing you something. — Funk Forty Niner, Nov 19 '15 at 18:35
thing is though, you have `('id','name','animal')` but no reference to the "age" column. Probably why, one reason anyway. — Funk Forty Niner, Nov 19 '15 at 18:36
@Fred -ii-: Yes, because I do not get "age" from the csv file. It is something which will be added later to the database — peace_love, Nov 19 '15 at 18:37
`error_reporting(E_ALL);` does not "display". You need to do `error_reporting(E_ALL); ini_set('display_errors', 1);` inside all files. — Funk Forty Niner, Nov 19 '15 at 18:38
@Fred -ii-: Changed it, still no errors displayed on my page! I do not know why :( — peace_love, Nov 19 '15 at 18:40
you also didn't remove the quotes for `('id','name','animal')` a quote `'` and a tick `\`` are 2 different animals. check your column types and lengths also. — Funk Forty Niner, Nov 19 '15 at 18:40
also try changing `$q = $pdo->prepare($sql);` to `$q = $pdo->query($sql);` you're not really preparing anything. — Funk Forty Niner, Nov 19 '15 at 18:41
you're welcome and I'm out of ideas. See the answer given below. Good luck — Funk Forty Niner, Nov 19 '15 at 18:45

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

There are factors on the MySQL server side that can effect the behavior of using LOAD DATA INFILE.

MySQL Config

MySQL has its own restrictions on both local and server filesystem access. One such system setting is --secure_file_priv, which you can read more about in the manual.

--secure_file_priv

This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. By default, this variable is empty. If set to the name of a directory, it limits import and export operations to work only with files in that directory.

So if this setting is non-empty you will either need to move the file to the configured directory in order for MySQL to be able to read it, or you will need to change it accordingly.

Also make sure to supply the absolute file path in your code since relative paths will be meaningless to the server.

MySQL User Privelages

See the MySQL Manual Section 6.2.1 Privileges Provided by MySQL for more details on how user privileges can effect using LOAD DATA INFILE and SELECT .. INTO OUTFILE (i.e. the user connecting to MySQL needs to have FILE privileges to be able to use these statements). That would be your self::$dbUsername in this case.

Filesystem Permissions

Finally, the MySQL user needs to have sufficient filesystem permissions to read from the given file or otherwise it won't be able to complete the operation. You could insure this isn't a problem by using chmod() to turn on world read permissions. Typically your umask should already cover this, but best to verify first rather than assuming it does.

Better Error Handling

Also, since you're using try/catch here, it's probably important to note that you may want to put PDO in exception mode, since it will otherwise not throw exceptions in the event your MySQL server returns an error, such as in this case.

You can do this in your constructor call to PDO like so...

new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

See the manual on Errors and error handling for details.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 19 '15 at 18:42

Sherif

11,786
3
32
57

error reporting would have signaled something to that effect, wouldn't it? having no access to the file would have produced something like "unable to open file"... etc. – Funk Forty Niner Nov 19 '15 at 18:45
@Sherif, thank you I changed it to the real path, but my code is still not working – peace_love Nov 19 '15 at 18:46
Not necessarily, no. This isn't a PHP error. It's a MySQL error. You could get the error information back from the MySQL server through PDO if you set PDO in exception error mode, or check `errorInfo()`, but it's not clear that the OP did that in this case. – Sherif Nov 19 '15 at 18:47
I told the OP to use exceptions in comments; I got nothing back. – Funk Forty Niner Nov 19 '15 at 18:48
@Jarla, yea it's not just about changing the path. Please read my full answer and check the MySQL manual links provided as there are multiple things that you may need to change on the MySQL side that could be causing this. One of them would be the secure file priv for local data infile and the other could also be the file permissions for the user. – Sherif Nov 19 '15 at 18:49
Thank you, I will check it out. Apart from this, you think my code looks ok? – peace_love Nov 19 '15 at 18:50
@Jarla Notice I say that you must both provide the full path **and** change your mysql config, *or just move the file to the privileged directory where MySQL is expecting to find it*. You can check your MySQL error logs for details and I'm confident you will find the error information informing you of the security error there. – Sherif Nov 19 '15 at 18:51
@Sherif: Wow, thank you very much for your very detailed explanation. Unfortunately I cannot get it work. I called my host where to configure the secure_file_priv, but they could't help me also. They said it is maybe not possible to change it. – peace_love Nov 20 '15 at 13:05
Have you tried checking the mysql user permissions first? `--secure_file_priv` may not even be the problem. See better **error handling** part of my answer to get some more detailed error information on what's going wrong here. – Sherif Nov 20 '15 at 13:38

score 0 · Accepted Answer · answered Nov 23 '15 at 11:48

Finally I got it work. The reason was actually no server permission issue. It was a problem in the database connection.

Here is the working code:

$pdo = Database::connect();

$sql = "LOAD DATA LOCAL INFILE 'http://www.mypage.myfile.csv'
        INTO TABLE test
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"' 
        LINES TERMINATED BY '\n' 
        IGNORE 1 LINES
        (id,name,animal)";

$con=mysqli_connect("localhost","dbuser","dbpassword","dbname");

if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result = mysqli_query($con, $sql);

if (mysqli_affected_rows($con) == 1) {
   $message = "The data was successfully added!";
} else {
   $message = "The user update failed: ";
   $message .= mysqli_error($con); 
};

echo $message;
mysqli_close($con);

How can I insert data from csv file into mySQL database (big data,fast way)?

2 Answers2

MySQL Config

MySQL User Privelages

Filesystem Permissions

Better Error Handling