Import & Export CSV File Using PHP

Import & Export CSV File Using PHP

Published May 13,2022 by Kailash Singh

0 Comment     8270 Views    

In this tutorial, we are going to teach you, how to create Import & Export CSV File Using PHP.

Step 1 : Create table in Database.

CREATE TABLE `elevenstech_youtube`.`excel_detail` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(300) NULL , `email` VARCHAR(200) NULL , `phone` VARCHAR(100) NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Step 2 : Create .csv file.

In this file, you need to add Name, Email and Phone and after save in .csv format.

Otherwise you can download dummy file. Click here to download

Step 3 : Create Form to upload .csv file

<form method="post" action="" enctype="multipart/form-data">
	
	<?php echo $msg; ?>

	<input type="file" name="file" class="form-control">

	<button type="submit" name="import" class="btn btn-primary">Upload</button>
		
</form>

Step 4: Create connection to the database

$con = mysqli_connect('localhost','root','','elevenstech_youtube');

Step 5: Add php code to import csv file data

<?php 
 
$msg = '';

if(isset($_POST['import']))
{

	//get file extension
    $getfile = basename($_FILES["file"]["name"]);
   	$extention = @end(explode('.', $getfile));
	$lover_extention = strtolower($extention);
	
    
    $filename = $_FILES["file"]["tmp_name"];

    // check only file accepted 
    $allowTypes = array('csv'); 
    if(in_array($lover_extention, $allowTypes))
    {
	    if($_FILES["file"]["size"] > 0)
	    {
	        
	        //open file and read
	        $file = fopen($filename, "r");
	        $i = 0;
	        while (($col = fgetcsv($file, 10000, ",")) !== FALSE) 
	        {
	        	if($i > 0 && $col[0] !='')
	            {
		            $insert = "INSERT INTO excel_detail (name,phone,email)values('".$col[0]."','".$col[1]."','".$col[2]."')";
		            mysqli_query($con,$insert);
		        }
		        $i++;
	        }
	        $msg = '<p style="color: green;"> CSV Data inserted successfully</p>';

	    }
	}
	else{
		$msg = '<p style="color: red;">File extension should be .csv format</p>';
	}

}

?>

Step 6: Now we will try to get the user details whatever we uploaded in the database with the help of csv file

<?php 
	$fetchsql = "SELECT * FROM excel_detail";
	$fetchrow = mysqli_query($con,$fetchsql);
	$fetchcount = mysqli_num_rows($fetchrow);

 	if($fetchcount > 0)
	{ 
		echo '<h3>User List:</h3>
			<form action="" method="POST" >
				<button type="submit" name="export" class="btn btn-primary">Export</button>
				<br>

				<table class="table table-bordered table-striped">
					<thead>
						<tr>
							<th></th>
							<th>S.No.</th>
							<th>Name</th>
							<th>Email</th>
							<th>Phone</th>
						</tr>
					</thead>
					<tbody>';
			$k = 1;		
	      	while($get = $fetchrow->fetch_assoc())
	        { 
				echo'<tr>
						<td><input type="checkbox" name="userid[]" class="checkbox" value="'.$get['id'].'"></td>
						<td>'.$k.'</td>
						<td>'.$get['name'].'</td>
						<td>'.$get['email'].'</td>
						<td>'.$get['phone'].'</td>
					</tr>';
		 		$k++;
		    }
		    echo '</tbody>
			</table>

		</form>';
	} 
?>

Step 7: Now you can see that I have created an Export button above this table. Then when we click on this button we will get the user details in a .xls Format. Now we will write the PHP code to export the data.

<?php 

if(isset($_POST['export']))
{
  $users = @$_POST['userid'];
  if(sizeof($users) > 0)
  {	
  	  $content = '<table><tr><th>Name</th><th>Email</th><th>Phone</th></tr>';
      for ($j=0; $j < sizeof($users); $j++) 
      { 
          $result = mysqli_query($con, "SELECT * FROM excel_detail where id = '".$users[$j]."' ");
          while($row = mysqli_fetch_array($result))  
          {  
              $content .='<tr>';
              $content .='<td>'.$row['name'].'</td>';
              $content .='<td>'.$row['phone'].'</td>';
              $content .='<td>'.$row['email'].'</td>';
              $content .='</tr>';   
          } 
      }
      $content.='</table>'; 

      header('Content-Type:application/xls');  
      header('Content-Disposition: attachment; filename=users.xls');

      echo $content;
      exit();
  	}
   
}

?>