如何使用phpmyadmin将Excel文件导入到mysql数据库中 [英] How to import Excel file into mysql database using 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，使用这个简单的库 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
}

这篇关于如何使用phpmyadmin将Excel文件导入到mysql数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！