Page 1 of 2

import csv

Posted: Fri Mar 07, 2008 12:45 pm
by samUK
Hi, i have a csv document and am trying to import in in to a mysql database but seem to be having problems.

i have created a table called stocks, and have created a table called stocks with the following fields,

name, currentprice, time, date, percentage, previousprice1, previousprice2, previousprice3, other

This is my connect file,

Code: Select all

<?php
$dbhost = "localhost";
$dbuser = "root";
$dbpassword = "";
$dbdatabase = "stocks";
 
$config_project = "Data Visualisation";
$config_author = "Sam Gibson";
$config_basedir = "http://127.0.0.1/FinalMajorProject/";
?>
This is my import file,

Code: Select all

<?php
session_start();
require("connect.php");
$db = mysql_connect($dbhost, $dbuser, $dbpassword);
mysql_select_db($dbdatabase, $db);
 
 
if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
     $handle = fopen("$filename", "r");
     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     {
    
       $import="INSERT into stocks(name, currentprice, time, date, percentage, previousprice1, previousprice2, previousprice3, other ) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]')";
       mysql_query($import) or die(mysql_error());
     }
     fclose($handle);
     print "Import done";
 
   }
   else
   {
 
      print "<form action='import.php' method='post'>";
      print "Type file name to import:<br>";
      print "<input type='text' name='filename' size='20'><br>";
      print "<input type='submit' name='submit' value='submit'></form>";
   }
?>
This is how my csv file looks,

AAL.L,3300.00, 4:35PM,03/07/2008,-141.00,3393.00,3417.00,3293.00,8884595
ABF.L,819.00, 4:35PM,03/07/2008,-5.50,821.00,826.00,810.00,3018890
ADM.L,859.50, 4:35PM,03/07/2008,-12.00,868.00,873.50,852.00,1772574


when i upload my csv doc i get error messges saying undefined index line15.

Can someone shed some light? Many Thanks, Sam

Re: import csv

Posted: Fri Mar 07, 2008 1:06 pm
by Christopher
What does the data on line 15 look like? It probably has fewer columns.

Re: import csv

Posted: Fri Mar 07, 2008 1:21 pm
by samUK
in my import file i have the following code on line 15,

$import="INSERT into stocks(name, currentprice, time, date, percentage, previousprice1, previousprice2, previousprice3, other )

And this is the error message that comes up,

Notice: Undefined index: 1 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Notice: Undefined index: 2 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Notice: Undefined index: 3 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Notice: Undefined index: 4 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Notice: Undefined index: 5 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Notice: Undefined index: 6 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Notice: Undefined index: 7 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Notice: Undefined index: 8 in C:\Program Files\EasyPHP 2.0b1\www\FinalMajorProject\import.php on line 15

Re: import csv

Posted: Fri Mar 07, 2008 2:20 pm
by Christopher
No, I mean the data from the file. I am guessing that $data[8] is not defined in some rows because there are empty columns.

Re: import csv

Posted: Fri Mar 07, 2008 4:50 pm
by samUK
Is there anyway of getting round this?

the csv file im trying to import contains the ftse 100, heres the link,

http://uk.old.finance.yahoo.com/d/quote ... hgv&e=.csv

iv looked at it and it seems that all rows have 9 columns???

Re: import csv

Posted: Fri Mar 07, 2008 5:37 pm
by Christopher
You can just ignore the Notice, you can do isset() checks for each value, or you can find out which lines are causing errors and sort out the problem with fgetcsv().

Re: import csv

Posted: Fri Mar 07, 2008 5:42 pm
by bertfour
I tried it, my way...

And it works without errors....

Code: Select all

 
<?php
$host="localhost";
$user="root";
$password="root";
$dbname="csvtest";
$link=mysql_connect ($host, $user, $password);
 
$filename="http://uk.old.finance.yahoo.com/d/quotes.csv?s=@%5EFTSE&f=sl1d1t1c1ohgv&e=.csv";
$handle = fopen("$filename", "r");
// print "<pre>";
 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
 
$import="INSERT into stocks(name, currentprice, time, date, percentage, previousprice1, previousprice2, previousprice3, other ) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]')";
 
$result=mysql_db_query ($dbname, $import, $link);
 
// print_r($data);
}
// print "</pre>";
?>

Db:

Code: Select all

CREATE TABLE stocks (
  name varchar(255) NOT NULL DEFAULT '',
  currentprice varchar(255) NOT NULL DEFAULT '',
  `time` varchar(255) NOT NULL DEFAULT '',
  `date` varchar(255) NOT NULL DEFAULT '',
  percentage varchar(255) NOT NULL DEFAULT '',
  previousprice1 varchar(255) NOT NULL DEFAULT '',
  previousprice2 varchar(255) NOT NULL DEFAULT '',
  previousprice3 varchar(255) NOT NULL DEFAULT '',
  other varchar(255) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
No errors 8O

Re: import csv

Posted: Sat Mar 08, 2008 6:43 am
by samUK
im a bit new to this so forgive me for being slow.

i have copied your code, created a table called csvtest and copied the table structure.

when i run the php file to import, nothing happens? no data goes in the database and no errors show up?

i have it running in the right directory, (eashphp/www/FinalMajorProject)

what have i done wrong?

Re: import csv

Posted: Sat Mar 08, 2008 6:51 am
by bertfour
Are you behind a proxy?

This is without the DB:

Code: Select all

<?php
$filename="http://uk.old.finance.yahoo.com/d/quotes.csv?s=@%5EFTSE&f=sl1d1t1c1ohgv&e=.csv";
$handle = fopen("$filename", "r");
print "<pre>";
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
 print_r($data);
}
print "</pre>";
?>

Re: import csv

Posted: Sat Mar 08, 2008 6:53 am
by samUK
helo, i got it working!!! Yay!!!

Thank you so much for your help!!!

At the moment, when i run the file it pulls in the csv and builds up more rows. Is there anyway i can just import the rows of data and overwrite the data already there??

Re: import csv

Posted: Sat Mar 08, 2008 7:01 am
by bertfour
Before the "while" :

Code: Select all

 
 
$sql="TRUNCATE TABLE stocks";
 
$result=mysql_db_query ($dbname, $sql, $link);

Re: import csv

Posted: Sat Mar 08, 2008 7:08 am
by samUK
Once again, many many thanks.

and one more cheeky question,

i would like this script to automatically run every 20mins. I am going to keep this running locally on my machine so im not gonna upload it to anyother server. Is this possible?

If so what is this automation called?

All help and advice is much appreciated,

Sam

Re: import csv

Posted: Sat Mar 08, 2008 7:21 am
by bertfour
I have something similar running on my website...

if you can't run cron jobs:

I just take a page that is regularly visited and make a piece of code there that checks if an hour has past, and then loads the external stuff...

Sample (currencies):

Code: Select all

$query = "SELECT * FROM currencies WHERE ID = 1";
$result=mysql_db_query ($dbname, $query, $dblink);
$currRow = mysql_fetch_array ($result) ;
$hrnow = date('H');
$daynow = date('D');
$lastupper=$currRow['hr'];
 
if (($hrnow <> $lastupper) AND ($daynow <> "Sat" AND $daynow <> "Sun")) {
 
 $dblink=mysql_connect ($dbhost, $dbuser, $dbpasswd);
 $hquery = "UPDATE currencies SET hr=$hrnow WHERE ID = 1" ;
 $hresult=mysql_db_query ($dbname, $hquery, $dblink);
 
 
// do the import stuff here
 
}
 
Thats how I get the currencies on my phpBB3 forum: http://balipod.com/

Re: import csv

Posted: Sat Mar 08, 2008 7:54 am
by samUK
thank you for your help, could you possiblly talk me through what your script is doing?

thanks

Re: import csv

Posted: Sat Mar 08, 2008 7:59 am
by bertfour
Well, I have a currencies table, where I store the "last hour" the import was done.

Then, I check every time the script runs, if the hour has changed.

If so, I update the "last hour", and do the import....

In your case, you have to create an extra table, where you store the "last hour"....

Try to program it, and come back with some code, we take it from there...