Help Importing an Excel File into MySQL using phpM

Question

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.

Answer 1

This is what i normally do:

1. Save the excel file as CSV format

2. I will manually create the database table by indicating the data-types for every column of my interest.

3. I will upload the csv file to the selected table by ignoring the "column names" as i have defined it at step 2. Decimals are truncated because phpmyadmin has some unexplained algorithm to determine the data type and the size allocated to a column. To prevent that, you create the table as mentioned above at step 2.

Hope it helps!

Answer 2

If you are familiar with html and php, by using this script simplex excel library 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.

CREATE A HTML FORM TO UPLOAD EXCEL SHEET

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
}