35

I'm having a bit of a problem trying to import data from a CSV and have a couple of questions on it that I haven't managed to solve myself yet.

First off here's my code to help put things in perspective (tidied it up a bit, removing CSS and DB connection):

<body>
<div id="container">
<div id="form">

<?php
$deleterecords = "TRUNCATE TABLE tablename"; //empty the table of its current records
mysql_query($deleterecords);

//Upload File
if (isset($_POST['submit'])) {
    
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded 
 successfully." . "</h1>";
        echo "<h2>Displaying contents:</h2>";
        readfile($_FILES['filename']['tmp_name']);
    }

    //Import uploaded file to Database
    $handle = fopen($_FILES['filename']['tmp_name'], "r");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $import="INSERT into importing(text,number)values('$data[0]','$data[1]')";
        
        mysql_query($import) or die(mysql_error());
    }

    fclose($handle);

    print "Import done";

//view upload form
} else {

    print "Upload new csv by browsing to file and clicking on Upload<br />\n";

    print "<form enctype='multipart/form-data' action='upload.php' method='post'>";

    print "File name to import:<br />\n";

    print "<input size='50' type='file' name='filename'><br />\n";

    print "<input type='submit' name='submit' value='Upload'></form>";

}

?>

</div>
</div>
</body>

It's basically an adaptation of an example I have found after many many attempts at various methods.

My CSV has two columns of data, the first one being text and the second is integers The table in the database also has two columns, the first called "text" and the second "number"

So the questions I have are:

  1. the text being uploaded is just being displayed as 0 in every field and i'm not sure why
  2. I keep reading about data ending up enclosed in "", if that happens how would I sort it?
  3. how can I ignore the first X lines of the CSV for headers etc?
  4. is the data format changed throughout this process or is it ready for me to use in a graph? e.g. would a decimal stay a decimal once placed in the database?

EDIT:

Just done a test of 10,000 record uploading and got the error:

"Fatal error: Maximum execution time of 30 seconds exceeded"

any thoughts?

Improve this question
5
  • 3
    If you are uploading a large file, also have a look at Load Data Infile as it is MUCH quicker. LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES; Commented Jul 12, 2012 at 9:01
  • @Fluffeh I tried using that method but for some reason it wouldn't work when I incorporated it into PHP. The speed would definitely be useful as it will be a large file in the end. Commented Jul 12, 2012 at 9:08
  • @Pidge: LOAD DATA INFILE works great in PHP: I have it working for me, loading a 300,000 line CSV file almost instantly. It's incredibly flexible, very fast, and makes any PHP loop-based solution look silly. Commented Jul 12, 2012 at 10:05
  • @Pidge: csv import mostly done by a cron so you can use any method. Commented Jul 12, 2012 at 10:23
  • mysqlimport can be used too : stackoverflow.com/a/56027148/1085978 Commented May 7, 2019 at 16:50

5 Answers 5

Reset to default
78

I answered a virtually identical question just the other day: Save CSV files into mysql database

MySQL has a feature LOAD DATA INFILE, which allows it to import a CSV file directly in a single SQL query, without needing it to be processed in a loop via your PHP program at all.

Simple example:

<?php
$query = <<<eof
    LOAD DATA INFILE '$fileName'
     INTO TABLE tableName
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (field1,field2,field3,etc)
eof;

$db->query($query);
?>

It's as simple as that.

No loops, no fuss. And much much quicker than parsing it in PHP.

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

Hope that helps

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

5 Comments

Sorry, I didn't even encounter in in my scouring of the web, obviously I was searching the wrong thing! If you could have a quick look to check at where I have put it I would be eternally grateful good sir :) (pastebin.com/e9wvsT4c) (saving spamming text everywhere again)
your pastebin code looks as if you've got the right idea. Some bugs still present (eg $filename isn't set), but I'm sure you'll work through those easily enough.
If you encounter, "access denied for load data infile in MySQL" switch LOAD DATA INFILE to LOAD DATA LOCAL INFILE . Source: stackoverflow.com/a/4162310/394969
Is this safe ? A malicious user could maybe put sql in the file and compromise DB ?
This also assumes that the database has access to the file in the first place which may not be so trivial if the database is running in an isolated container.
8 
$i=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i>0){
    $import="INSERT into importing(text,number)values('".$data[0]."','".$data[1]."')";
    mysql_query($import) or die(mysql_error());
}
$i=1;
}
Improve this answer

3 Comments

That fixed the issue with text :)
Out of curiosity, what part of this is making it ignore the first line of the CSV?
code diff with your code will get your answer. accept appropriate answer as accepted answer
2

letsay $infile = a.csv //file needs to be imported.

class blah
{
 static public function readJobsFromFile($file)
{            
    if (($handle = fopen($file, "r")) === FALSE) 
    {
        echo "readJobsFromFile: Failed to open file [$file]\n";
        die;
    }

    $header=true;
    $index=0;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
    {
        // ignore header
        if ($header == true)
        {
            $header = false;
            continue;
        }

        if ($data[0] == '' && $data[1] == '' ) //u have oly 2 fields
        {
            echo "readJobsFromFile: No more input entries\n";
            break;                        
        }            


        $a      = trim($data[0]);
        $b   = trim($data[1]);                 





        if (check_if_exists("SELECT count(*) FROM Db_table WHERE a='$a' AND b='$b'") === true)
        {

                $index++;
            continue;    
        }            

        $sql = "INSERT INTO DB_table SET a='$a' , b='$b' ";
        @mysql_query($sql) or die("readJobsFromFile: " . mysql_error());            
        $index++;
    }

    fclose($handle);        
    return $index; //no. of fields in database.
} 
function
check_if_exists($sql)
{
$result = mysql_query($sql) or die("$sql --" . mysql_error());
if (!$result) {
    $message  = 'check_if_exists::Invalid query: ' . mysql_error() . "\n";
    $message .= 'Query: ' . $sql;
    die($message);
}

$row = mysql_fetch_assoc ($result);
$count = $row['count(*)'];
if ($count > 0)
    return true;
return false;
}

$infile=a.csv; 
blah::readJobsFromFile($infile);
}

hope this helps.

Improve this answer

1 Comment

Even though I refactored some of your code for my use case, it was a great starting point. Thanks.
2 
set_time_limit(10000);

$con = mysql_connect('127.0.0.1','root','password');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db", $con);

$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]."')";

    mysql_query($importSQL) or die(mysql_error());  

}

fclose($fp);
mysql_close($con);
Improve this answer

1 Comment

@Pidge "Just done a test of 10,000 record uploading and got the error.." With this script I have import over 1M records from CSV. It should work for you.
-1

Database Connection:

 try {
        $conn = mysqli_connect($servername, $username, $password, $db);
        //echo "Connected successfully"; 
    } catch (exception $e) {
        echo "Connection failed: " . $e->getMessage();
    }

Code to read CSV file and upload to table in database.

   $file = fopen($filename, "r");
            while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) {
                $sql = "INSERT into db_table 
                   values ('','" . $getData[1] . "','" . $getData[2] . "','" . $getData[3] . "','" . $getData[4] . "','" . $getData[5] . "','" . $getData[6] . "')";
                $result = mysqli_query($conn, $sql);
                if (!isset($result)) {
                    echo "<script type=\"text/javascript\">
                            alert(\"Invalid File:Please Upload CSV File. 
                            window.location = \"home.do\"
                          </script>";
                } else {
                    echo "<script type=\"text/javascript\">
                        alert(\"CSV File has been successfully Imported.\");
                        window.location = \"home.do\"
                    </script>";
                }
            }
            fclose($file);
Improve this answer

1 Comment

Very bad practice to write java script directly in php just for redirection

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.