Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Import Data from CSV file in Mysql Database

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 114
    Comment on it

    Hello friends,

    I am sharing that how we can save csv file data in mysql database. Csv Data consists of rows and columns with comma separated.

    for example we have a csv file which contains user first name, last name, age and designation. like this :-
    Rahul, Rana, 24, Software Developer
    Manoj, Tiwari, 23, Doctor
    So this above information we have in our csv file. Now I will write a code to save above data in mysql database.

    Step 1 . Create a New folder and give name Classes.
    Step2 . Create a Connection.php file inside the folder Classes and write the piece of code :-

        <?php
        /**
         * @author: Your Name <your email>
         * @description: Connection file for Multiple Database 
         */
        final class Connection
        {
            private static $user = "USERNAME"; // put your mysql user here 
            private static $password = "PASSWORD"; // put your mysql password here
            private static $host = "localhost";
            private static $database = "";
            public static $conn = "";
    
            function Connection ( $database )
            {
                ini_set ( "display_errors" , 1 );
                self::$database = $database;
                if ( self::$database == "user_db" ) 
                {
                    self::$conn = mysqli_connect ( self::$host , self::$user , self::$password , self::$database ) or die ( "<br> Please check your Connection Details" );
                }
            }
    
            static function connectionClose ()
            {
                self::$conn -> close ();
            }
    
        }
    ?>
    

    In the above step we are connecting mysqli database.


    Step 3 . Now create import_csv.php file in your project root directory and write a piece of code:-

    <?php
    
    /**
     *  @author Your Name <Your Email>
     *  @description Import Csv file according to conditions. 
     */
    ini_set ( "display_errors" , 0 );
    if ( isset ( $_REQUEST[ "csv" ] ) and $_REQUEST[ "csv" ] != "" )
    {
        require_once $_SERVER[ "DOCUMENT_ROOT" ] . '/Classes/Connection.php';
        new Connection ( "user_db" );
        Connection::$conn -> autocommit ( FALSE );
       /*
         I have used Switch case here because user can upload many csv files like smss, revenues etc etc
        */
        switch ( $_REQUEST[ "csv" ] )
        {
            case 'users':
                importUsers ();
                break;
    
            default:
                break;
        }
    }
    
    /**
     * @descripton Import users method in users table
     * @throws Exception
     * 
     */
    function importUsers ()
    {
        $file = fopen ( "users.csv" , "r" );
        $msg = array ();
    
        while ( ($emapData = fgetcsv ( $file , 10000 , "," )) !== FALSE )
        {
                $firstName = Connection::$conn -> real_escape_string ( $emapData[ 0 ] ) ;
                $lastName = Connection::$conn -> real_escape_string ( $emapData[ 1 ] );
                $age = Connection::$conn -> real_escape_string ( $emapData[ 2 ] );
                $designation = Connection::$conn -> real_escape_string ( $emapData[ 3 ] );
    
                $query = Connection::$conn -> query ( "INSERT INTO `user_db`.`users` (`first_name`,`last_name`,`age`,`designation`,`created`,`updated`) VALUES('" . $firstName . "','" . $lastName . "','" . $age . "','" . $designation . "',now(),now()) " );
                $msg [] = ($query == TRUE) ? 1 : 0;
        }
        try
        {
            if ( in_array ( 0 , $msg ) )
            {
                throw new Exception ( "Some Problem occurred, while uploading csv file" );
            }
            Connection::$conn -> commit ();
            echo "File saved Successfully";
        }
        catch ( Exception $ex )
        {
            echo $ex -> getMessage ();
        }
        fclose ( $file );
        Connection::connectionClose ();
    }
    ?>
    

    Enjoy the code, if you found any problem , please let me know, I will try my best to help you.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: