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
– Maru, Commented Feb 19, 2016 at 12:59
So how long do you expect this script to run for? How many rows are you loadig to your database — RiggsFolly
– RiggsFolly, Commented Feb 19, 2016 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
– baselinej70, Commented Feb 19, 2016 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
– baselinej70, Commented Feb 19, 2016 at 13:23

Mr. Engineer · Accepted Answer · 2016-02-19 13:01:07Z

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, 2016 at 13:01

Mr. Engineer

3,5254 gold badges20 silver badges34 bronze badges

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

7 Comments

baselinej70 Over a year ago

Do I need to define $final_line as an array?

Mr. Engineer Over a year ago

No you dont need to do that. PHP is Loosely Typed

baselinej70 Over a year ago

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 :)

Mr. Engineer Over a year ago

Run that query in phpadmin and see what happen.

Mr. Engineer Over a year ago

Check your 6th line which is : mysql_query($database, $db). It must be mysql_select_db ()

|

Community · Accepted Answer · 2017-05-23 12:15:39Z

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.

Community · Accepted Answer · 2017-05-23 12:15:39Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Feb 19, 2016 at 12:59

safin chacko

1,3901 gold badge11 silver badges18 bronze badges

Collectives™ on Stack Overflow

Insert values from csv file to MySQL table

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related