PHP code for importing items from a CSV file to MYSQl

[georgebates]

Hi there,

 

I need some code that the user specifies the file on their computer that has a big list of rows with five columns of data and then get added to a mysql database.

 

Can anyone help me out with this?

 

Thanks

[ChemicalBliss]

I made this a while ago:

 

<?php

Function add_csv_to_db($path,$table){
$query = "INSERT INTO `".$table."` (`col1`,`col2`,`col3`,`col4`,`col5`) VALUES ";

// Open File
$handle = fopen($path,"r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	if(!isset($firstrun)){
		$firstrun = true;
		$query .= "\n";
	}else{
		$query .= "\n,";
	}
	$query .= "(
		'".mysql_real_escape_string($data[0])."',
		'".mysql_real_escape_string($data[1])."',
		'".mysql_real_escape_string($data[2])."',
		'".mysql_real_escape_string($data[3])."',
		'".mysql_real_escape_string($data[4])."'
	)";

}

$query .= ";";

$result = mysql_query($query) or die("Error: <br/> ".mysql_error()."<hr /> ".$query);

return "Table Rows Imported Successfully.";
}

?>

 

-cb-

[trq]

Can anyone help me out with this?

 

Where exactly are you stuck? Were not here to just hand out code.

[ChemicalBliss]

Oh and the form:

 

<form method="post" action="process.php" enctype="multipart/form-data">
<input type="hidden" name="MAX_FILE_SIZE" value="5242880" /><!-- 5mb MAX -->
<table width="500" border="0" cellpadding="0" cellspacing="0">
<tr>
	<td width="200">
		<input type="submit" name="uploadit" Value="Upload A New Update File." />
	</td>
	<td width="300">
		<input name="uploadedfile" type="file" />
	</td>
</tr>
</table>
</form>

 

process.php

<?php

Function cleanup_mysql($filepath,$query=null){
unlink($filepath);
exit("ERROR END<hr />".mysql_error()."<hr />".$query);
}

$table = 'mysql_tablename'; // MySQL Tablename
$csv_directory = './'; // Current directory?

// Connect to mysql here... <<<<<

// Upload File, Move To New CSV Directory whilst adding log entry and checking if file exists already. (if it does check MD5 and say..)

// Check if file upload was clicked but no filename given:
if(isset($_FILES['uploadedfile']['error']) && $_FILES['uploadedfile']['error'] == 4){
exit("ERROR - PLEASE CHOOSE A FILE (Go Back and Try Again)");
}

// Uploaded File Name
$tName = pathinfo($_FILES['uploadedfile']['name']);

// Uploaded File Target
$Target = $tName['basename'];

// Check Filesize
$max_filesize = 5242880; // 5MB
if(filesize($_FILES['uploadedfile']['tmp_name']) > $max_filesize){
exit("File Size Too Large (>5MB). <a href='javascript:histroy.go(-1);'>Go Back?</a>");
}

// Check Extension
if(strtolower($tName['extension']) != "csv"){
exit("File must be of type 'CSV' (Comma Seperated File). <a href='javascript:histroy.go(-1);'>Go Back?</a>");
}

if (!is_uploaded_file($_FILES['uploadedfile']['tmp_name'])) {
exit("File Was Not Uploaded Correctly.");
}

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $csv_directory.$Target) === FALSE){
exit("There was an error copying the file over.");
}else{
// add csv
add_csv_to_db($csv_directory.$Target,$table);
}
?>

 

I took out the mysql part because i had a class file that would complicate this for you. Connecting to mysql is easy though im sure you know how.

I also took out the part that checked if the file has already been uploaded, but thats using a special table that saves all the csv files locations and md5.

 

-cb-

[georgebates]

oh sweet. that looks great thank u.