How to Import CSV to MySQL database with PHP

CSV files, commonly known as Excel (.xls) files, are used as spread sheets to store information in a tabular manner. Modern CSV handlers are capable of most of the functions — like calculating sum, average, median to making graphs, charts and tables. But, at times we may need to store the data in SQL format to be parsed easily, or for secure storage. SQL is not doubt the best medium for such kind of jobs. But typing or even copy-pasting each data to SQL is a tiresome job.

This is where PHP comes in action. It has the ability to parse CSV files, which allows us to do further processing. Let us see how to import the CSV to MySQL database using PHP.

Introduction

To start first let us understand what is CSV. It stands for “Comma Separated Values”, easy isn’t it? Unlike other easy sounding difficult things, it actually is very easy. I bet few users have already started imagining that how they can use explode() and split data but PHP has many functions to directly deal with them.

We will have an index.php file and test.csv. Use the absolute path to avoid any errors.

Creation of SQL tables

Assuming that you use MySQL (even PostgreSQL will work as its sintax is similar), we start with creation of tables. You can use PhpMyAdmin as well. For this tutorial, we are using the terminal method. Open the bash withCtrl/Cmd + Alt + t. You must know the username and password of MySQL before hand (and we will not discuss how to install MySQL as it is off the topic). The steps are as follows.

  • Login using mysql command with your username and password

Mysql Login

  • Create a fresh database

Mysql Database Creation

  • Create a new table inside the database

Mysql Table Creation

We can then proceed to creating the script to extract data from the CSV file and insert into the database.

PHP Script

We can write all of it in a single file. But we divide it into subparts for readability. Note that if you are working on a large project, you should separate out these parts if you plan to develop a full fledged application.

Connecting to Database

We start by connecting to the database. We will also report the errors and block the script in case of errors.

<?php
$host = "localhost"; 
$user = "root";
$pass = "asdf1234"; 
$db = "csv"; 
$con = mysql_connect($host, $user, $pass);
if (!$con) {
    echo "Could not connect to server\n";
    die(mysql_error());
} else {
    echo "Connection established\n"; 
}

$con1 = mysql_select_db($db);

if (!$con1) {
    echo "Cannot select database\n";
    die(mysql_error()); 
} else {
    echo "Database selected\n";
}
?>

We store the database, username, host and passwords in variables. Then mysql_connect is processed and the result is stored in $con. This is then checked for any errors and if errors exist, the script dies showing messageCould not connect to server followed by error. Similarly, checks for database selection is also performed.

Relevant PHP functions

PHP – fopen

This is the file handler provided by PHP. It is usually used as:

fopen ( string $filename , string $mode)

where $filename is used to specify the absolute path or URL of the file. $mode can have following values depending upon the situation and usage.

  • r : Open for reading only; place the file pointer at the beginning of the file.
  • r+: Open for reading and writing; place the file pointer at the beginning of the file.
  • w : Open for writing only; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.
  • w+: Open for reading and writing; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.

and few more exist but these are the usual ones [quoted from – fopen

PHP – fgetcsv

This is a relatively simple function fetching 1 full row at a time in the form of an array.

fgetcsv($handle,$limit,$delimiter)

Here, $handle specifies the file handle coming from fopen$limit specifying the number of maximum columns and $delimiter which is the delimiting character set as , in our code. Since we have 1 full row, each time, we loop in over while and and insert each set of data over and over again.

Opening and Parsing CSV

After the connection to database, we can simply open the CSV file and insert the data to the table.

if (($handle = fopen("/var/www/test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $sql = "INSERT INTO record ( id, name, marks) VALUES ( '".mysql_escape_string($data[0])."','".mysql_escape_string($data[1])."','".mysql_escape_string($data[2])."')";
        $query = mysql_query($sql);
        if($query){
            echo "row inserted\n";
        }
        else{
            echo die(mysql_error());
        }
    }
    fclose($handle);
}

?>

$sql contains our INSERT command which prepares the sql statement for inserting 3 values to our 3 fields.$data contains the row. For example, if we consider the first row,

  • $data[0] has ‘1’ //id
  • $data[1] has ‘Sam’ //name
  • $data[2] has ’79’ //marks

mysql_escape_string is used to prevent sql-injection.$query has the result of each query. It is also checked for errors. Once $data does not receive anything, it exits the while loop. fclose($handle) closes the file handle.

This marks the end of the script. At the end, we can find that the script ran and the data is successfully inserted in our database. It can be confirmed by logging into MySQL. You can also execute a print statement after each insert if you like to see the status of the script in real time (almost).

In this way, all of information in the CSV file can be stored as a database and further manipulations can be performed using PHP and SQL to filter and sort the data. There can be a few exceptions like a different delimiter but I hope you can follow the explanation and docs a bit to make up for that.

Here is the GitHub repository that contains the code and files used in the tutorial.

Comments

  1. i want to read as $data[‘id’], $data[‘name’],$data[‘marks’]; to insert into DATABASE instead of below lik this//

    $data[0] has ‘1’ //id
    $data[1] has ‘Sam’ //name
    $data[2] has ’79’ //marks

  2. i want to read as $data[‘id’], $data[‘name’],$data[‘marks’]; to insert into DATABASE instead of below lik this//

    $data[0] has ‘1’ //id
    $data[1] has ‘Sam’ //name
    $data[2] has ’79’ //marks,

  3. i want to read as $data[‘id’], $data[‘name’],$data[‘marks’]; to insert into DATABASE instead of below lik this//

    $data[0] has ‘1’ //id
    $data[1] has ‘Sam’ //name
    $data[2] has ’79’ //marks,……..
    ;

  4. i want to read as $data[‘id’], $data[‘name’],$data[‘marks’]; to insert into DATABASE instead of below lik this//

    $data[0] has ‘1’ //id
    $data[1] has ‘Sam’ //name
    $data[2] has ’79’ //marks,……..
    ;

  5. how to update the record in csv file to database

Leave a Reply to anu Cancel reply

*