Reading and importing CSV file into database using PHP


Introduction:

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]);
                }

                $i++;
            }
            fclose($handle);

            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 "
";
print_r($csvArray);
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.



$query = '';
foreach ($csvFormatedArray as $key => $value) {


    $query .= '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`) ';

    $query .= " 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']."' ";

    $query .= " ); \n\n";

}



You can see we are not doing any database operation inside the loop, so that we are building the query and finally executing as single query. By doing this you are able to optimize the code.

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);
    $q->execute();
} catch(PDOException $e) {
    echo $e->getMessage();
}


Conclusion:

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.

Related Posts Plugin for WordPress, Blogger...
Follow Us On Facebook Twitter Google+ Linkedin
Copyright @2011-2013 www.appsntech.com. All rights reserved. Powered By- Sanjoy Dey Productions