Writing a CSV File to SQL

Question

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;
}

if you adding it to the db with no checking or processing the easy way is to use load date infile: dev.mysql.com/doc/refman/5.1/en/load-data.html — user557846
– user557846, Commented Jan 24, 2015 at 1:07
You need to do a var_dump($ar); because, based on your error, it looks like that is failing to return values — Machavity
– Machavity ♦, Commented Jan 24, 2015 at 1:17
Why not just use the SQL statement LOAD DATA INFILE '/path/to/Pal.ORD.csv' INTO TABLE order;? — SameOldNick
– SameOldNick, Commented Jan 24, 2015 at 1:26

Funk Forty Niner · Accepted Answer · 2015-01-24 04:01:54Z

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;
}

Hirthy · Accepted Answer · 2015-01-24 01:19:51Z

0

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.

answered Jan 24, 2015 at 1:19

Hirthy

233 bronze badges

1 Comment

Funk Forty Niner Over a year ago

That shouldn't matter. If it were in single quotes, I would say OK, but not for this. $f_pointer=fopen("$file","r"); is valid, while $f_pointer=fopen('$file',"r"); will fail.

Collectives™ on Stack Overflow

Writing a CSV File to SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related