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.
Contents
1. Create Plugin
- Create a new folder
myplugininwp-content/plugins/folder. - Create an
imgfolder inmyplugin/folder and store the plugin logo file. - Here, create two files –
- myplugin.php
- displaylist.php
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.
