#How to Import CSV to MYSQL 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.
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.
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 with Ctrl/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
mysqlcommand with your username and password
- Create a fresh database
- Create a new table inside the database
We can then proceed to creating the script to extract data from the CSV file and insert into the database.
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.
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 message Could not connect to server followed by error. Similarly, checks for database selection is also performed.
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
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.
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.