Cara Import File CSV ke Database MySQL Menggunakan PHP & Ajax

Import file csv adalah suatu kegiatan upload file excel dengan format csv. Dimana pada artikel tentang Cara Import File CSV ke Database MySQL Menggunakan PHP & Ajax yang artinya kita akan melakukan upload file ke dalam database MySQL.

Langkah pertama Cara Import File CSV ke Database MySQL Menggunakan PHP & Ajax yaitu kita akan membuat databasenya. Teman-teman copy saja script di bawah ini.

CREATE TABLE IF NOT EXISTS `import` ( 

  `id` int(11) NOT NULL AUTO_INCREMENT, 

  `name` varchar(250) NOT NULL, 

  `email` varchar(300) NOT NULL, 

  PRIMARY KEY (`id`) 

 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Kemudian selanjutnya kita akan membuat struktur htmlnya sebagai berikut dengan nama file index.php.

<?php 

 $connect = mysqli_connect("localhost", "dumet", "school", "test"); 

 $query = "SELECT * FROM import ORDER BY id desc"; 

 $result = mysqli_query($connect, $query); 

 ?> 

 <!DOCTYPE html> 

 <html> 

      <head> 

           <title>Cara Import File CSV ke Database MySQL Menggunakan PHP & Ajax</title> 

           <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script> 

           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" /> 

           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> 

      </head> 

      <body> 

           <div class="container" style="width:900px;"> 

                <h2 align="center">Import CSV File Data into MySQL Database using PHP & Ajax</h2>   

                <form id="import_csv" method="post" enctype="multipart/form-data"> 

                     <div class="col-md-3"> 

                          <br /> 

                          <label>Pilih File</label> 

                     </div> 

                     <div class="col-md-4"> 

                          <input type="file" name="data_csv" style="margin-top:15px;" /> 

                     </div> 

                     <div class="col-md-5"> 

                          <input type="submit" name="upload" id="upload" value="Upload" style="margin-top:10px;" class="btn btn-info" /> 

                     </div> 

                     <div style="clear:both"></div> 

                </form> 

                <br /><br /><br /> 

                <div class="table-responsive" id="tabledata"> 

                     <table class="table table-bordered"> 

                          <tr> 

                               <th width="5%">ID</th> 

                               <th width="25%">Nama</th> 

                               <th width="35%">Email</th> 

                          </tr> 

                          <?php while($row = mysqli_fetch_array($result)) { ?> 

                          <tr> 

                               <td><?php echo $row["id"]; ?></td> 

                               <td><?php echo $row["name"]; ?></td> 

                               <td><?php echo $row["email"]; ?></td>

                          </tr> 

                          <?php } ?> 

                     </table> 

                </div> 

           </div> 

      </body> 

 </html> 

 <script> 

      $(document).ready(function(){ 

           $('#import_csv').on("submit", function(e){ 

                e.preventDefault();

                $.ajax({ 

                     url:"import.php", 

                     method:"POST", 

                     data:new FormData(this), 

                     contentType:false,   

                     cache:false,           

                     processData:false,   

                     success: function(data){ 

                          if(data=='Error1'){  

                               alert("Format File Salah");

                          }else if(data == "Error2"){ 

                               alert("Silahkan Pilih File"); 

                          }else{ 

                               $('#tabledata').html(data); 

                          } 

                     } 

                }) 

           }); 

      }); 

 </script> 

Langkah selanjutnya yaitu, kita akan membuat file import.php, dimana isinya adalah perintah untuk menyimpan data ke dalam database dari file csv.

<?php 

if(!empty($_FILES["data_csv"]["name"])){ 

      $connect = mysqli_connect("localhost", "dumet", "school", "test"); 

      $output = ''; 

      $format_file = array("csv"); 

      $extension = end(explode(".", $_FILES["data_csv"]["name"])); 

      if(in_array($extension, $format_file)){ 

            $file_data = fopen($_FILES["data_csv"]["tmp_name"], 'r'); 

            fgetcsv($file_data); 

            while($row = fgetcsv($file_data)){ 

                $name = mysqli_real_escape_string($connect, $row[0]); 

                $email = mysqli_real_escape_string($connect, $row[1]);

                $query = "INSERT INTO import VALUES ('','$name', '$email')"; 

                mysqli_query($connect, $query); 

            } 

            $select = "SELECT * FROM import ORDER BY id DESC"; 

            $result = mysqli_query($connect, $select); 

            $output .= ' 

                <table class="table table-bordered"> 

                  <tr> 

                      <th width="5%">ID</th> 

                      <th width="25%">Nama</th> 

                      <th width="35%">Email</th>  

                  </tr> 

            '; 

           while($row = mysqli_fetch_array($result)){ 

                $output .= ' 

                  <tr> 

                      <td>'.$row["id"].'</td> 

                      <td>'.$row["name"].'</td> 

                      <td>'.$row["email"].'</td>

                  </tr> 

                '; 

           } 

           $output .= '</table>'; 

           echo $output; 

      }else{ 

           echo 'Error1'; 

      } 

 }else{ 

      echo "Error2"; 

 } 

 ?>             

Jika sudah selesai maka import file csv kedalam database MySQL sudah berhasil kita lakukan.

Demikian Cara Import File CSV ke Database MySQL Menggunakan PHP & Ajax. Semoga bermanfaat dan selamat mencoba.