impoting a csv document

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
samUK
Forum Newbie
Posts: 18
Joined: Fri Mar 07, 2008 12:31 pm

impoting a csv document

Post 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
www.WeAnswer.IT
Forum Newbie
Posts: 24
Joined: Wed Mar 19, 2008 6:33 pm

Re: impoting a csv document

Post 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]}')";
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: impoting a csv document

Post by Christopher »

For importing CSV data, you might want to look into LOAD DATA INFILE.
(#10850)
Post Reply