How to import csv file into MySQL with php?

Question

Is there a way to import csv into MySQL using php?

I have an ImportData.php file containing the following code which doesn't produce an error message. The ImportData.php and abc.csv are in the same folder on a web server while MySQL is on another server.

If LOAD DATA LOCAL INFILE will not work, what other ways are there to import with php? I want to set a cronjob with that task.

$sql = "LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE tbl_test
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES";

LOCAL INFILE and another server is, if the file is on your maschine, not working — JOUM
– JOUM, Commented Sep 11, 2016 at 21:19
OK @Olaf Dietsche does have made the better point :) configuration! — JOUM
– JOUM, Commented Sep 11, 2016 at 21:20
"does not work" is neither an error message nor a meaningful diagnosis. What error does it emit? — symcbean
– symcbean, Commented Sep 11, 2016 at 21:55

Hrvoje Antunović · Accepted Answer · 2016-09-12 00:51:40Z

1

Related to your question, we're happy to have a few paths to resolve it:

1. LOAD DATA INFILE

A great MySQL feature that allows you to import a CSV file directly, in a single query.

The SQL command you're looking for is LOAD DATA INFILE

Manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Quick example:

    LOAD DATA INFILE 'fileName'
     INTO TABLE tableName
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (
    field1,
    field2,
    field3,
    @variable1,
    @variable2,
    etc
    )        
    set
    (
    field4 = concat(@variable1,@variable2)
    );

That's a fairly basic example, but it covers most of what you'd want. The manual page gives full details of how to do some very complex stuff with it.

**2. Parsing your CSV and inserting it programatically **

    set_time_limit(10000);


    $con = new mysqli("example.com", "user", "password", "database");
    if (mysqli_connect_errno()) {
    trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);} 


    $fp = fopen("file.csv", "r");

    while( !feof($fp) ) {
      if( !$line = fgetcsv($fp, 1000, ';', '"')) {
         continue;
      }

        $importSQL = "INSERT INTO table_name                VALUES('".$line[0]."','".$line[1]."','".$line[2]."')";

        $qry = $con->query(($importSQL) ;

        if($qry === false) {
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $con->error, E_USER_ ERROR);
     }  

    }

    fclose($fp);

Hope that helps.

edited Sep 12, 2016 at 0:51

answered Sep 12, 2016 at 0:13

Hrvoje Antunović

8416 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Félix Adriyel Gagnon-Grenier Over a year ago

I would advise against what you are actually proposing. The mysql_* extension is presently deprecated, one should never write new code with it. moreover, OP obviously know about LOAD DATA INFILE, they actually use a specific keyword to execute a specific need. Your solution actually makes it worse by preventing this specific need to be met.

Gray Programmerz Over a year ago

Warning: This will cause sql injection

Olaf Dietsche · Accepted Answer · 2016-09-11 21:20:45Z

0

See the MySQL manual about this LOAD DATA INFILE Syntax

The LOCAL keyword affects expected location of the file and error handling, as described later. LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 7.1.6, “Security Issues with LOAD DATA LOCAL”.

So you must first check your MySQL configuration, if it is allowed.

The alternative could be to parse the CSV file yourself, and import it line by line with an INSERT INTO statement.

answered Sep 11, 2016 at 21:20

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Collectives™ on Stack Overflow

How to import csv file into MySQL with php?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related