Insert values from csv file to MySQL table

Question

I'm trying to insert values extracted from a csv file to a mysql table. It runs but the table is not populated. I've tried to debug for the last XXXX but just can't see my error. Echo-ing out the values give me the correct SQL but when it comes to the INSERT - no dice.

Thanks very much for your help.

<?php 

$host = 'localhost';
$user = 'fulltime_admin';
$pass = 'secret';
$database = 'fulltime_db';

$db = mysql_connect($host, $user, $pass);
mysql_query($database, $db);

//////////////////////////////// EDIT //////////////////////////////////// 

$redirect_num = 500;   // Select how many rows to insert each time before refresh. 
// More rows = faster insertion. However cannot be too high otherwise it will timeout. 

$filename = "ps4_emails.csv"; // The file we are going to get the data from... 

$table = "`ps4_emails`"; 

////////////////////////////// END EDIT ////////////////////////////////// 

$file = file($filename); 
$lines = count($file); 

// Have we just redirected? 
$nextline = $_GET['nextline']; 
if (!isset($nextline)){ 
    $nextline = 0; 
} 

$query = "INSERT INTO ".$table." (email) VALUES ('".$final_line[0]."')";

for ($line=$nextline; $line<=$lines; $line++){ 

    $final_line = explode(",", $file[$line]); 

    if ($line!=$lines){ 
        mysql_query($query,$db); 

    } 

    if ($line % $redirect_num){ 

        // something needs to go here
    } else { 
        $nextline = $line+1; 
        exit ('<meta http-equiv="refresh" content="0;url=texttomysqlemails.php?nextline='.$nextline.'" />'); 
    } 

    echo  ( $line==$lines ) ? "Done" : ""; 

} 
?>

don't use mysql_\* functions. Use mysqli_\* or PDO with prepared statements instead. — Maru, Feb 19 '16 at 12:59
So how long do you expect this script to run for? How many rows are you loadig to your database — RiggsFolly, Feb 19 '16 at 13:16
Agreed. I initially had it as mysqli - out of desperation I changed it to mysql + agreed password is befitting the test server in which the dodgy code resides. — baselinej70, Feb 19 '16 at 13:20
test csv file has over 4000 rows but the file I have in mind has over 18 million rows and about 48 columns of data — baselinej70, Feb 19 '16 at 13:23
**SOLVED** Thanks everyone. @mr-engineer spotted my rookie mistake. Used mysql_query instead of mysql_select_db($database, $db) to connect to db. ^#$#@^@&(%!@!!## — baselinej70, Feb 20 '16 at 01:39
How long does it take to save the records in the db with the test file of 4000 records? Have you tried with the file of 18 million records? — Fernando León, Nov 28 '17 at 16:48

score 1 · Accepted Answer · answered Feb 19 '16 at 13:01

1

Put your query inside loop in order use it with variable $final_line.

Try this :

$final_line = explode(",", $file[$line]); 

if ($line!=$lines){ 
   $query = "INSERT INTO ".$table." (email) VALUES ('".$final_line[0]."')";
    mysql_query($query,$db); 
}

Don't use mysql_*. It's deprecated and removed from PHP 7. Use mysqli_* or PDO.

answered Feb 19 '16 at 13:01

Mr. Engineer

3,522
4
17
34

Do I need to define $final_line as an array? – baselinej70 Feb 19 '16 at 13:19
No you dont need to do that. PHP is Loosely Typed – Mr. Engineer Feb 19 '16 at 13:23
I tried that but it doesnt work. I've tested the database connection - that's fine. I've echoed out "INSERT INTO ".$table." (email) VALUES ('".$final_line[0]."')"; - that's fine too. I can see the email values in the SQL. AAARRRRRgh :) – baselinej70 Feb 19 '16 at 13:27
Run that query in phpadmin and see what happen. – Mr. Engineer Feb 19 '16 at 13:30
INSERT INTO ps4_emails (email) VALUES ('test@test.com') - works. I can see test@test.com in the ps4_emails table. How strange! – baselinej70 Feb 19 '16 at 13:54
1

Check your 6th line which is : `mysql_query($database, $db)`. It must be `mysql_select_db ()` – Mr. Engineer Feb 19 '16 at 15:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104002/discussion-between-baselinej70-and-mr-engineer). – baselinej70 Feb 20 '16 at 01:40

score 1 · Answer 2 · edited May 23 '17 at 12:15

This seems like a perfect script to run from the command line PHP CLI and therefore you can forget about all the refresh complexity.

If the file is huge, like your comment suggest, loading all the file into memory may also bring you up against the PHP memory limits, so it might be better to read a line at a time rather than the whole file using fgetcsv() which is intended for reading csv files.

<?php 

    $host = 'localhost';
    $user = 'fulltime_admin';
    $pass = 'secret';
    $database = 'fulltime_db';

    $db = mysql_connect($host, $user, $pass);
    mysql_query($database, $db);

    $filename = "ps4_emails.csv"; 
    $table = ""; 

    $handle = fopen('ps4_emails.csv', 'r');
    if ( ! $handle ) {
        echo 'File does not exists in this location';
        exit;
    }

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $query = "INSERT INTO `ps4_emails` (email) VALUES( '{$data[0]}')";        

        mysql_query($query,$db); 
    }
?>

You now just run this script from the command line/terminal like

>php script.php

And it can run for minutes/hours/days with no likleyhood of blowing any limits.

I have to mention this or someone will nag me for not saying it

Please dont use the mysql_ database extension, it is deprecated (gone for ever in PHP7) Especially if you are just learning PHP, spend your energies learning the PDO or mysqli_ database extensions, and here is some help to decide which to use

When you need to upload the real file, it would also be a good idea to add a restart mechanism, so you can restart the process from whereever a problem happened or someone shut the database down for a backup or some other unforseen hiccup.

<?php 

    $host = 'localhost';
    $user = 'fulltime_admin';
    $pass = 'secret';
    $database = 'fulltime_db';

    $restart_from = 0;

    $db = mysql_connect($host, $user, $pass);
    mysql_query($database, $db);

    $filename = "ps4_emails.csv"; 
    $table = ""; 

    $handle = fopen('ps4_emails.csv', 'r');
    if ( ! $handle ) {
        echo 'File does not exists in this location';
        exit;
    }

    // is it a restart?
    if ( file_exists('restart.txt') ) {
        // its a restart
        $restart_from = file_get_contents('restart.txt');

        // read up the file to the last good row inserted
        for ( $i=0; $i<=$restart_from; $i++ ) {
            $data = fget($handle, 1000);
        }
    }

    $upd_cnt = restart_from;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $query = "INSERT INTO `ps4_emails` (email) VALUES( '{$data[0]}')";        

        mysql_query($query,$db); 

        $upd_cnt++;
        file_put_contents('restart.txt', $upd_cnt);
    }
?>

The above restart code is not tested, but I have used something very like this in the past very successfully. So you will have to check I have not made any silly mistakes, but it should give you an idea of how to do a restart from the last row successfully updated before a crash.

score 0 · Answer 3 · edited May 23 '17 at 12:15

0

You can use LOAD DATA INFILE to insert from file.

refer http://dev.mysql.com/doc/refman/5.7/en/load-data.html

insert csv file data into mysql

edited May 23 '17 at 12:15

Community

1
1

answered Feb 19 '16 at 12:59

safin chacko

1,345
1
11
18

Insert values from csv file to MySQL table

3 Answers3