Passing CSV data to MySQL

Anon

Hi,

I am trying to read a csv file and pass the data to a table in MySQL. THe problem I am encountering is the first field of the csv file is being ignored and the last field is being jooined to the first field of the next line.

My script is:

$file = fopen("c:\cap\stock.csv", "r");

while(!feof($file)) {

$line = fgets($file, 2048);

if ($line) {

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

//each field in a text file will become a field in MySQL table

$stocknum= $fields['1'];
$description= $fields['2'];
$color= $fields['3'];
$regnum= $fields['4'];
$mileage= $fields['5'];
$keepers= $fields['6'];
$regdate= $fields['7'];
$fsh= $fields['8'];
$opp= $fields['9'];
$siv= $fields['10'];
$prep= $fields['11'];
$age= $fields['12'];
$price= $fields['13'];
$vat= $fields['14'];
$notes= $fields['15'];
$notes2= $fields['16'];

}

echo ($stocknum);

$sql = "INSERT INTO stock(stocknum, description, color, regnum, mileage, keepers, regdate, fsh, opp, siv, prep, age, price, vat, notes, notes2) VALUES
('$stocknum','$description','$color','$regnum','$mileage','$keepers','$regdate','$fsh','$opp','$siv','$prep','$age','$price','$vat','$notes','$notes2')";

     $result = mysql_query($sql)
     or die("update of stock table failed");

}

I believe the issue may relate to the fact that the first entry in each line is preceeded by white space and has a white space before the comma. I have included two lines from my csv below:

16412 ,"CLIO 1.4 5DR PRIVILEGE","SILVER","777184", 0 , 0 ,"24/12/2001","yes", 7206.38 , 7206.38 , 0 , 39 , 9995 ,"Y","IMPORT","Retail"
16279 ,"CLIO 1.4 ALIZE+","BLUE","X444uuu", 8351 , 1 ,"01/09/2000","yes", 5042.55 , 5042.55 , 66.57 , 53 , 7195 ,"Y"," ","Retail"

I am a PHP newbie and would be very grateful if someone could show me where I am going wrong.

Thanks
Dean

Anon

$line = fgets($file, 2048);

One line in the CSV file may not always be 2K, so I'd recommend use file() instead.

Diego

Anon

Hi,

I have tried using file with no success ( i am very new to php!! ).

The problem I do not understand is the script appears to read the last field of the first line and join the data with the first field of the next line. The last field in my table shows both pieces af data as 1 entry. (I guess this is because the last field doesn't end with a comma)

I have played around in with the value in fgets($file, 2048); and the only difference it makes is that the numbers of fields in my table reduces the higher the figure.

My question is:

How can I tell the script o recognize the end of the line and move to the next line ensuring the first piece of data encountered is treated as the first field.

I hope this makes sense and I would be extremely grateful if someone could show me where I am going wrong.

Thanks
Dean

Diego Huang wrote:

$line = fgets($file, 2048);

One line in the CSV file may not always be 2K, so I'd recommend use file() instead.

Diego

Anon

How can I tell the script o recognize the end of the line and move to the next line ensuring the first piece of data encountered is treated as the first field.

Just use file:

$lines = file("c:\cap\stock.csv");

foreach ($lines as $line) {

if ($line) {

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

//each field in a text file will become a field in MySQL table

}

Also, in your previous code:
$file = fopen("c:\cap\stock.csv", "r");
That won't work, because you can't use '\' in a double quoted string. If you want to use it, then you have to use a double backslash, like I did in my code above.

Diego