2

I'm having an strange issue, while importing the .xlsx file in to the mysql database using php script.

The excel files are being generated from another oracle db server, and are being copied on my server. The script is not inserting any record until and unless, I manually

  1. open the file,
  2. Save it, and
  3. close it.

This is pretty strange, because I'm not making any change in the file, no change at all. I've already compared the file permissions, before and after saving the files and there is no change.

For the excel files, I'm creating on my current server are working fine without any hassle.

DB File 

    // Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
    else{
        echo "Connection Made Succesfully !!!";
    }
?>

CODE Sample

<?php
include 'db.php';
if (isset($_FILES['file'])) {
    IF($_POST["TotalCol"] && $_POST["TableName"])
    {
        require_once "simplexlsx.class.php";
        $xlsx = new SimpleXLSX( $_FILES['file']['tmp_name'] );
         list($cols, $rows) = $xlsx->dimension();
           foreach( $xlsx->rows() as $k => $r) 
             { // loop through excel worksheet
                 $sub = mysql_escape_string($r[0]);
                 for ($x = 1; $x < $_POST["TotalCol"]; $x++) {
                    $sub .= "','".mysql_escape_string($r[$x]);
                    } 
                 //$sub = mysql_escape_string($r[0])."','" . mysql_escape_string($r[1]);
                 //$q = "insert into test2 value('" . mysql_escape_string($r[0])."','" . mysql_escape_string($r[1])."','" . mysql_escape_string($r[2]) . "');";
                 $q = "insert into ".$_POST["TableName"]." value('" . $sub . "');"

                    if ($conn->query($q) === TRUE) {
                        echo "New record created successfully";
                    } else {
                        echo "<br>// Error: " . $sql . "<br>" . $conn->error . "<br>";
                    }
            } // IF ENDS HERE
        }
}
?>

<h1>Upload</h1>
<form method="post" enctype="multipart/form-data">
*.XLSX <input type="file" name="file"  /></br><input type="Text" name="TableName" class="TableName" placeholder="TableName" /></br><input type="Text" name="TotalCol" class="TotalCol" placeholder="Total Columns" />&nbsp;&nbsp;<input type="submit" value="Insert" />
</form>

And I'm using the simplexlsx.class.php Class from here

Maham Fatima
  • 59
  • 8
  • Did you check the extension of Excel file before and after? – CapeStar Dec 22 '15 at 19:41
  • Just verified, there is no change in it either. – Maham Fatima Dec 22 '15 at 19:49
  • $q = "insert into ".$_POST["TableName"]." value('" . $sub . "');" ';' missing. i think its a typo. Does your script echo the data? – CapeStar Dec 22 '15 at 19:56
  • I think, it is happening because we don't have MS Excel installed on the Oracle server. But, I'm not sure. – Maham Fatima Dec 22 '15 at 19:56
  • One more think try to use PHPEXCEL its working good for me. – CapeStar Dec 22 '15 at 19:57
  • @CapeStar its not a typo. We dont need semi-colon at the end, because this statement is running in the foreach, meaning it will only be inserting one record at a time. – Maham Fatima Dec 22 '15 at 19:58
  • All I know about this is a 'syntax error' may be you have a super Compiler which does not show you error. – CapeStar Dec 22 '15 at 20:31
  • Your code is about uploading files via POST, what does this have to do with locally stored files mentioned in your question? Also, [do not use database functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) which have been unsupported for 5 years. Won't even work in current version of PHP. – miken32 Dec 22 '15 at 22:11
  • xlsx files can have their cell data stored in either "inline strings" or "shared strings" (I'm conjuring up these terms from memory). Some libraries assume/support only one and not the other, and Excel may be "fixing" the file when you open, save, and close -- it's *really* bad about doing that, in fact. I'm unfamiliar with this php library, but it I strongly suspect this is somehow relevant. – Michael - sqlbot Dec 23 '15 at 02:02
  • @Michael-sqlbot, it makes sense. I guess, I'll have to configure a bot script to open all excel files and save them before executing this script. Thanks. – Maham Fatima Dec 23 '15 at 14:20
  • @miken32, I was just communicating the scenario. For the db functions, I'll read about it. I'm new to PHP, so dont know much about it. Thanks for the prompt. – Maham Fatima Dec 23 '15 at 14:23
  • When you're new to something, don't start by learning the wrong way! – miken32 Dec 23 '15 at 17:05

1 Answers1

0

I guess, the issue is occurring because we dont have MS Office/Excel installed on the oracle server, from where the excel files are being generated.

xlsx files can have their cell data stored in either "inline strings" or "shared strings" (I'm conjuring up these terms from memory). Some libraries assume/support only one and not the other, and Excel may be "fixing" the file when you open, save, and close -- it's really bad about doing that, in fact. I'm unfamiliar with this php library, but it I strongly suspect this is somehow relevant. – Michael - sqlbot 12 hours ago

Therefore, I'm configuring a bot script (using AutoIT) to open all excel files and save them, before executing this script.

If anyone knows any better workaround, then please do mention it. Thanks.

Maham Fatima
  • 59
  • 8