Create array from csv file on the fly

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
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Create array from csv file on the fly

Post by Jim_Bo »

Hi,

I am using fgetcsv to grab the contents of a csv file .. Its working fine I think, how do you get it to display as an array to make sure its building it correctly?

Im not very familiar with arrays yet, here is the code:

Code: Select all

<?php$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
   $num = count($data);
   $row++;
   foreach ($data as $lines) {
        print_r($lines);
   }
}
fclose($handle);?>

The project it to take a csv file and check against product codes, if they exist update them, if non existant insert the record .. etc


Thanks
Last edited by Jim_Bo on Sun Sep 04, 2005 12:10 am, edited 3 times in total.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Scrap that I think I have it by removing foreach();

Code: Select all

<?php

$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
   $num = count($data);
   $row++;
   
   	print_r ($data);
 }

fclose($handle);

?>
Is that correct .. am i on the right track to continue and start making querys on the arrays?


Thanks
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Yes that will return an array per line of the .CSV file. In this array will be each comma delimited field from your csv filed, stored with a numerically incremented key.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

For your purposes you may want to do something like the following

Say your csv line is

product, product key, price, date (or something similar)

Code: Select all

$handle = fopen("test.csv", "r"); 

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
  $arrayofdata[] = $data[1]; // one would be the numerically indexed product key from the .csv file
}

print_r($arrayofdata);  // I'm assuming this would print an array of product keys here

// query for all product keys .. then
while($productkeyarray = mysql_fetch_assoc($result))
{
  if(!in_array($productkeyarray['productkey'], $arrayofdata))
  {
    // insert into database
  } ELSE
  {
    // update product key
  }
}
That's just off the top of my head. Something like that should work good, but I'm not guaranteeing that exact code would work :P
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Ive been trying to sort this out by myself without to much success, I tried playing with the code posted by scrotaye above and havnt managed to get it to work ..

Also this code below:

Code: Select all

<?php 

require 'db1.php'; 

$row = 1; 
$handle = fopen("test.csv", "r"); 
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { 
   $num = count($data); 
   $row++; 

//	print_r ($data);
//	echo $data[2]. "<br>"; 

	$sql = mysql_query("SELECT code FROM items"); 
    	while ($row = mysql_fetch_array($sql)) { 
     
    	$code = $row['code']; 
 
	if ($code == $data[2]) {
	
	echo "update";
	  
    $sql1 = mysql_query("UPDATE items SET filter='$data[0]', item='$data[1]', code='$data[2]', description='$data[3]', price1='$data[4]', price2='$data[5]' WHERE code='$data[2]'"); 
    if(!sql1) { echo 'update failed'; } 

}else{ 

	echo "insert";
	
    $sql2 = mysql_query("INSERT INTO items (filter, item, code, description, price1, price2) VALUES ('$pro[0]', '$pro[1]', '$pro[2]', '$pro[3]', '$pro[4]', '$pro[5]'"); 
    if(!sql2) { echo 'insert failed'; } 

  }  
 } 
} 

fclose($handle); 

?>
The above code looks to create the correct arrays and also displays a line of code for each row, but doesnt write anything to the database, I would have thought atleast the least it would have echoed insert to the screen in the else statement. seems as tho the if and else staement arnt working at all ..

does using fgetcsv avoind having to use explode() or implode() functions?

A push in the right direction would be great ..

Thanks
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

replace your if($code == $row['code']) line with if(in_array($code, $row)

mysql_fetch_array will return exactly that.. an array.. like array('code1','code2','code3','code4','code5')

So $code will never be equal to an entire array. But you can check to see if $code is in the array. If it is, then update, if it isn't, than insert.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

I dont think thats the answer, it gives me the following error that I cant seem to fix:

Parse error: parse error, unexpected '{' in /home/fusion/public_html/delete.php on line 19


Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you're missing a closing paren for the if's expression.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

I dont think its that .. as there are 3 tags open and 3 have been closed:

Code: Select all

<?php 

require 'db1.php'; 

$row = 1; 
$handle = fopen("test.csv", "r"); 
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { 
   $num = count($data); 
   $row++; 

//	print_r ($data);
//	echo $data[2]. "<br>"; 

	$sql = mysql_query("SELECT code FROM items"); 
    	while ($row = mysql_fetch_array($sql)) { 
     
    	$code = $row['code']; 
 
	if(in_array($code, $row) {
	
	echo "update";
	  
    //$sql1 = mysql_query("UPDATE items SET filter='$data[0]', item='$data[1]', code='$data[2]', description='$data[3]', price1='$data[4]', price2='$data[5]' WHERE code='$data[2]'"); 
    //if(!sql1) { echo 'update failed'; } 

}else{ 

	echo "insert";
	
    //$sql2 = mysql_query("INSERT INTO items (filter, item, code, description, price1, price2) VALUES ('$pro[0]', '$pro[1]', '$pro[2]', '$pro[3]', '$pro[4]', '$pro[5]'"); 
    //if(!sql2) { echo 'insert failed'; } 

  }  
 } 
} 

fclose($handle); 

?>
before I replaced if ($code == $data[2]) the page would load for ages, then I get the time out error .. but had the same opening and closing tags in place.


Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

if(in_array($code, $row) {
how many parens do you count? I count three: open, open, close, ......?
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Doh, I was looking towards the {} :oops:

If I start with and empty table, the page loads blank and no data is inserted into the db at all .. Is this code even correct to do the job?

read a csv file and update any rows found that match the code column in the csv file, and any codes codes not found as a match in the csv file are to be added?

Code: Select all

<?php 

require 'db1.php'; 

$row = 1; 
$handle = fopen("test.csv", "r"); 
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { 
   $num = count($data); 
   $row++; 

//	print_r ($data);
//	echo $data[2]. "<br>";

	$sql = mysql_query("SELECT code FROM items"); 
    	while ($row = mysql_fetch_array($sql)) { 
     
    	$code = $row['code']; 
 
	if(in_array($code, $row)) {
	
    $sql1 = mysql_query("UPDATE items SET filter='$data[0]', item='$data[1]', code='$data[2]', description='$data[3]', price1='$data[4]', price2='$data[5]' WHERE code='$data[2]'"); 
    if(!sql1) { echo 'update failed'; } 

}else{ 

    $sql2 = mysql_query("INSERT INTO items (filter, item, code, description, price1, price2) VALUES ('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]'"); 
    if(!sql2) { echo 'insert failed'; } 

  }  
 } 
} 

fclose($handle); 

?>
Is that anywhere near right for the job

thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the code has potential to eat the server's RAM. Store off the results of the search against the database. That will prevent you from the same query (and nearly the same result set each time) over and over. The storing should be done in a seperate loop from the fgetcsv.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Code: Select all

while ($row = mysql_fetch_array($sql)) { 
      
        $code = $row['code']; 

    if(in_array($code, $row)) {
using that code, $code will ALWAYS be in $row

try something like this:

Code: Select all

while($row = mysql_fetch_array($sql))
{
   $arrayofcodes[] = $row['code'];
}

if(in_array($data[2], $row))  // $data[2] should be replaced with whatever place your 'code' is in.. in the $data array
// it could be $data[0] or $data[1] etc..
{
  //
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply