Page 1 of 1
Uploading Excel file into Mysql database
Posted: Wed Nov 10, 2010 4:33 pm
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.
Re: Uploading Excel file into Mysql database
Posted: Wed Nov 10, 2010 5:11 pm
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.