帮助使用phpMyAdmin将Excel文件导入MySQL [英] Help Importing an Excel File into MySQL using phpMyAdmin

问题描述

我正在使用phpMyAdmin的内置导入功能将包含数字值（如884.557和731.0547）的Excel文件（.xls）上传到MySQL数据库。但是，我有可怕的舍入/截断问题。由于某些原因，某些值（如884.557和731.0547）更改为99.99999或9.99999。但是，正确导入其他值，如127.0947。谁能帮忙？如果可能，我仍然希望使用内置的phpMyAdmin导入功能，因为它是有用的。

I am uploading Excel files (.xls) containing numeric values such as 884.557 and 731.0547 into a MySQL database using phpMyAdmin's built-in Import function. However, I am having horrible rounding/truncation issues. For some reason, some values like 884.557 and 731.0547 are changed to 99.99999 or 9.99999. However, other values like 127.0947 are imported correctly. Can anyone help? If possible, I would still like to use the built-in phpMyAdmin Import function because it is useful.

推荐答案

如果你熟悉与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屋！