0

I am trying to import a csv file into the data base without defining any of the rows as it will be automatic when the page loads - 

$file = '../csv/file.csv';
$table = 'table_name';

// get structure from csv and insert db
ini_set('auto_detect_line_endings',TRUE);
$handle = fopen($file,'r');
// first row, structure
if ( ($data = fgetcsv($handle) ) === FALSE ) {
    echo "Cannot read from csv $file";die();
}
$fields = array();
$field_count = 0;
for($i=0;$i<count($data); $i++) {
    $f = strtolower(trim($data[$i]));
    if ($f) {
        // normalize the field name, strip to 20 chars if too long
        $f = substr(preg_replace ('/[^0-9a-z]/', '_', $f), 0, 255);
        $field_count++;
        $fields[] = $f.' VARCHAR(255)';
    }
}

$sql = "CREATE TABLE $table (" . implode(', ', $fields) . ')';

$conn->query($sql);
while ( ($data = fgetcsv($handle) ) !== FALSE ) {
    $fields = array();
    for($i=0;$i<$field_count; $i++) {
        $fields[] = '\''.addslashes($data[$i]).'\'';
    }
   $sql = "Insert into $table values(" . implode(', ', $fields) . ')';

   $conn->query($sql);

}
fclose($handle);
ini_set('auto_detect_line_endings',FALSE);

It loads the data in the page if I echo it and it creates the table correctly just doesn't load the data into the table and I cant find out why..

Thanks!

UPDATED

Here is the first row I am getting when I echo $sql - 

Insert into 1001_inventory values('New', '581613', '88888888888888888', '2016', 'Toyota')
Ryan D
  • 741
  • 1
  • 11
  • 29
  • You're probably running into quoting issues. Save yourself the headache, and use prepared statements and bind your variables ([bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php) for mysqli, [bindParam](http://php.net/manual/en/pdostatement.bindparam.php) for PDO). Check for errors on each insert so you can see which rows failed and why. – aynber Nov 22 '16 at 18:46
  • 1
    can you echo the last $sql and give us 1 line that you are getting from this – Buddhi741 Nov 22 '16 at 18:48
  • Might be easier http://dev.mysql.com/doc/refman/5.7/en/load-data.html – AbraCadaver Nov 22 '16 at 18:51
  • Dont see any issues with the generated SQL statement. Perhaps try to use a data loader tool like SequelPro – Yash Dayal Nov 22 '16 at 19:01
  • Would having blank data fields in the csv file cause this issue? – Ryan D Nov 22 '16 at 19:02
  • It seems to work for me even with blank values.. Data File: `field1,field2 data11,data12 data21, data31,data32` Generated SQL: `CREATE TABLE table_name (field1 VARCHAR(255), field2 VARCHAR(255)) Insert into table_name values('data11', 'data12') Insert into table_name values('data21', '') Insert into table_name values('data31', 'data32')` – Yash Dayal Nov 22 '16 at 19:11
  • The only other thing I can think of is that there is a characters encoding issue/ mismatch in the file. – Yash Dayal Nov 22 '16 at 19:16
  • Ya if I just import it straight through phpMyAdmin it imports fine.. Weird – Ryan D Nov 22 '16 at 19:24
  • Good to hear that you are unblocked. If you still want to deep dive...you can see the number of rows inserted from your PHP code? The issue will be in the subsequent line in your data file – Yash Dayal Nov 22 '16 at 19:31
  • Have you checked for errors after each insert line? Have you tried prepared statements instead of attempting to quote the variables yourself? – aynber Nov 22 '16 at 19:32
  • **WARNING**: `addslashes`is **NOT** a suitable escaping method. Use prepared statements with placeholder values, or better still, [`LOAD DATA INFILE`](http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table) on the CSV directly. – tadman Nov 22 '16 at 19:36
  • Yes just found my issue kind of, the fields were to small for the data so I got it to import – Ryan D Nov 22 '16 at 19:36

1 Answers1

1

So I found that the varchar(255) was to small for some of the fields so I expanded it and the problem is solved, Thanks for all the help!

Ryan D
  • 741
  • 1
  • 11
  • 29