How to import a CSV file into MySQL using PHP
belongs to collection: PHP Programming Exercises
All Answers
total answers (1)
belongs to collection: PHP Programming Exercises
total answers (1)
Php programming
The CSV stands for "Comma-separated-values", as it uses a comma to separate values. This is a widely used file format that stores data in a tabular format. We generally use this in business, data-based applications for data exchange. Most organisations are web-based, so there may also be a common need to import data from a spreadsheet or a CSV file to a database. The data in a CSV is stored as sequences of records. With the help of PHP, we can easily store each comma-separated sequence in a database row.
Suppose we have the following data stored in a CSV file -
Anjali,anjali@example.com,878433948 Priska,priska@example.com,493905490 Abhi,abhi@example.com,403022139 Smith,smith@example.com,504903904Here is the script with a step-by-step code explanation. First, we create a main file 'index.php' that we will call in the browser. In this, we take an HTML file upload form with a submit button.
index.php
At the top of the 'index.php' page, we have used two PHP constants UPLOAD_DIR and MAXSIZE to define the upload directory and maximum allowed file size limit respectively.
define('UPLOAD_DIR', '/var/uploaded_files/'); define('MAXSIZE', 7340032); // allow max 7 MBNext, we have defined all the allowed file extensions in an array $ALLOWED_FILEEXT and all the allowed MIME types in an array $ALLOWED_MIME.
$ALLOWED_MIME = array('text/comma-separated-values', 'text/csv', 'text/plain', 'application/csv', 'application/excel', 'application/vnd.ms-excel', 'application/vnd.msexcel');Next, we create a function name 'allowedfile()' in the same 'index.php' file that accepts a temporary file name and destination path as parameters. In this, we got the uploaded file extension using the PHP predefined function pathinfo() and the mime type of the uploaded file using mime_content_type() function. The allowedfile() function returns TRUE if both file extension and MIME type of the uploaded file are allowed.
function allowedfile($tempfile, $destpath) { global $ALLOWED_MIME; $file_ext = pathinfo($destpath, PATHINFO_EXTENSION); $file_mime = mime_content_type($tempfile); $valid_mime = in_array($file_mime, $ALLOWED_MIME); $allowed_file = ($file_ext == 'csv') && $valid_mime; return $allowed_file; }Next, create a function 'handleUpload()' and validate the file size and call the 'allowedfile()' function within it before moving the file to its destination.
function handleUpload() { $temp = $_FILES['file']['tmp_name']; $filename = basename($_FILES['file']['name']); $file_dest = UPLOAD_DIR. $filename; $is_uploaded = is_uploaded_file($temp); $valid_size = $_FILES['file']['size'] <= MAXSIZE && $_FILES['file']['size'] >= 0; if ($is_uploaded && $valid_size && allowedfile($temp, $file_dest)) { move_uploaded_file($temp, $file_dest); insertCSV($file_dest); } else { $response = 'Error: uploaded file size or type is not valid.'; } return $response; }Here, we have added different error handling cases and called 'handleUpload()' within one of the cases. PHP returns an appropriate error code along with the file array. It is found in the file error segment or, $_FILES['file']['error'], that returns the error code if any problem is created during the file upload.
// Handle Error if (!empty($_FILES)) { echo $error = $_FILES['file']['error']; switch($error) { case UPLOAD_ERR_OK: $response = handleUpload(); break; case UPLOAD_ERR_INI_SIZE: $response = 'Error: file size exceeds the allowed.'; break; default: $response = 'An unexpected error occurred; the file could not be uploaded.'; break; } } else { $response = 'Please upload CSV file'; } echo $response;Next, we have created a function 'insertCSV()' to store data in the database. Here is the table structure, you can either copy paste this in your database, or you can use your existing MySQL table.
CREATE TABLE IF NOT EXISTS `empdata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(25) NOT NULL, `email` varchar(100) NOT NULL, `phone` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;function insertCSV($filename){ $conn = mysqli_connect('hostname', 'username', 'password', 'database'); //Check for connection error if($conn->connect_error){ die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error); } if($fileHandle = fopen($filename, "r")){ while(($row = fgetcsv($fileHandle, 0, ",")) !== FALSE) { $insert = "INSERT into empdata(name,email,phone) values('$row[0]','$row[1]','$row[2]')"; if(mysqli_query($conn, $insert)){ echo 'Data inserted successfully'; } else{ echo 'Error: '.mysqli_error($conn); } } fclose($fileHandle); echo "CSV File has been successfully Imported."; } }