[SOLVED] PHP reading file incorrectly?

[_tina_]

I am reading a comma delimited file into php to insert into a MySql database as follows:

 

<?
  $fcontents = file ('file.csv'); 

  for($i=0; $i<sizeof($fcontents); $i++) { 
      $line = trim($fcontents[$i]); 
      $arr = explode(",", $line); 
     
      $sql = "insert into TABLENAME values ('". 
                  implode("','", $arr) ."')"; 
      mysql_query($sql);
      echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";
      } 
}
?>

 

Everything is acting as expected.  The only problem is that it seems to be looping on extra time and giving me a blank set of results at the end of each line and producing the following:

 

insert into TABLENAME values ('"name"','"random data"','"random data"','"random data",'','','','')

 

Do you know what might be the problem?

Thanks in advance

[MadTechie]

A CSV's data it like this

"MadTechie","is great"

and your doing this

$arr = explode(",", $line); 

which Breaks it up into "MadTechie" and "is great" with the quote,

This would remove the quotes

$arr = explode('","', $line); 

 

But you should really use fgetcsv(),

 

however, i don't see the point of using

$arr = explode(",", $line); 

then

implode("','", $arr) ."')"; 

 

when you could just do

$sql = "insert into TABLENAME values ($line)"; 

[_tina_]

Thank you for the reply.

 

I wrote another version using fgetcsv(), but it was doing the same.  Do you know what might be the cause of this?

[_tina_]

I noticed that the csv file contains the following:

 

name, random data, random data, random data, ,,,,

 

How could I remove these when I import it?

 

Thanks you in advance.

 

 

[MadTechie]

I assume your delimiter or enclosure was incorrect

it should be like this

fgetcsv($handle, 1000, ",", '"')

 

could you provide some sample data from the CSV file and show what you wish to keep/loss

ie

"Hello","Blar","Welcome","Yoyo",554,"Lollypop",123

"More","coffee","GoodBye","xbox",45,"Candy",456

only remove the red

[_tina_]

 

could you provide some sample data from the CSV file and show what you wish to keep/loss

ie

"Hello","Blar","Welcome","Yoyo",554,"Lollypop",123

"More","coffee","GoodBye","xbox",45,"Candy",456

only remove the red

 

 

This is what is one line from the CSV:

 

"john", "E-Commerce Integration", "Notes notes",,,,

 

I basically just want to take each field out, remove slashes, commas and just grab the text and insert into a database one row at a time.  The extra commas at the end of each row are my problem.  I am doing something like:

        $lineseparator = "\n";
        $linearray = explode($fieldseparator,$line);
        $linemysql = implode("','",$linearray);

        $query = "insert into $databasetable ($fields) values('$linemysql');";

 

This is giving me the query correctly except it is adding quoted commas to the end as in:

 

insert into TABLENAME values ('"name','data','data',,,,, ')

 

Thanks.

[MadTechie]

<?php
$line = '"john", "E-Commerce Integration", "Notes notes",,,,';

$line = trim($line,',"'); //remove , and "
//it should now be 'john", "E-Commerce Integration", "Notes notes'

$linearray = explode('", "',$line); //Get and array //or below
/* $linearray should be
array(
john
E-Commerce Integration
Notes notes
)
*/

$sqlvalue = "'".implode("','",$linearray)."'"; //you need the first and last '
//$sqlvalue should be 'john','E-Commerce Integration','Notes notes'

// i'll assume $fields are okay!
$query = "insert into $databasetable ($fields) values($linemysql);";

[_tina_]

<?php
$line = '"john", "E-Commerce Integration", "Notes notes",,,,';

$line = trim($line,',"'); //remove , and "
//it should now be 'john", "E-Commerce Integration", "Notes notes'

$linearray = explode('", "',$line); //Get and array //or below
/* $linearray should be
array(
john
E-Commerce Integration
Notes notes
)
*/

$sqlvalue = "'".implode("','",$linearray)."'"; //you need the first and last '
//$sqlvalue should be 'john','E-Commerce Integration','Notes notes'

// i'll assume $fields are okay!
$query = "insert into $databasetable ($fields) values($linemysql);";

 

Thank you very much for the help.  It i now inserting into the db.  Thanks again for the help

[MadTechie]

Your very welcome,

if this is now solved can you please click topic solved (bottom left) i think!