In this php post, I am discussing how to import csv file into MySQL database table using PHP.CSV stands for “Comma Separated Values” and contains all data in comma separated.
Its very common task for every application which has too much database and need to import in MySql.
I am using two files one is index.php
file which will contain UI part to show upload CSV file and import_csv.php
file to connect MySQL and parse CSV data and store into MySQL table.
Normally, We need to import data into database then we used following file types.
- Xml file
- CSV file
- Excel file
To import data through CSV file into MySQL Database table.I am doing following steps to import data from CSV
file into MYSQL
using PHP
Script code.
You can also check other tutorial of Export/Import Data with PHP,
- Import CSV File Into MySql Using PHP
- Exporting Data to Excel with PHP and MySQL
- Export Data to CSV and Download Using PHP and MySQL
- Export HTML Table Data to Excel, CSV, PNG and PDF using jQuery Plugin
Steps to import CSV file into MySql database using PHP
Step 1: Create import_csv.php
and put below code into this file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <?php if(isset($_POST["submit"])) { $host="localhost"; // Host name. $db_user="root"; //mysql user $db_password=""; //mysql pass $db='phpDB'; // Database name. //$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error()); //mysql_select_db($db) or die (mysql_error()); $con=mysqli_connect($host,$db_user,$db_password,$db); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } echo $filename=$_FILES["file"]["name"]; $ext=substr($filename,strrpos($filename,"."),(strlen($filename)-strrpos($filename,"."))); //we check,file must be have csv extention if($ext=="csv") { $file = fopen($filename, "r"); while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) { $sql = "INSERT into tableName(name,email,address) values('$emapData[0]','$emapData[1]','$emapData[2]')"; mysqli_query($con, $sql); } fclose($file); echo "CSV File has been successfully Imported."; } else { echo "Error: Please Upload only CSV File"; } } ?> |
Below code is used for creating interface to upload CSV file.
Step 2: Create a new index.php
file to display csv upload form.You need to put below code into index.php
file.
1 2 3 4 5 6 7 8 9 10 11 12 | <form enctype="multipart/form-data" method="post" action="import_csv.php"> <table border="1"> <tr > <td colspan="2" align="center"><strong>Import CSV file</strong></td> </tr> <tr> <td align="center">CSV File:</td><td><input type="file" name="file" id="file"></td></tr> <tr > <td colspan="2" align="center"><input type="submit" value="submit"></td> </tr> </table> </form> |