Read and pull from CSV

[Kudose]

I am new to CSVs, just to make that known before hand.

I read a little about them, but still have some questions.

Does reading them with fread work the same with CSV's as it would a txt file?

How could I, very simply, scan a CSV for a input value and return it (please give short code example)?

I am not looking for a complex DB insert by the returned value or anything like that, but it will be the end result. 🙂

Thanks.

🙂

<edit>
A CSV file is being used for an inventory system as opposed to a SQL DB and I am going to migrate them, is why I am asking. Just to give you an idea of what my end code will be.
</edit>

[Kudose]

Ok, I got this far...

If I enter a number not found in the CSV, it displays

lostlostlost

Lost.

If I enter a number found in the CSV, anywhere not the row it is supposed to (6 aka Jul), it doesnt say lost or found.

Here is the csv layout

Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
"2,400","6,500","17,950","40,000","72,650","115,900","169,750","234,200","309,250","394,900","491,150","598,000"
850,850,"11,450","22,050","32,650","43,250","53,850","64,450","75,050","85,650","96,250","106,850"
"3,250","10,600","10,600","10,600","10,600","10,600","10,600","10,600","10,600","10,600","10,600","10,600"

Here is the PHP

$file = "test.csv";
//pull for jul
$column = 6;
$handle = fopen($file, 'r'); 
while (($row = fgetcsv($handle, 1000, ",")) !== false) 
{ 
	   if ($row[$column] == $product_id) 
    	 { 
		 echo "found";
		$msg = "Found.";
	     } else{
		 echo "lost";
	 	$msg = "Lost.";
		}
	}
}

Thanks!

[Weedpacket]

Testing this as:

$file = "test.csv";
$product_id = '53,850';
//pull for jul
$column = 6;
$handle = fopen($file, 'r'); 
while (($row = fgetcsv($handle, 1000, ",")) !== false) 
{ 
       if ($row[$column] == $product_id) 
         { 
         echo "found";
        $msg = "Found.";
         } else{
         echo "lost";
         $msg = "Lost.";
        }
    }

I get:

lostlostfoundlost

Which is what I expected. Presumably at the end of the loop, $msg equals "Lost.", because that's the last thing it's set to. Do you perhaps want to put a break in there so you don't lose what you've found?

[Kudose]

Thanks for your reply Weedpacket.

I am not too worried about the repeatitivness. 🙂

It stalls, or just doesnt pring $msg, when I enter a number it is supposed to find.

I cant see to figure out how to:

1. Make it find what I enter.

2. If you can, narrow the csv search by column heading.

I am sure these things are possible, as is most anything with PHP, but I was wondering if there were built in features or functions I am missing.

🙂

[superwormy]

Probably your best bet is to read the entire file into a multi-dimensional array, something like this:

array(
0 => array(
'column 1' => 'value for col 1, row 1',
'column 2' => 'valuef or col 2, row 1',
...
'column 3' => 'value for col 3, row 1'),
1 => array(
'column 1' => 'value for col 1, row 2',
'column 2' => 'valuef or col 2, row 2',
...
'column 3' => 'value for col 3, row2 '),
2 => array(
'column 1' => 'value for col 1, row 3',
'column 2' => 'valuef or col 2, row 3',
...
'column 3' => 'value for col 3, row 3')

So then you can just foreach() through the array, limit searches by column pretty easily, etc.

fgetcsv() is a very nice function for this...

• Keith

[Kudose]

How fast would your method be reading from over 200,000 rows of about 12 columns?

[superwormy]

If you have 200,000 rows you should be looking into databases and not freakin' CSV files...

But... there's only one way to find out :-)

Hvae fun :-)

[Kudose]

Here's the deal.

There a a third party company whic hsupplies inventory levels based on a CSV. I am wanting to take that CSV, scan it for a user inputed variable, and insert it into the database.

Can you point me to a tutorial on that?

Becuase what you had explained before makes sense, I just need more info on how to do it, and do it by search one column, but pulling all info after the find.

Thanks again.

🙂

[Weedpacket]

The code you gave worked for me. Unless I was totally off-base (I guessed at a $product_id) then your problem may well lie elsewhere.

Originally posted by Kudose
How fast would your method be reading from over 200,000 rows of about 12 columns?

You sure it's not just timing out during the read? I'm sure you've verified that none of the lines in the CSV file are more than a thousand characters long. Have you tried print_r() on each line returned by fgetcsv() to see what it contains e.g., immediately before it stalls or misses an item that should have been in there? (That would be a lot of stuff to dig through if you're trying this with the full quarter-million records every time; no doubt you're testing on a smaller sample set.)

What might be faster (assuming you've got the basic reading working) is to just replicate the data in the CSV file in the database. Create a temporary table with the same structure, read rows in from the CSV and shovel them into the table. Once it's all in the table, you can do all the appropriate search and selection and insertion into real tables in SQL. And afterwards, drop the temporary table.

[Kudose]

Ok, I decided to go the DB route.

Now I have a permissions issue with the database.

On every other page and routine I have good permissions, but when executing the following script, and not killing it, I get an access denied error using password YES.

I check and I have set admin rights to that user, and have even used the master DB account and get the same error.

Any ideas on that one?

Thanks for eveyones help and support thus far.

if($_POST['submit']){

$stop_exe = FALSE;
$allowed = array("text/csv", "text/comma-seperated-values", "application/csv", "application/excel", "application/vnd.msexcel", "application/vnd.ms-excel", "application/octet-stream");

$file_name = $_FILES['file']['name'];
if($_FILES['file']['size'] > 2000000){
	$msg = "The maximum file size allowed is 2MB.  Please try a differnet file.";
	$stop_exe = TRUE;
}
//end size check
if(!in_array($_FILES['file']['type'], $allowed)){
	$msg = "The file you are trying to upload is not a valid type for this fuction.<br \><br \>";
	echo "Valid extensions are: ";
	foreach($allowed as $key => $var){
		echo $var." ";
	}
	echo "<br /><br />Your file type was: ".$_FILES['file']['type'];
	$stop_exe = TRUE;
}

if($stop_exe == FALSE){

	$sql_csv = mysql_query("LOAD DATA INFILE '".$file_name."' REPLACE INTO TABLE wholesale FIELDS TERMINATED BY ';' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES") or die(mysql_error());

	if($sql_csv !== FALSE){
		$msg = "You're CSV file was upload into the database.";
	} else {
		$msg = "The was an error uploading your CSV file into the database.";
	}
}		
}

[Kudose]

You sure it's not just timing out during the read?

I used a 12 row CSV in the test, and it just gave no results. Although I did not try printing everyline fromthe read, that I should try.