update with a CSV file

peehaichpee · February 7, 2012

where am I going wrong here?

<?php

$message = null;

$allowed_extensions = array('csv');

$upload_path = 'c:\wamp\tmp';

if (!empty($_FILES['file'])) {

if ($_FILES['file']['error'] == 0) {

	// check extension
	$file = explode(".", $_FILES['file']['name']);
	$extension = array_pop($file);

	if (in_array($extension, $allowed_extensions)) {

		if (move_uploaded_file($_FILES['file']['tmp_name'], $upload_path.'/'.$_FILES['file']['name'])) {

			if (($handle = fopen($upload_path.'/'.$_FILES['file']['name'], "r")) !== false) {

				$keys = array();
				$out = array();

				$insert = array();

				$line = 1;

				while (($row = fgetcsv($handle, 0, ',', '"')) !== FALSE) {
			       	
			       	foreach($row as $key => $value) {
			       		if ($line === 1) {
			       			$keys[$key] = $value;
			       		} else {
			       			$out[$line][$key] = $value;
			       			
			       		}
			       	}
			        
			        $line++;
			      
			    }
			    
			    fclose($handle);    
			    
			    if (!empty($keys) && !empty($out)) {
			    	
			    	$conndb = mysql_connect("localhost", "root", "");

			   				mysql_select_db("master", conndb);	  

			    	foreach($out as $key => $value) {
			    	
						$sql = "UPDATE `master` SET `LOCATION` = '{$LOCATION}', `Location Name` = '{$Location Name}' WHERE `Asset Number` = {$Asset Number}";

							mysql_query($sql);
			    		
			   		}
			   		mysql_close($conndb);
			   		$message = '<span class="green">File has been uploaded successfully</span>';
			   		
			   	}	
			    
			}

		}

	} else {
		$message = '<span class="red">Only .csv file format is allowed</span>';
	}

} else {
	$message = '<span class="red">There was a problem with your file</span>';
}

}

?>
<!DOCTYPE HTML>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>Upload CSV to MySQL</title>
<meta name="description" content="" />
<meta name="keywords" content="" />
<link href="/styles/core.css" rel="stylesheet" type="text/css" />
<!--[if lt IE 9]>
<script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
</head>
<body>

<section id="wrapper">	

<form action="" method="post" enctype="multipart/form-data">

	<table cellpadding="0" cellspacing="0" border="0" class="table">
		<tr>
			<th><label for="file">Select file</label> <?php echo $message; ?></th>
		</tr>
		<tr>
			<td><input type="file" name="file" id="file" size="30" /></td>
		</tr>
		<tr>
			<td><input type="submit" id="btn" class="fl_l" value="Submit" /></td>
		</tr>
	</table>

</form>

</section>

</body>
</html>

Line 55 errors saying:

( ! ) Parse error: syntax error, unexpected T_STRING, expecting '}' in C:\Documents\PHPRunnerProjects\Jobs3\output\importtest.php on line 55

the line in question is:

$sql = "UPDATE `master` SET `LOCATION` = '{$LOCATION}', `Location Name` = '{$Location Name}' WHERE `Asset Number` = {$Asset Number}";

garethhall · February 7, 2012

Try

$sql = "UPDATE `master` SET `LOCATION` = '{$LOCATION}', `Location Name` = '{$Location Name}' WHERE `Asset Number` = '{$Asset Number}'"

or

$sql = "UPDATE `master` SET `LOCATION` = '$LOCATION', `Location Name` = '$Location Name' WHERE `Asset Number` = $Asset Number"

or

$sql = 'UPDATE `master` SET `LOCATION` = ' . $LOCATION} . ', `Location Name` = ' . $Location Name . ' WHERE `Asset Number` = ' . $Asset Number

DavidAM · February 7, 2012

$sql = "UPDATE `master` SET `LOCATION` = '{$LOCATION}', `Location Name` = '{$Location Name}' WHERE `Asset Number` = {$Asset Number}";

Your variable names have spaces in them. -- i.e. {$Location Name} -- This is NOT allowed. Besides, I don't see anywhere that you have defined these variables.

Also, your foreach($out as $key => $value) { is not consistent with the way you loaded the data.

I'm guessing here, but I think that section of code should be something like this:

if (!empty($keys) && !empty($out)) {

$conndb = mysql_connect("localhost", "root", "");

		mysql_select_db("master", conndb);	  

//	foreach($out as $key => $value) {
foreach ($out as $lineNo => $values) {
	$LOCATION = $values['LOCATION'];
	$LocationName = $values['Location Name'];
	$AssetNumber = $values['Asset Number'];

	$sql = "UPDATE `master` SET `LOCATION` = '{$LOCATION}', `Location Name` = '{$LocationName}' WHERE `Asset Number` = {$AssetNumber}";

		mysql_query($sql);

}
mysql_close($conndb);
$message = '<span class="green">File has been uploaded successfully</span>';

}

peehaichpee · February 7, 2012

Hey DavidAM, I'm learning as I go, I tried your suggestion and it's saying Notice: Undefined index: LOCATION in

but for all of the variables, so how would I go about declaring them properly or setting a value to them?

peehaichpee · February 8, 2012

ok I worked that bit out, now it's not updating the SQL field so I added echo $value and I get this:

40102401024010240102401024010240102401024010240102


$conndb = mysql_connect("localhost", "root", "");

mysql_select_db("test", $conndb);


//foreach($out as $key => $value) {



foreach ($out as $lineNo => $values) {



$LOCATION = $value['LOCATION'];
$LocationName = $value['Location Name'];
$AssetNumber = $value['Asset Number'];

$sql = "UPDATE `master` SET `LOCATION` = '{$LOCATION}', `Location Name` = '{$LocationName}' WHERE `Asset Number` = {$AssetNumber}";
echo $value;

mysql_query($sql);

			    		
			   		}
			   		mysql_close($conndb);
			   		$message = '<span class="green">File has been uploaded successfully</span>';
			   		
			   	}	
			    
			}

		}

	} else {
		$message = '<span class="red">Only .csv file format is allowed</span>';
	}

} else {
	$message = '<span class="red">There was a problem with your file</span>';
}

}

what am I missing here?

DavidAM · February 8, 2012

I think the code where you are parsing the CSV needs to change as well. Try this:

while (($row = fgetcsv($handle, 0, ',', '"')) !== FALSE) {

foreach($row as $key => $value) {
	if ($line === 1) {
		$keys[$key] = $value;
	} else {
		$out[$line][$keys[$key]] = $value; // <<< We want the heading as the index

	}
}

$line++;
  
}

unless I am terribly mistaken, the $key value in the foreach will be just an integer index (i.e. 0, 1, 2, 3 ...). This is (I guess) why the first line is captured in the $keys array. So, you need to use those collected key names in loading the line.

That is why you were getting the undefined indexes (I think). So you will need to change back to $values in the code I gave you before:

$LOCATION = $values['LOCATION'];
$LocationName = $values['Location Name'];
$AssetNumber = $values['Asset Number'];

peehaichpee · February 9, 2012

OK I'm going about it a slightly different way, and it's inefficient in that each CSV has to be exactly the same since I can only seem to grab the array location by number instead of by column name, but on top of that the SQL just isn't working:

$allowed_extensions = array('csv');

$upload_path = 'c:\wamp\tmp';

if (!empty($_FILES['file'])) {

if ($_FILES['file']['error'] == 0) {

	// check extension
	$file = explode(".", $_FILES['file']['name']);
	$extension = array_pop($file);

	if (in_array($extension, $allowed_extensions)) {



		if (move_uploaded_file($_FILES['file']['tmp_name'], $upload_path.'/'.$_FILES['file']['name'])) {

		echo "move success<br />";
		$filename = $upload_path.'\\'.$_FILES['file']['name'];

			if (($handle = fopen($filename, "r")) !== false) {
				   # Set the parent multidimensional array key to 0.
        $nn = 0;
	#as long as there is data in the file 
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            # Count the total keys in the row.
            $c = count($data);

            # Populate the multidimensional array.
            for ($x=0;$x<$c;$x++)
            {
                $csvarray[$nn][$x] = $data[$x];
			//$csvarray[$nn]['AssetNumber'] = $data[];
            }
		print_r($data[8]);
            $nn++;
        }
        # Close the File.
       // fclose($handle);
    }
    # Print the contents of the multidimensional array.
columnize the array

//import the csv file into mysql 
$conndb = mysql_connect("localhost", "root", "");

mysql_select_db("test", $conndb);





$LOCATION = $data[8];
$LocationName = $data[9];
$AssetNumber = $data['0'];

$sql = "UPDATE `master` SET LOCATION = $LOCATION, `Location Name` = '$LocationName' WHERE `Asset Number` = '$AssetNumber'";



echo "<br />".$sql;

mysql_query($sql);

any ideas?

edit: block comment code removed

peehaichpee · February 9, 2012

nailed it. $data wasn't being passed through to the sql statement, so I moved the curly bracket from the while statement down under it and it works.

Still haven't worked out why I have to access the array with the number instead of using field names though but I'm stoked it's working.

Sign In

update with a CSV file

Recommended Posts

peehaichpee

Link to comment

Share on other sites

garethhall

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

peehaichpee

Link to comment

Share on other sites

peehaichpee

Link to comment

Share on other sites

DavidAM

Link to comment

Share on other sites

peehaichpee

Link to comment

Share on other sites

peehaichpee

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information