CSV to Access database

Question

I got in some dead-end. I have csv file, delimited by commas, but I'm not able to import data to database. What can be causing this? No errors, simpy no data getting imported

Import code:

<form enctype="multipart/form-data" method="POST">
        <input name="csv" type="file">
        <input type="submit" name="submit" value="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>";
            }
            $handle = fopen($_FILES['csv']['tmp_name'], "r");
            $import=$db->prepare("INSERT INTO adherence(
                    dateandtime,
                    lastname,
                    firstname,
                    paidtime,
                    approvedtime,
                    notadhering)VALUES(
                    ?,?,?,?,?,?)");
            while (($data = fgetcsv($handle, 10000,',')) !== 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->bindParam(6, $data[5], PDO::PARAM_STR);
                $import->execute();
                }
                $i++;

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

            fclose($handle);
        }

CSV file format:

Database structure:

Any help would be appreciated.

3per · Accepted Answer · 2015-11-23T12:25:26.697

1

notadhering)VALUES(

Try add a space

notadhering) VALUES(

I don't know how to write PHP code. After formatting, the sql command should be

INSERT INTO adherence(
  dateandtime,
  lastname,
  firstname,
  paidtime,
  approvedtime,
  notadhering)
  VALUES(
  #2015/04/01 00:00#,
  'lastname',
  'name',
   8,
   0.14972222,
   0.31222222
   );

I've quickly learned PHP (humor, just watched concatenation syntax). I think the PHP code should be

$myDate = "#{$myDate}#";
$lname =  "'{$data[1]}'";
$fname = "'{$data[2]}'";
$import->bindParam(1, $myDate, PDO::PARAM_STR);
$import->bindParam(2, $lname, PDO::PARAM_STR);
$import->bindParam(3, $fname, PDO::PARAM_STR);
$import->bindParam(4, $data[3], PDO::PARAM_STR);
$import->bindParam(5, $data[4], PDO::PARAM_STR);
$import->bindParam(6, $data[5], PDO::PARAM_STR);

edited Nov 23 '15 at 12:25

answered Nov 20 '15 at 13:22

3per

351
9
26

No luck, I removed $removal part, now it is printing only dates, all the same - 1970/01/01 – JustinasT Nov 20 '15 at 13:35
The date should be around # in VALUES part. #2015/04/01# – 3per Nov 20 '15 at 13:51
1

Strings should be around ' – 3per Nov 20 '15 at 13:52
@JustinasT, why do you ask this question again http://stackoverflow.com/questions/33859062/csv-to-database-with-php ? You didn't add # and quotes still – 3per Nov 23 '15 at 00:36
Did not get that, can you please be more specific, what needs to be done? – JustinasT Nov 23 '15 at 01:03
@JustinasT, See the answer, I've correted it. You should find a way to add symbols "#" to datetime value and symbols ' to string values – 3per Nov 23 '15 at 01:28
Fatal error: Cannot pass parameter 2 by reference on this line: $import->bindParam(1, "#{$myDate}#", PDO::PARAM_STR); – JustinasT Nov 23 '15 at 11:35
@JustinasT, Ok, let's study PHP together (: See the PHP code in the answer, I corrected it again. – 3per Nov 23 '15 at 12:27

CSV to Access database

1 Answers1