Parse CSV and enter in database

Hi,

CSV is one of the most convenient ways of importing and exporting data. CSV is nothing but a simple text files with fields seperated by ",". It can be edited by any spreadsheet editor, such that each cell is seperated by a ",". So if you want to parse a CSV file, you can do it by simply reading the file by fopen() of PHP, read each line, explode each line with a deliminator as ",".

But there is a simpler way to do that by using the PHP function, fgetcsv(). This reads 1 row at a time and returns the cell's in that row as an array. But before we start writing the code, the format of the CSV needs to be predetermined. That means the sequence of fields should be decided before starting the parsing.

Download the source code with the database. In my example I am maintaining the following table schema:

CREATE TABLE IF NOT EXISTS `csv_table` (
  `id` int(10) NOT NULL DEFAULT '0',
  `username` varchar(30) DEFAULT NULL,
  `phone` varchar(10) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
)


Before going into the code and explanation lets see the output of the code. This is the content of my CSV file:


Id,Username,Phone,Email
10021,John,22190097,john@mailinator.com
10022,Paul,556447885,paul@mailinator.com

The output of the above after executing my code is as:

In the above I am converting the CSV into table structure and storing in the database. 

Code and Explanation:

First I am reading the CSV file and traversing each line with the following code:

<?php

if (($handle = fopen("sample.csv", "r")) !== FALSE) {

$i=0;

while (($data = fgetcsv($handle, 0, ",")) !== FALSE)   // 0 specifies unlimited size of each row seperated by ","

{

                // code to manipulate with the CSV data

  }

}
?>

Now the code to create the table and updating the database is as:


if($i>0){
$q="insert into csv_table set id='".$data[0]."', username='".$data[1]."', phone='".$data[2]."', email='".$data[3]."'";
mysql_query($q);
}
?>
<tr>
<?php
foreach($data as $rec){
echo '<td>'.$rec.'</td>';
}
?>
</tr>
<?php
$i++;
}


In the above the most important thing is the $data variable. Whenever the fgetcsv() is executed, it reads a new row in the CSV and returns all the cells as an array, I am storing it in the $data variable and making the database update.

That's all you need to do. Download csv.zip