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
– Funk Forty Niner, Commented Nov 22, 2015 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
– Funk Forty Niner, Commented Nov 22, 2015 at 19:35
also make sure that the column it is going into, is datetime type. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 22, 2015 at 19:39

Community · Accepted Answer · 2017-05-23 11:44:35Z

2

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.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Nov 23, 2015 at 4:12

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

4 Comments

JustinasT Over a year ago

Yes, I get error: SQLSTATE[22018]: Invalid character value for cast specification: -3030. still -same failure

Parfait Over a year ago

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() after insert.

JustinasT Over a year ago

Not importing a thing

Parfait Over a year ago

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

Collectives™ on Stack Overflow

CSV to database with php

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related