Reading and importing CSV file into database using PHP


Hi guys, my last article was about "Building RESTful Web Service using Cygnite PHP Framework, MySQL" . Today I would like to write about “Reading csv file using php”. This article may be useful for beginners or if you are willing to write flexible CSVReader library. Let me show you how you can read and return simple array as Column and value pair using simple line of code.

Step 1: Reading CSV file:

Create a file call CsvReader.php and paste below code.

 function readCsvToArray($path, $delimiter = ';')
        $csvArray = $headerArray = $csvFilledArray = array();

        if (($handle = fopen($path, "r")) !== FALSE) {
            $i = 0;
            while (($dataArray = fgetcsv($handle, 4000, $delimiter)) !== FALSE) {

                $count = count($dataArray);

                for ($j=0; $j< $count; $j++) {

                    if ($i ==0) {
                        $headerArray[$i][$j] = str_replace(' ', '_', $dataArray[$j]);
                    } else {
                        $csvArray[$i][$j] = $dataArray[$j];

                if ($i != 0) {
                    $csvFilledArray[$i] = array_combine($headerArray[0], $csvArray[$i]);


            return $csvFilledArray;

Step 2 : Returning output as Array:

Now include the library file and call the function. Find the demo CSV file here.

require 'CsvReader.php';
$path = 'country_csv_reading_using_php.csv';

$csvArray = array(); 
$csvArray =  readCsvToArray($path, ‘;’);

echo "
echo "

Step3: Importing data into Mysql Database:

Let us consider our table is “CountryInfo” and table has columns as follows.

"Sort_Order", "Common_Name", "Formal_Name", "Type",
"Sub_Type", "Sovereignty", "Capital", "ISO_4217_Currency_Code",
 "ISO_4217_Currency_Name", "ITU-T_Telephone_Code", "ISO_3166-1_2_Letter_Code", 
"ISO_3166-1_3_Letter_Code", "ISO_3166-1_Number", "IANA_Country_Code_TLD"

Now we can loop throw the array and insert values into the database.

Paste below code to prepare all queries together and execute it.

$sql = '';

$sql = 'INSERT INTO `CountryInfo` (`Sort_Order, `Common_Name`, `Formal_Name`, `Type`,
      `Sub_Type`, `Sovereignty`, `Capital`, `ISO_4217_Currency_Code`,
      `ISO_4217_Currency_Name`, `ITU-T_Telephone_Code`, `ISO_3166-1_2_Letter_Code`, `ISO_3166-1_3_Letter_Code`,
      `ISO_3166-1_Number`, `IANA_Country_Code_TLD`) VALUES';

$data = array();

foreach ($csvFormatedArray as $key => $value) {

    $data[] = " VALUES ( '".$value['Sort_Order']."', '".
                  $value['Common_Name']."', '".
                  $value['Formal_Name']."', '".
                  $value['Type']."', '".
                  $value['Sub_Type']."', '".
                  $value['Sovereignty']."', '".
                  $value['Capital']."', '".
                  $value['ISO_4217_Currency_Code']."', '".
                  $value['ISO_4217_Currency_Name']."', '".
                  $value['ITU-T_Telephone_Code']."', '".
                  $value['ISO_3166-1_2_Letter_Code']."', '".
                  $value['ISO_3166-1_3_Letter_Code']."', '".
                  $value['ISO_3166-1_Number']."', '".
                  $value['IANA_Country_Code_TLD']."' )";

if (count($values)) {
    $sql = $sql.implode(',', $data);
    # build query

As the "Best Coding Practice" you can see we are not doing any database operation inside the loop, building the query and finally executing as single query. By doing this you are not running n number of queries instead single optimised query to save all.

Step 4: Inserting into database:

Let us now connect with the database and import csv content into database.

try {
    $conn = new PDO("mysql:host=localhost;dbname=tutorials", 'root', '');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line
    $q = $conn->prepare($query);
} catch(PDOException $e) {
    echo $e->getMessage();


Hope enjoyed the post and you can build modular library on the top of this small step by step article.

If you find above tutorial helpful, please don’t forget to give a small like or share with friends, also leave your comments below.

Have a nice day. Keep visiting for interesting articles.

No comments:

Post a Comment

Follow Us On Facebook Open Source Web Developers by Appsntech facebook group Twitter Open Source Web Developers by Appsntech twitter group Google+ Open Source Web Developers by Appsntech Google group Linkedin Open Source Web Developers by Appsntech, LinkedIn group
Copyright @2011-2015 All rights reserved. Powered By- Sanjoy Dey Productions