Page 1 of 1

impoting a csv document

Posted: Fri Mar 14, 2008 6:34 am
by samUK
Hi, i posted on here a while ago about how to import a csv file in to a mysql database. I got it working fine, but for some reason it wont work any more? I made a small change somewhere but dont know where???

This is my php file,

Code: Select all

<?php
$host="localhost";
$user="sam";
$password="sam";
$dbname="sam";
$link=mysql_connect ($host, $user, $password);
 
$filename="http://uk.old.finance.yahoo.com/d/quotes.csv?s=%5EBVSP+%5EMXX+%5EMERV+%5EGSPTSE+%5EAEX+%5EATX+%5EBFX+%5EFCHI+%5EGDAXI+%5EFTSE+%5EIETP+%5ESMSI+OMXC20.CO+%5EOMXSPI+%5EPSI20+%5ESPMIB+%5ESSMI+%5EOSEAX+%5EAORD+%5EBSESN+%5EHSI+%5EJKSE+%5EKLSE+%5ENZ50+%5EN225+%5EKS11+000001.SS+%5ESTI+%5ETWII+%5ECCSI+%5ETA100+%5EDJI&f=snl1d1t1c1ohgv&e=.csv";
$handle = fopen("$filename", "r");
 
$sql="TRUNCATE TABLE worldindices";
 
$result=mysql_db_query ($dbname, $sql, $link);
 
 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
 
$import="INSERT into worldindices(isymbol, imove, value, misc, time, date, change, open, fig1, fig2, volume) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]')";
 
$result=mysql_db_query ($dbname, $import, $link);
}
?>
 
And here is my table,

Code: Select all

CREATE TABLE `worldindices` (
  `isymbol` varchar(25) NOT NULL,
  `iname` varchar(25) NOT NULL,
  `value` varchar(25) NOT NULL,
  `misc` varchar(25) NOT NULL,
  `time` varchar(25) NOT NULL,
  `date` varchar(25) NOT NULL,
  `change` varchar(25) NOT NULL,
  `open` varchar(25) NOT NULL,
  `fig1` varchar(25) NOT NULL,
  `fig2` varchar(25) NOT NULL,
  `volume` varchar(25) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 

Any help is much appreciated,

Thanks Sam

Re: impoting a csv document

Posted: Wed Mar 19, 2008 7:56 pm
by www.WeAnswer.IT
I don't know if this is what's wrong, but this line:

Code: Select all

$import="INSERT into worldindices(isymbol, imove, value, misc, time, date, change, open, fig1, fig2, volume) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]')";

Has to be changed to this:

Code: Select all

$import="INSERT into worldindices(isymbol, imove, value, misc, time, date, change, open, fig1, fig2, volume) values('{$data[0]}','{$data[1]}','{$data[2]}','{$data[3]}','{$data[4]}','{$data[5]}','{$data[6]}','{$data[7]}','{$data[8]}','{$data[9]}','{$data[10]}')";

Re: impoting a csv document

Posted: Wed Mar 19, 2008 11:06 pm
by Christopher
For importing CSV data, you might want to look into LOAD DATA INFILE.