Import CSV file to MySQL from custom plugin in WordPress

CSV is a widely used format for data dump and bulk data uploading to the database like product details, user data, etc.

The file is consists of one or more fields in a row.

Inserting records one by one is time-consuming and if you already have data available in CSV format then you can import it with the help of PHP script.

In this tutorial, I show how you can import CSV file in your MySQL database from custom plugin in WordPress.

Import CSV file to MySQL from custom plugin in WordPress


Contents

  1. Create Plugin
  2. myplugin.php
  3. displaylist.php
  4. Output
  5. Conclusion

1. Create Plugin

  • Create a new folder myplugin in wp-content/plugins/ folder.
  • Create an img folder in myplugin/folder and store the plugin logo file.
  • Here, create two files –
    • myplugin.php
    • displaylist.php

Create WordPress plugin folder and files


2. myplugin.php

Specify Plugin name, Plugin URI, Description, Version, Author, and Author URI between /* */.

Create Table –

Create plugin_table() function to define customplugin table definition and pass function name in register_activation_hook( __FILE__, 'plugin_table' );.

Add menu –

Create plugin_menu() function.

Add menu using add_menu_page() and pass function name add_action("admin_menu", "plugin_menu");.

Also, create displayList() function to include 'displaylist.php'.

Completed Code

<?php

/*
Plugin Name: My Plugin
Plugin URI: https://makitweb.com
Description: Plugin to demonstrate CSV import
Version: 0.1
Author: Yogesh singh
Author URI: https://makitweb.com
*/

// Create a new table
function plugin_table(){

   global $wpdb;
   $charset_collate = $wpdb->get_charset_collate();

   $tablename = $wpdb->prefix."customplugin";

   $sql = "CREATE TABLE $tablename (
     id mediumint(11) NOT NULL AUTO_INCREMENT,
     name varchar(80) NOT NULL,
     username varchar(80) NOT NULL,
     email varchar(80) NOT NULL,
     age smallint(3) NOT NULL,
     PRIMARY KEY (id)
   ) $charset_collate;";

   require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

}
register_activation_hook( __FILE__, 'plugin_table' );

// Add menu
function plugin_menu() {

   add_menu_page("My Plugin", "My Plugin","manage_options", "myplugin", "displayList",plugins_url('/myplugin/img/icon.png'));

}
add_action("admin_menu", "plugin_menu");

function displayList(){
   include "displaylist.php";
}

3. displaylist.php

Assign table name in $tablename.

Import Form & List records –

Create a <form > and add file and submit button in it.

Fetch all records from customplugin table and list records in <table >.

Import CSV file –

On submit button click check if the file extension is 'csv' or not.

If it is csv then open a file in reading mode and read a record by calling fgetcsv($csvFile) to skip the header row.

Read all data from a file using a while loop and skip the execution if a row doesn’t have 4 columns.

Assing values to variables and checks if a record already exists in the customplugin table.

If not exists then check if all variable has value or not.

Execute insert query and increment $totalInsert by 1 if last insert id is greater than 0.

Completed Code

<?php
global $wpdb;

// Table name
$tablename = $wpdb->prefix."customplugin";

// Import CSV
if(isset($_POST['butimport'])){

  // File extension
  $extension = pathinfo($_FILES['import_file']['name'], PATHINFO_EXTENSION);

  // If file extension is 'csv'
  if(!empty($_FILES['import_file']['name']) && $extension == 'csv'){

    $totalInserted = 0;

    // Open file in read mode
    $csvFile = fopen($_FILES['import_file']['tmp_name'], 'r');

    fgetcsv($csvFile); // Skipping header row

    // Read file
    while(($csvData = fgetcsv($csvFile)) !== FALSE){
      $csvData = array_map("utf8_encode", $csvData);

      // Row column length
      $dataLen = count($csvData);

      // Skip row if length != 4
      if( !($dataLen == 4) ) continue;

      // Assign value to variables
      $name = trim($csvData[0]);
      $username = trim($csvData[1]);
      $email = trim($csvData[2]);
      $age = trim($csvData[3]);

      // Check record already exists or not
      $cntSQL = "SELECT count(*) as count FROM {$tablename} where username='".$username."'";
      $record = $wpdb->get_results($cntSQL, OBJECT);

      if($record[0]->count==0){

        // Check if variable is empty or not
        if(!empty($name) && !empty($username) && !empty($email) && !empty($age) ) {

          // Insert Record
          $wpdb->insert($tablename, array(
            'name' =>$name,
            'username' =>$username,
            'email' =>$email,
            'age' => $age
          ));

          if($wpdb->insert_id > 0){
            $totalInserted++;
          }
        }

      }

    }
    echo "<h3 style='color: green;'>Total record Inserted : ".$totalInserted."</h3>";


  }else{
    echo "<h3 style='color: red;'>Invalid Extension</h3>";
  }

}

?>
<h2>All Entries</h2>

<!-- Form -->
<form method='post' action='<?= $_SERVER['REQUEST_URI']; ?>' enctype='multipart/form-data'>
  <input type="file" name="import_file" >
  <input type="submit" name="butimport" value="Import">
</form>

<!-- Record List -->
<table width='100%' border='1' style='border-collapse: collapse;'>
   <thead>
   <tr>
     <th>S.no</th>
     <th>Name</th>
     <th>Username</th>
     <th>Email</th>
     <th>Age</th>
   </tr>
   </thead>
   <tbody>
   <?php
   // Fetch records
   $entriesList = $wpdb->get_results("SELECT * FROM ".$tablename." order by id desc");
   if(count($entriesList) > 0){
     $count = 0;
     foreach($entriesList as $entry){
        $id = $entry->id;
        $name = $entry->name;
        $username = $entry->username;
        $email = $entry->email;
        $age = $entry->age;

        echo "<tr>
        <td>".++$count."</td>
        <td>".$name."</td>
        <td>".$username."</td>
        <td>".$email."</td>
        <td>".$age."</td>
        </tr>
        ";
     }
   }else{
     echo "<tr><td colspan='5'>No record found</td></tr>";
  }
  ?>
  </tbody>
</table>

4. Output


5. Conclusion

You can remove the fgetcsv() code which I executed after fopen if your CSV file doesn’t contain a header row and it is better to check the number of columns in a row before inserting into the MySQL database.

If you found this tutorial helpful then don't forget to share.