CSV files or commonly known as Excel (.xls) files used as spread sheets used to store information in a tabular manner. Although modern CSV handlers are capable of most of the functions right from calculating sum, average, median to making graphs, charts and tables. But at times we need to store that data in SQL format so that it can be parsed easily, or lets say able to get displayed on browser or for secure storage. SQL is not doubt the best medium for such jobs. But typing or even copy-pasting each data to sql is a tiresome and menial job.
Here comes PHP in action. It has the ability to parse CSV files and then we can do whatever we want to do with them.
To start first let us understand what is CSV. It means 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 just avoid errors.
Assuming that you use mysql (even postgres will work as its sintax is similar) we start with creation of tables. You can use phpmyadmin as well. But for sake, we are using the terminal method. Open the bash with Ctrl+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 way off the topic). The steps are as follows.
- Login using mysql command with user and password
- Create a fresh database.
- Create a new table inside the database.
We can proceed on to scripting
We can write all of it in a single file. But we divide it into subparts.
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)
Where $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.
So in this way, all of csv file can be stored as a database and further manipulations using sql to filter and sort the data. There can be few exceptions like delimiter can be different etc but I hope you can follow the explanation and docs a bit to make up for that.
Here goes the github repository for the same.