CODEWITHSUNDEEP
phpmysqlcsv
PaulS
PaulS

Reputation: 11

Writing a CSV File to SQL

Hope someone can help me with what I think will be something minor (I'm still learning...). I'm trying to write the entire contents of a CSV File server based to an SQL database here is the code I presently have. The line // out writes perfectly and generates a new record. The $ar0 values generate no entries into the table named order - even though the csv file is about 100 lines long I just get

Error: INSERT INTO order (Picker,Order_Number,Timestamp,System)values ('','','','')

$file = "Pal.ORD.csv"; 
$tbl = "order"; 


$f_pointer=fopen("$file","r"); // file pointer

while(! feof($f_pointer)){
$ar=fgetcsv($f_pointer);
//$sql="INSERT INTO `order` (Picker,Order_Number,Timestamp,System)values ('Me','9999','23-01-2015','ORD')";
$sql="INSERT INTO `order` (Picker,Order_Number,Timestamp,System)values ('$ar[0]','$ar[1]','$ar[2]','$ar[3]')";

echo $sql;
echo "<br>";
}


if ($connect->query($sql) === TRUE) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Upvotes: 1

Views: 81

Answers (2)

Funk Forty Niner
Funk Forty Niner

Reputation: 74216

What I think may be going on is that your file probably has an empty line/carriage return as the last line in the file and is using that to insert the data as blank entries.

I can't be 100% sure about this since you have not provided a sample of your CSV file, however that is what my tests revealed.

Based on the following CSV test model: (Sidenote: blank lines will be ignored)

a1,a2,a3,a4
b1,b2,b3,b4
c1,c2,c3,c4

Use the following and replace with your own credentials.

This will create a new entry/row for each line found in a given file based on the model I have provide above.

<?php 
$DB_HOST = 'xxx';
$DB_USER = 'xxx';
$DB_PASS = 'xxx';
$DB_NAME = 'xxx';

$db = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($db->connect_errno > 0) {
  die('Connection failed [' . $db->connect_error . ']');
}

$file = "Pal.ORD.csv";
$delimiter = ',';
if (($handle = fopen("$file", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
        foreach($data as $i => $content) {
            $data[$i] = $db->real_escape_string($content);
        }
        // echo $data[$i].""; // test only not required
        $db->query("INSERT INTO `order` 
                      (Picker, Order_Number, Timestamp, System)
                      VALUES ('" . implode("','", $data) . "');");
    }
    fclose($handle);
}

if($db){
echo "Success";
}

else {
    echo "Error: " . $db->error;
}

Upvotes: 1

Hirthy
Hirthy

Reputation: 23

At a quick glance it seems like this:

$f_pointer=fopen("$file","r"); // file pointer

Should be this:

$f_pointer=fopen($file,"r"); // file pointer

You might not be reading anything from the file. You can try outputting the file contents to see if that part is working, since you've confirmed that you can insert into the DB.

Upvotes: 0

Related Questions