Site icon Tutorials Website: Upgrade Your Web Development & Coding Skills

How to Import and Export CSV File using PHP and MySql

Pradeep Maurya
Import CSV Data into Database

Import and Export include is extremely helpful for the information the board segment. The Import usefulness enables the client to transfer and embed numerous information in the database. Utilizing the Import include, the mass information can be embedded in the database on a solitary snap. The fare usefulness enables the client to download the table information rundown and spare in a document for disconnected use. Utilizing the Export include, various records can be downloaded in a document position.

For the most part, the CSV document position is utilized to import and fare information in the web application. CSV (comma-isolated qualities) record stores the information in plain content configuration and moves information between projects. The import and fare usefulness can be effectively actualized with a CSV document utilizing PHP and MySQL. Import CSV record information in database/Export information to CSV document both can be incorporated with PHP and MySQL. In this instructional exercise, we will tell you the best way to import and fare CSV record information in database utilizing PHP and MySQL.

Make Database Table

To store the part’s information, a table should be made in the database. The following SQL query makes an users table with some essential fields in the MySQL database.


CREATE TABLE users (
 id int(11) NOT NULL AUTO_INCREMENT,
 name varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 email varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 phone varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 created datetime NOT NULL,
 modified datetime NOT NULL,
 status enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In the example import and export script, the following functionality will be implemented.

Create Database Configuration File (dbconfig.php)


<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName     = "Tutorialswebsite";

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>

The dbconfig.php is used to connect the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

Create main file to Upload CSV Fileand Download (index.php)






How to Import and Export CSV File using PHP and MySql







    







    
    

        

             Import
             Export
        

    

    
    

        

            
            
        

    


     
    
        query("SELECT * FROM users ORDER BY id DESC");
        if($result->num_rows > 0){
            while($row = $result->fetch_assoc()){
        ?>
            

        
            

                #ID
                Name
                Email
                Phone
                Status
            

        
        

                
                
                
                
                
            

        
            
No Record found...

Create ( importcsvfile.php ) to Import CSV Data into Database

<?php
// Load the database configuration file
include_once 'dbconfig.php';

if(isset($_POST['importSubmit'])){
    
    // Allowed mime types
    $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
    
    // Validate whether selected file is a CSV file
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){
        
        // If the file is uploaded
        if(is_uploaded_file($_FILES['file']['tmp_name'])){
            
            // Open uploaded CSV file with read-only mode
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
            
            // Skip the first line
            fgetcsv($csvFile);
            
            // Parse data from CSV file line by line
            while(($line = fgetcsv($csvFile)) !== FALSE){
                // Get row data
                $name   = $line[0];
                $email  = $line[1];
                $phone  = $line[2];
                $status = $line[3];
                
                // Check whether member already exists in the database with the same email
                $prevQuery = "SELECT id FROM users WHERE email = '".$line[1]."'";
                $prevResult = $db->query($prevQuery);
                
                if($prevResult->num_rows > 0){
                    // Update member data in the database
                    $db->query("UPDATE users SET name = '".$name."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'");
                }else{
                    // Insert member data in the database
                    $db->query("INSERT INTO users (name, email, phone, created, modified, status) VALUES ('".$name."', '".$email."', '".$phone."', NOW(), NOW(), '".$status."')");
                }
            }
            
            // Close opened CSV file
            fclose($csvFile);
            
            $qstring = '?status=succ';
        }else{
            $qstring = '?status=err';
        }
    }else{
        $qstring = '?status=invalid_file';
    }
}

// Redirect to the listing page
header("Location: index.php".$qstring);
?>

Create ( exportcsvfile.php ) to Export Data into CSV File

<?php 
// Load the database configuration file 
include_once 'dbconfig.php'; 
 
$filename = "users_" . date('Y-m-d') . ".csv"; 
$delimiter = ","; 
 
// Create a file pointer 
$f = fopen('php://memory', 'w'); 
 
// Set column headers 
$fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status'); 
fputcsv($f, $fields, $delimiter); 
 
// Get records from the database 
$result = $db->query("SELECT * FROM users ORDER BY id DESC"); 
if($result->num_rows > 0){ 
    // Output each row of the data, format line as csv and write to file pointer 
    while($row = $result->fetch_assoc()){ 
        $lineData = array($row['id'], $row['name'], $row['email'], $row['phone'], $row['created'], $row['status']); 
        fputcsv($f, $lineData, $delimiter); 
    } 
} 
 
// Move back to beginning of file 
fseek($f, 0); 
 
// Set headers to download file rather than displayed 
header('Content-Type: text/csv'); 
header('Content-Disposition: attachment; filename="' . $filename . '";'); 
 
// Output all remaining data on a file pointer 
fpassthru($f); 
 
// Exit from file 
exit();
?>

The exportcsvfile.php file handles the data export process using PHP and MySQL.

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request OR Chat Using Bottom Right Facebook Chat Box

Exit mobile version