import csv

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

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

import csv

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

Re: import csv

Post by Christopher »

What does the data on line 15 look like? It probably has fewer columns.
(#10850)
samUK
Forum Newbie
Posts: 18
Joined: Fri Mar 07, 2008 12:31 pm

Re: import csv

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

Re: import csv

Post 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.
(#10850)
samUK
Forum Newbie
Posts: 18
Joined: Fri Mar 07, 2008 12:31 pm

Re: import csv

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

Re: import csv

Post 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().
(#10850)
bertfour
Forum Commoner
Posts: 45
Joined: Fri Mar 07, 2008 7:33 am

Re: import csv

Post 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
samUK
Forum Newbie
Posts: 18
Joined: Fri Mar 07, 2008 12:31 pm

Re: import csv

Post 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?
bertfour
Forum Commoner
Posts: 45
Joined: Fri Mar 07, 2008 7:33 am

Re: import csv

Post 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>";
?>
samUK
Forum Newbie
Posts: 18
Joined: Fri Mar 07, 2008 12:31 pm

Re: import csv

Post 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??
bertfour
Forum Commoner
Posts: 45
Joined: Fri Mar 07, 2008 7:33 am

Re: import csv

Post by bertfour »

Before the "while" :

Code: Select all

 
 
$sql="TRUNCATE TABLE stocks";
 
$result=mysql_db_query ($dbname, $sql, $link);
samUK
Forum Newbie
Posts: 18
Joined: Fri Mar 07, 2008 12:31 pm

Re: import csv

Post 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
bertfour
Forum Commoner
Posts: 45
Joined: Fri Mar 07, 2008 7:33 am

Re: import csv

Post 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/
samUK
Forum Newbie
Posts: 18
Joined: Fri Mar 07, 2008 12:31 pm

Re: import csv

Post by samUK »

thank you for your help, could you possiblly talk me through what your script is doing?

thanks
bertfour
Forum Commoner
Posts: 45
Joined: Fri Mar 07, 2008 7:33 am

Re: import csv

Post 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...
Post Reply