CSV to database with php

Question

I have a little issue with importing data from .csv file to "ms access" database.

<form action="index.php" method="post" enctype="multipart/form-data">
            <input type="file" name="csv"/>
            <input type="submit" name="submit"/>
        </form>
<?php
if (isset($_POST['submit'])) {
            $i=0;
            require "connection.php";
            if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
                echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
            }
            //Import uploaded file to Database
            $handle = fopen($_FILES['csv']['tmp_name'], "r");
            $import=$db->prepare("INSERT INTO adherence(
                    dateandtime,
                    lastname,
                    paidtime,
                    approvedtime,
                    notadhering) VALUES(
                    ?,?,?,?,?)");
            while (($data = fgetcsv($handle, 1000, "\t", "'")) !== FALSE) {
                if($i>0) {
                $data = str_replace('"', '', $data);
                $myDate =  date("Y/m/d",strtotime(str_replace('/','-',$data[0])));
                $import->bindParam(1, $myDate, PDO::PARAM_STR);
                $import->bindParam(2, $data[1], PDO::PARAM_STR);
                $import->bindParam(3, $data[2], PDO::PARAM_STR);
                $import->bindParam(4, $data[3], PDO::PARAM_STR);
                $import->bindParam(5, $data[4], PDO::PARAM_STR);
                $import->execute();
                }
                $i++;

            }
            /*$removal=$db->prepare("delete FROM adherence WHERE approvedtime = '0' OR notadhering IS NULL");
            $removal->execute();*/

            fclose($handle);
            echo 'IMPORTED' ;
        }

CSV file:

Import result:

I have no clue, why my file is not being imported, and what is this random date. Please assist!

EDIT:

Var_dump:

EDIT 2

<form action="index.php" method="post" enctype="multipart/form-data">
            <input type="file" name="csv"/>
            <input type="submit" name="submit"/>
        </form>
<?php
if (isset($_POST['submit'])) {
            $i=0;
            require "connection.php";
            if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
                echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
            }
            //Import uploaded file to Database
            $handle = fopen($_FILES['csv']['tmp_name'], "r");
            $import=$db->prepare("INSERT INTO adherence(
                    dateandtime,
                    lastname,
                    paidtime,
                    approvedtime,
                    notadhering) VALUES(
                    ?,?,?,?,?)");
            while (($data = fgetcsv($handle, 1000, "\t", "'")) !== FALSE) {
                if($i>0) {
                $data = str_replace('"', '', $data);
                $myDate = date("Y-m-d H:i",strtotime($data[0]));
                $import->bindParam(1, $myDate, PDO::PARAM_STR);
                $import->bindParam(2, $data[1], PDO::PARAM_STR);
                $import->bindParam(3, $data[2], PDO::PARAM_STR);
                $import->bindParam(4, $data[3], PDO::PARAM_STR);
                $import->bindParam(5, $data[4], PDO::PARAM_STR);
                $import->execute();
                var_dump($data);
                }
                $i++;

            }
            /*$removal=$db->prepare("delete FROM adherence WHERE approvedtime = '0' OR notadhering IS NULL");
            $removal->execute();*/

            fclose($handle);
            echo 'IMPORTED' ;
        }

EDIT 3

if (isset($_POST['submit'])) {
            $i=0;
            require "connection.php";
            if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
                echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
            }
            //Import uploaded file to Database
            $handle = fopen($_FILES['csv']['tmp_name'], "r");
            $import=$db->prepare("INSERT INTO adherence(
  dateandtime,
  lastname,
  paidtime,
  approvedtime,
  notadhering)
  VALUES(:dateandtime, :lastname, :paidtime, :approvedtime, :notadhering)");
            while (($data = fgetcsv($handle, 1000, "\t", "'")) !== FALSE) {
                if($i>0) {
                $myDate = date("Y/m/d",strtotime(str_replace('/','-',$data[0])));   
                $import->execute(array(':dateandtime'=> $myDate, ':lastname'=> $data[1], ':paidtime'=> $data[2], ':approvedtime'=> $data[3], ':notadhering'=> $data[4]));
                }
                $i++;

            }
            /*$removal=$db->prepare("delete FROM adherence WHERE approvedtime = '0' OR notadhering IS NULL");
            $removal->execute();*/

            fclose($handle);
            echo 'IMPORTED' ;
        }

EDIT 4 FULL CODE

<form action="index.php" method="post" enctype="multipart/form-data">
            <input type="file" name="csv"/>
            <input type="submit" name="submit"/>
        </form>
<?php
if (isset($_POST['submit'])) {
            $i=0;
            require "connection.php";
            if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
                echo "<h3>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h3>";
            }
            //Import uploaded file to Database
            $handle = fopen($_FILES['csv']['tmp_name'], "r");
            try {
      $import= $db->prepare("INSERT INTO adherence(
                              dateandtime,
                              lastname,
                              paidtime,
                              approvedtime,
                              notadhering) VALUES(
                              ?,?,?,?,?)");

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

        if($i > 0) {
            $data = str_replace('"', '', $data); 

            $import->bindParam(1, $data[0], PDO::PARAM_STR);                
            $import->bindParam(2, $data[1], PDO::PARAM_STR);                
            $import->bindParam(3, $data[2], PDO::PARAM_STR);                
            $import->bindParam(4, $data[3], PDO::PARAM_STR);                
            $import->bindParam(5, $data[4], PDO::PARAM_STR);                
            $import->execute();
        }
        $i++;

    }
}

catch(PDOException $e) {  
    echo $e->getMessage()."\n";
}}

Connection is working. It is .MDB

Your CSV file's datetime doesn't match MySQL's `datetime` YYYY-mm-dd 00:00:00 format. — Funk Forty Niner, Nov 22 '15 at 19:21
Hard to say but you can try changing `$myDate = date("Y/m/d",strtotime` to `$myDate = date("d/m/Y",strtotime` see if that fixes it. — Funk Forty Niner, Nov 22 '15 at 19:35
also make sure that the column it is going into, is `datetime` type. — Funk Forty Niner, Nov 22 '15 at 19:39
I see your csv is missing another set of `:00`. can you work with a test `.csv` copy and modify those to read as `01/04/2015 00:00:00` and see what it does. Or `2015/01/04 00:00:00` — Funk Forty Niner, Nov 22 '15 at 19:47
ok but can you at least try it. If that fixes it, you'll know what to fix. — Funk Forty Niner, Nov 22 '15 at 19:54
or see if by adding on `:00` will work. I have a doubt, but worth a shot `$myDate = date("Y/m/d",strtotime(str_replace('/','-',$data[0]))) . " :00";` — Funk Forty Niner, Nov 22 '15 at 19:56
It gives fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22018]: Invalid character value for cast specification: -3030 [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. — JustinasT, Nov 22 '15 at 20:00
Possible duplicate of [INSERT into DB DateTime string](http://stackoverflow.com/questions/10539154/insert-into-db-datetime-string) — , Nov 22 '15 at 21:53
Use this for myDate: `$myDate = date("Y-m-d H:i",strtotime($data[0]));` If that doesn't work: Do `var_dump($data);` just inside the while loop and edit the question with that output — , Nov 22 '15 at 22:21
That "random date" means you're passing 0 as the second parameter to the date function. Show your updated code — , Nov 22 '15 at 23:20
With pdo, you can use named placeholders instead of just `?`. Try those? So your query is `INSERT INTO... VALUES (:theDate, :lastName, :paidTime, :approvedTime, :notadhering)` Then remove all the bindParam's and use `$import->execute(array(':theDate'=> $myDate, ':lastName'=> $data[0],...` and so on instead — , Nov 22 '15 at 23:51
@Terminus code (EDIT 3) gives errors: Notice: Undefined offset: 1 Notice: Undefined offset: 2 Notice: Undefined offset: 3 Notice: Undefined offset: 4 — JustinasT, Nov 23 '15 at 12:58

score 2 · Accepted Answer · edited May 23 '17 at 11:44

Your fundamental issue is you do not specify the comma delimiter in the fgetcsv() function. As a result, all the data pushed into one column, date[0] and the Access ACE/JET engine unable to convert to date/time of the first column rendered all to zero which in unix time begins at 1/1/1970.

Also, consider using try/catch to catch PDO exceptions.

try {
      $import= $dbh->prepare("INSERT INTO adherence(
                              dateandtime,
                              lastname,
                              paidtime,
                              approvedtime,
                              notadhering) VALUES(
                              ?,?,?,?,?)");

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

        if($i > 0) {
            $data = str_replace('"', '', $data); 

            $import->bindParam(1, $data[0], PDO::PARAM_STR);                
            $import->bindParam(2, $data[1], PDO::PARAM_STR);                
            $import->bindParam(3, $data[2], PDO::PARAM_STR);                
            $import->bindParam(4, $data[3], PDO::PARAM_STR);                
            $import->bindParam(5, $data[4], PDO::PARAM_STR);                
            $import->execute();
        }
        $i++;

    }
}

catch(PDOException $e) {  
    echo $e->getMessage()."\n";
}

As for dates, MS Access date/time field follows the current CPU's language clock such as MM/DD/YYYY (US) or DD/MM/YYYY (UK), etc. It does not follow the date/time format of other RDMS's of YYYY-MM-DD or such variants. So there is no need to convert since your csv aligned already to Access' format. With that being said, you may receive a warning:

SQLSTATE[22018]: Invalid character value for cast specification: -3030

However, according to my tests, the database updated all rows correctly. Normally, you would concatenate the # around date strings which I tried to no avail with your code but possibly in your bind parameters process, string types do not wrap ideally with the hashtags.

Yes, I get error: SQLSTATE[22018]: Invalid character value for cast specification: -3030. still -same failure — JustinasT, Nov 23 '15 at 11:39
Did you check database results? As mentioned from testing if you changed delimiter, csv records still append. This may be a PDO exception caught in `try/catch` not a PHP fatal error. Alternatively, make dateandtime a string variable and update it to another date/time field with `#` or [CDate()](http://www.techonthenet.com/access/functions/datatype/cdate.php) after insert. — Parfait, Nov 23 '15 at 14:54
Interesting. Code works here. If you comment out the PDO catch line, `SQLSTATE[22018]:` message does not appear. Did you refresh the Access table? Did you change the `fgetcsv()` arguments? Notice I remove the `\t` and `'` for comma `,`. If yes to all above, can you edit your post to add the PDO connection string to see driver/dsn and file type (.mdb or .accdb)? — Parfait, Nov 23 '15 at 19:42

CSV to database with php

1 Answers1

Linked