CODEWITHSUNDEEP
mysqlexcelphpmyadminrounding
user845454
user845454

Reputation: 41

Help Importing an Excel File into MySQL using 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.

Upvotes: 2

Views: 9611

Answers (2)

sravis
sravis

Reputation: 3680

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
}

Upvotes: 2

madi
madi

Reputation: 5662

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!

Upvotes: 0

Related Questions