Skip to content

Instantly share code, notes, and snippets.

@kawadhiya21 kawadhiya21/k.md Secret
Last active Aug 29, 2015

Embed
What would you like to do?

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.

Introduction

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.

Creation of sql tables

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.

  1. Login using mysql command with user and password

Mysql Login

  1. Create a fresh database.

Mysql Database Creation

  1. Create a new table inside the database.

Mysql Table Creation

We can proceed on to scripting

PHP Script

We can write all of it in a single file. But we divide it into subparts.

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 message Could 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.

  1. r : Open for reading only; place the file pointer at the beginning of the file.
  2. r+: Open for reading and writing; place the file pointer at the beginning of the file.
  3. 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.
  4. 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)

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.

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,

  1. $data[0] has '1' //id
  2. $data[1] has 'Sam' //name
  3. $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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.