CSV Import Into MySQL in PHP

"CSV import into MySql in PHP

Data Import/Export allow us to upload data from external sources to database and write data to CSV from database. From this tutorial, we will learn how to import data from CSV sheet in Mysql database using PHP and vice versa. In web development, Sometime it is necessary to upload bulk data in database. So just inserting data one by one is very difficult and time consuming process. Here we will learn how to upload bulk data in database from CSV sheet.

Here is an example to add user info into the user_info table from an uploaded CSV file, populating the following three fields: (name, email, mobile).

Firstly we need a CSV file including multiple rows/columns of user data like name, email and mobile etc.

CSV

Secondly we need a table in MySQL database with following fields : id name email mobile

Table Structure :

CREATE TABLE `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`mobile` int,
PRIMARY KEY (`id`)
);

I’ve included the form to upload CSV file and PHP code below to import CSV file to MySQL database :

<?php if (!$_POST) { ?>
<html>
    <body>
        <form action="" method="post" enctype="multipart/form-data">
            Choose your file: <br /> 
            <input name="csv" type="file" id="csv" /> <br /> <br /> 
            <input type="submit" name="Submit" value="Submit" /> 
        </form>
    </body>
</html>
<?php
} else {
$connect = new mysqli("localhost", "username", "password", "database");
if ($_FILES[csv][size] > 0) {
    //get the csv file 
    $file = $_FILES[csv][tmp_name]; 
    $handle = fopen($file, "r");
    $i = 0;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if ($i > 0) {
$import = "INSERT into user_info(name,email,mobile) values('$data[0]','$data[1]','$data[2]')";
            $connect->query($import);
        }
        $i++;
    }
    fclose($handle);
    print "Import done";
}
}
?>

Form :

CSV

In above code, after uploading a CSV file via html form, we have to establish database connection. Then get CSV handle with fopen function. Once we got CSV handle, get CSV content with the help of fgetcsv function and apply while loop to iterate all rows. With the help of while loop INSERT command will insert data into table.

CSV

To export data from table to CSV : This operation need some PHP code and header code make the operation successful from the browser, that’s why we have to use the header code in this.

<?php
$connect = new mysqli("localhost", "username",      "password", "database");
$sql = "SELECT * FROM user_info";   
$data = $connect->query($sql);
if ($data->num_rows > 0) {
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment;         filename=user_info.csv');
$data_string = "\"Sr.No.\",\"Name\",\"Email\",\"Mobile\"\n";
$i = 1;
while ($row = $data->fetch_assoc()) {
    $data_string .= "\"" . $i .
        "\",\"" . $row['name'] .
        "\",\"" . $row['email'] .
        "\",\"" . $row['mobile'] .
        "\"\n";
    $i++;
}
echo $data_string;
unset($data_string);
}
?>

To export data from MySQL, first establish database connection. Then select data from user_info table. Here we use header() to define content type, CSV info like name and to make it downloadable. In last, with the help of while loop we append all data into a variable and print that variable to download CSV automatically.

Output of downloaded CSV :

CSV

Comments