如何使用phpmyadmin将Excel文件导入mysql数据库

2022-01-05 00:00:00 excel spreadsheet mysql phpmyadmin

我想通过 phpmyadmin 导入选项从 excel 表格格式 (.xls) 导入到 mysql 数据库.我知道我们需要先将格式转换为csv格式，然后才能导入phpmyadmin.但不幸的是，如果我更改为 csv，某些特殊字符或符号将变成问号 (?) 或其他不同的字符/符号.请就此给我建议，因为我对 phpmyadmin 真的很陌生.

I want to import from excel sheet format (.xls) to mysql database through phpmyadmin importing option. I understand that we need to convert the format to csv format first before we can import to the phpmyadmin. But unfortunately if I change to csv some special character or symbol will become question mark (?) or other different character/symbol. Please advise me on this as I am really new to phpmyadmin.

谢谢

推荐答案

我在这里回答了类似的问题 https://stackoverflow.com/a/16330428/1570901

I have answered similary question here https://stackoverflow.com/a/16330428/1570901

如果你熟悉 html 和 php，通过使用这个 simply library simplex excel 库和脚本，您可以创建自己的 excel 导入到 mysql.创建它可能需要几分钟时间，但一旦创建，您就可以终生使用它.

If you are familiar with html and php, by using this simply library simplex excel library and script you can create your own excel import to mysql. IT may take few minutes to create but once your create you can use it for life time.

//创建 HTML 表单以上传 Excel 表格

// CREATE A HTML FORM TO UPLOAD EXCEL SHEET

//然后创建一个像下面这样的 PHP 脚本

// THEN CREATE A PHP SCRIPT LIKE BELOW

require 'simplexlsx.class.php';

if (isset($_FILES['Filedata'])) {

$file = $_FILES['Filedata']['tmp_name']; // UPLOADED EXCEL FILE

$xlsx = new SimpleXLSX($file);

list($cols, $rows) = $xlsx->dimension();

foreach( $xlsx->rows() as $k => $r) { // LOOP THROUGH EXCEL WORKSHEET

$q = "INSERT INTO TABLENAME(COL1, COL2) VALUE(";
  $q .=  "'".mysql_escape_string($r[0])."', "; // EXCEL DATA
  $q .=  "'".mysql_escape_string($r[1])."', "; // EXCEL DATA
  $q .= ")";

  $sql = mysql_query($q);

    } // IF ENDS HERE
    } // FOR EACH LOOP
}

相关文章