comment download le file excel dans mysql databse et vérifier la validation du file

J'ai besoin de download un file au format excel ou csv dans la database mysql, et le code devrait également vérifier la validation que chaque colonne devrait get une valeur appropriée, par exemple: la colonne de nom doit seulement recevoir une input de caractère ou aucune colonne ne doit être vide. Voici un code qui fonctionne pour le format csv, maintenant, aidez-moi avec le téléchargement de file excel. index.php

<!doctype html> <html> <head> <title>Upload your Files</title> <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script> <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script> <meta charset="utf-8"> <style> body{ background: url(http://p1.pichost.me/i/13/1356818.jpg); } p{ font-size: 20px; } .hint { font-size: 10px; } footer { text-align: center; width:100%; height:80px; position:absolute; bottom:0; left:0; } </style> </head> <body> <nav class="navbar navbar-inverse navbar-fixed-top" role="navigation"> <div class="container"> <div class="navbar-header"> <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse"> <span class="sr-only">Toggle navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> <a class="navbar-brand" href="#">CSV To MySql</a> </div> <div class="navbar-collapse collapse"> <ul class="nav navbar-nav navbar-right"> <li class="active"><a href="#">Home</a></li> <li><a href="#about">About</a></li> <li><a href="#about">How It Works</a></li> <li><a href="#contact">Contact Me</a></li> </ul> </div> </div> </nav> <br> <br> <br> <br> <div class=" col-md-offset-4 col-xs-offset-4"> <form enctype="multipart/form-data" action="called.php" method="post" role="form"> <div class="form-group"> <p>File Upload</p> <input type="file" name="file" id="file" size="150"> <p class="hint">Only CSV File Import.</p> </div> <button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button> </div> </form> <footer>&copy;Parvez Alam</footer> </body> </html>

Mon file php appelé.php

 <?php if(isset($_POST["Import"])) { $host='localhost'; $db_user= 'root'; $db_password= ''; $db= 'product_record'; $conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error()); mysql_select_db($db) or die (mysql_error()); echo $filename=$_FILES["file"]["tmp_name"]; if($_FILES["file"]["size"] > 0) { $file = fopen($filename, "r"); while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) { $sql = "INSERT into prod_list_1(name,category,brand,price) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')"; mysql_query($sql); } fclose($file); echo 'CSV File has been successfully Imported'; } else echo 'Invalid File:Please Upload CSV File'; } ?>

Tout d'abord, utilisez DOP ou MySQLi … L'extension mysql est dépréciée.

J'ai déjà eu un problème similaire, mais je devais m'assurer que les valeurs ne sont pas dupliquées, et s'il y a un double, une page devrait me faire part d'une notification.

La méthode que j'utilise est différente. J'ai un formulaire simple pour download un file csv, une page pour parsingr le file téléchargé, et une fois que je suis d'accord avec l'parsing, le file est téléchargé sur la database.

Fichier 1: csv_upload.php

 <form enctype="multipart/form-data" id="csv_upload" name="csv_upload" action="csv_analysis.php" method="POST"> <table border="0" width="40%" cellpadding="0" cellspacing="0"> <tr> <th height="30">Select csv File</th> </tr> <tr> <td height="50"><input name="file" type="file" /><br></td> </tr> <tr> <td><input type="submit" value="Upload File" /></td> </tr> </table> </form>

Fichier 2: csv_analysis.php

  <?php session_start(); //DB Connection: require "dbcon.php"; if(is_uploaded_file($_FILES['file']['tmp_name'])){ //Process the CSV file $findings = ' <form method="post" action="csv_upload_action.php"> <table width="100%"> <tr> <th width="5%">No</th> <th width="19%">att0</th> <th width="19%">att1</th> <th width="19%">att2</th> <th width="19%">att3</th> <th width="19%">att4</th> </tr>'; $handle = fopen($_FILES['file']['tmp_name'], "r"); $data = fgetcsv($handle, 5000, ","); //Remove if CSV file does not have column headings $i = 1; $insert_record = 0; $update_record = 0; $file_duplicate_count = 0; $file_duplicate = array(); while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) { $att0 = $data[0]; $att1 = $data[1]; $att2 = $data[2]; $att3 = $data[3]; $att4 = $data[4]; //Check if row is in database already : Notification shown but record overwritten in DB $stmt = $db->query("SELECT * FROM table_name WHERE att0 = '$att0' AND att1 = '$att1' "); $count = $stmt->rowCount(); if($count > 0){ $findings = $findings . ' <tr class="'.$class.'"> <td>'.$i.'</td> <td><font color="#FF6600"><b>DB Duplicate<b></font></td>'. '<td>'.$att0.'</td>'. '<td>'.$att1.'</td>'. '<td>'.$att2.'</td>'. '<td>'.$att3.'</td>'. '<td>'.$att4.'</td>'. '</tr>'; //Update Statement: $_SESSION['insert'] .= "UPDATE table_name SET att2 = '$att2', att3 = '$att3', att4 = '$att4' WHERE att0 = '$att0' AND att1 = '$att1' ;"; $update_record ++; } //Check if the record is a part of the file (File Duplicates): elseif(array_search($att0.strtotime($att1), $file_duplicate) != false) { $findings = $findings . ' <tr> <td>'.$i.'</td> <td><font color="#FF6666"><b>File Duplicate<b></font></td>'. '<td>'.$att0.'</td>'. '<td>'.$att1.'</td>'. '<td>'.$att2.'</td>'. '<td>'.$att3.'</td>'. '<td>'.$att4.'</td>'. '</tr>'; $file_duplicate_count ++; } //Row is unique else{ //Add INSERT statement to INSERT queue $_SESSION['insert'] .= "INSERT INTO table_name ( att0, att1, att2, att3, att4 ) VALUES ( '$att0', '$att1', '$att2', '$att3', '$att4', );"; $insert_record ++; //Add row for row to findings table and mark unique $findings = $findings . ' <tr class="'.$class.'"> <td>'.$i.'</td> <td><font color="#3333CC"><b>OK</b></td>'. '<td>'.$att0.'</td>'. '<td>'.$att1.'</td>'. '<td>'.$att2.'</td>'. '<td>'.$att3.'</td>'. '<td>'.$att4.'</td>'.'</tr>'; } $i++; //using the array (to be used as a unique identifier for file duplicates): $file_duplicate[] = $att0.strtotime($att1); } $findings = $findings . ' <tr> <td colspan="13"> <br> <center> Please note that ensortinges that are highlighted with <b>"DB Duplicate"</b> will be updated in the schedule. This action can not be undone. </center> <br> <div align="center"> <input type="submit" value="Proceed" /></form><br><form action="upload_action.php"><input type="submit" value="Cancel" /></form> </div> </td> </tr> </table> '; $_SESSION['update_record'] = $update_record; $_SESSION['insert_record'] = $insert_record; $_SESSION['duplicated_records'] = $file_duplicate_count; echo $findings; } else{ header("Location:csv_upload.php"); } ?>

** Fichier 3: csv_upload_action.php **

  <?php session_start(); //DB Connection: require "dbcon.php"; $update_record = $_SESSION['update_record']; $insert_record = $_SESSION['insert_record']; $file_duplicate_count = $_SESSION['duplicated_records']; if(!isset($_SESSION['insert'])){ header("Location:csv_upload.php"); } $queries = explode(';', $_SESSION['insert']); foreach($queries as $query){ if($query != ""){ $stmt = $db->prepare($query); $stmt->execute(); } } echo "<center><br><br>CSV File uploaded<br>"; if($insert_record != 0){ echo $insert_record." records added to the database.<br>"; } if($update_record != 0){ echo $update_record." records were modified.<br>"; } if($file_duplicate_count != 0){ echo $file_duplicate_count." records were duplicated in the uploaded file, they were not written to the database.<br>"; } unset($_SESSION['insert']); ?>

Ne peut-être pas exactement ce que vous cherchez, mais j'espère que cela vous aidera.