How to Import CSV into MySQL using PHP


0

In this tutorial we are going to discuss Import CSV into MySQL using PHP

PHP is vastly used for creating a wide range of products ranging from web application to business level applications. The key to powerful PHP code is to follow proper workflows and automate processing. The result is effective and error-free code.

In nearly all PHP web applications, record is stored, accessible to and exchanged between assorted components of the web app. To make sure that this exchange and access to record goes freely and without any issues, the development team must make sure that the DB and record dumps are in proper format.

Import and export of record to and from DB is a common plenty procedure in PHP web application development. Further important activity is the backup and transfer of DB.

<?php

$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample";
$databaseusername ="test";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.csv";

/********************************/
/* Would you like to add an empty text field at the beginning of these data?
/* This is helpful if you have a sql table with the first data field being an auto_increment integer value
/* and the CSV file doesn't  have, such as empty data field before the data.
/* Set 1 for yes and 0 for no. Note: do not set to 1 if you are not certain.
/* This can sample data in the wrong data fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/

/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 1;
$outputfile = "output.sql";
/********************************/

if (!file_exists($csvfile)) {
        echo "File not found. Make sure you specified the correct path.\n";
        exit;
}

$file = fopen($csvfile,"r");

if (!$file) {
        echo "Error opening data file.\n";
        exit;
}

$size = filesize($csvfile);

if (!$size) {
        echo "File is empty.\n";
        exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

        $lines++;

        $line = trim($line," \t");

        $line = str_replace("\r","",$line);

        /************************************
        This line escapes the special character. remove it if entries are already escaped in the csv file
        ************************************/
        $line = str_replace("'","\'",$line);
        /*************************************/

        $linearray = explode($fieldseparator,$line);

        $linemysql = implode("','",$linearray);

        if($addauto)
                $query = "insert into $databasetable values('','$linemysql');";
        else
                $query = "insert into $databasetable values('$linemysql');";

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

        @mysql_query($query);
}

@mysql_close($con);

if ($save) {

        if (!is_writable($outputfile)) {
                echo "File is not writable, check permissions.\n";
        }

        else {
                $file2 = fopen($outputfile,"w");

                if(!$file2) {
                        echo "Error writing to the output file.\n";
                }
                else {
                        fwrite($file2,$queries);
                        fclose($file2);
                }
        }

}

echo "Found a total of $lines records in this csv file.\n";

?>


Like it? Share with your friends!

0
Developer

0 Comments