Uploading Excel file into Mysql database

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
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Uploading Excel file into Mysql database

Post by jankidudel »

HI,
I need to implement something very convenient that person who can't program completely and either downloading files directly through php myadmin . I know that there's an option to save excel file as CSV(comma delimited) and download, but if he doesn't want to do this kind of job ?


Any suggestions ? I appreciate.
User avatar
saltwine
Forum Newbie
Posts: 16
Joined: Tue Nov 09, 2010 7:05 am
Location: London

Re: Uploading Excel file into Mysql database

Post by saltwine »

Hi jankidudel


I think you're asking how to export MySQL data to CSV. You can do this by writing your own PHP script, but you shouldn't be editing phpMyAdmin code... you don't really want to let a user into pypMyAdmin anyway if they don't know what they're doing.

I found this function that might be of use:

Code: Select all

function exportMysqlToCsv($table,$filename = 'export.csv') {
	$csv_terminated = "\n";
	$csv_separator = ",";
	$csv_enclosed = '"';
	$csv_escaped = "\\";
	$sql_query = "select * from $table";
 
	// Gets the data from the database
	$result = mysql_query($sql_query);
	$fields_cnt = mysql_num_fields($result);
 
	$schema_insert = '';
 
	for ($i = 0; $i < $fields_cnt; $i++) {
		$l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
			stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
		$schema_insert .= $l;
		$schema_insert .= $csv_separator;
	} // end for
 
	$out = trim(substr($schema_insert, 0, -1));
	$out .= $csv_terminated;
 
	// Format the data
	while ($row = mysql_fetch_array($result)) {
		$schema_insert = '';
		for ($j = 0; $j < $fields_cnt; $j++) {
			if ($row[$j] == '0' || $row[$j] != '') {
 				if ($csv_enclosed == '') {
					$schema_insert .= $row[$j];
				} else {
					$schema_insert .= $csv_enclosed .
					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
				}
			} else {
				$schema_insert .= '';
			}
 
			if ($j < $fields_cnt - 1) {
				$schema_insert .= $csv_separator;
			}
		} // end for
 
		$out .= $schema_insert;
		$out .= $csv_terminated;
	} // end while
 
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	header("Content-Length: " . strlen($out));
	// Output to browser with appropriate mime type, you choose ;)
	header("Content-type: text/x-csv");
	header("Content-Disposition: attachment; filename=$filename");
	echo $out;
}
 
?>
Just create page that connects to your database and call that function passing in the table you want to export.
Post Reply