Automatically Populate Database Table with .CSV file using PHP & MySQL

ยท

4 min read

Do you know you can automatically populate a database with as much data as you want at once? i.e Uploading multiple records into a database table at once.

For example, let`s say you are working on a school cbt system and you need to upload questions and answers into the database a few days before the exam. You have over 100 questions from different subjects.

Or you just collected responses from an online survey with over a thousand people e.g Full Name, Age, Gender, Email Address, Phone Number, etc, and you want to upload all these to the database.

So how do you solve this, instead of inserting the data one after the other? You know this can be too stressful right? ๐Ÿ˜ฃ

What is CSV?

A CSV (comma-separated values) file is a text file that has a specific format that allows data to be saved in a table structured format.

Why is CSV better than Excel?

Comparing CSV vs Xlsx, CSV files are faster and also consume less memory whereas Excel consumes more memory while importing data. Comparing CSV vs Excel, CSV files can be opened with any text editor in windows while Excel files can't be opened with text editors.

In this article, I will be walking us through how to populate a database with a CSV file using PHP and MySQL.

Tools Required

  1. Xampp server download here https://www.apachefriends.org/download.html.
  2. Text editor of your choice but in this tutorial, we will be using vscode download here https://code.visualstudio.com/download.
  3. Browser (any of your choice).

Before we begin, I believe you already have an idea of working with Xampp i.e Creating a new database, tables, and inserting records, you can check this out on youtube https://www.youtube.com/watch?v=jLqBiSDNXO0.

So now let`s begin ๐Ÿ˜Ž

  1. Let`s set up our project folder. Create a new folder 'csv_upload' inside C:\xampp\htdocs Inside the folder you just created, create the following files:
- index.php
- dbconnec.php
- populate.php

image.png

Now let`s create the database where the data will be stored.

  • Open a browser of your choice (e.g Chrome, Firefox, Microsoft edge). Make sure Xampp is started and running.
  • Type localhost/phpmyadmin/, create a new database called 'populate', and create a table called 'userdetails_tbl' with 5 fields (columns) id, full_name, age, gender, email_address

or run this SQL query to automatically create the table with the fields.

CREATE TABLE userdetails_tbl (id INT NOT NULL AUTO_INCREMENT , full_name VARCHAR(20) NOT NULL , age VARCHAR(20) NOT NULL , gender VARCHAR(20) NOT NULL , position VARCHAR(20) NOT NULL , PRIMARY KEY (id))

image.png

  1. Now let's create the CSV file and insert the sample data of your choice.

  2. Open Excel on your computer and create a new document.

  3. insert raw data and ensure it follows the same field pattern of the database table we just created. i.e full_name, age, gender, email.
  4. Click on save as and select CSV (comma delimiter).

See the sample data here you can make use of https://drive.google.com/file/d/1FO90ioDKl8mjGikUhmpQfaiiTdldWVVh/view?usp=sharing

image.png

Now let`s go ahead and start writing some script into the files we created earlier.

index.php


<?php

    include './popluate.php';

?>

<!DOCTYPE html>

<html>

<head>

    <title>CSV Upload</title>

</head>

<body>

    <div class="container-fluid">

        <div class="jumbotron" style="width: 50%; margin:auto">

            <form action="" method="post" enctype="multipart/form-data">

                <h3> Upload CSV file </h3>

                <div class="input-group">

                    <label>Select File</label>

                    <input type="file" class="form-control" name="file_to_upload" accept=".csv">

                </div>

                <div class="input-group">    

                    <input type="submit" class="btn btn-success btn-block" value="Upload" name="upload_file">

                </div>

            </form>

        </div>

    </div>

</body>

</html>

dbconnect.php

<?php

// CONNECTING TO THE DATABASE

$host = "localhost";

$user = "root";

$pass = "";

$dbname = 'populate';

$conn = @mysqli_connect($host, $user, $pass);

try {

    if (!$conn) throw new Exception("Server Not Found", 1);

} catch (Exception $e) {

    die($e->getMessage());

}

?>

populate.php

<?php

include './dbconnect.php';

if (isset($_POST["upload_file"])) {

    // check if the database exists

    if (!mysqli_select_db($conn, $dbname)) {

        echo "Database Not found!!!";

    } else {

        $file_to_upload = $_FILES["file_to_upload"]["tmp_name"];

        if ($_FILES["file_to_upload"]["size"] > 0) {

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

            while (($getData = fgetcsv($file)) !== FALSE) {

                $sql = mysqli_query($conn, "INSERT INTO userdetails_tbl(`full_name`,`age`,`gender`, `position`) VALUES('" . $getData[0] . "','" . $getData[1] . "', '" . $getData[2] . "', '" . $getData[3] . "')");

                if (!isset($sql)) {

                    echo "Invalid File: Please Upload CSV file";

                } else {

                    echo "File has been uploaded successfully";

                }

            }

            fclose($file);

        }

    }

}

?>

The dbconnect.php handles the database connection i.e the 'populate' database we created earlier. Make sure the value of variable $dbname is exactly the name of the database you are connecting to that has already been created.

The populate.php is where the upload process takes place. First, after the user has selected the CSV file, it checks if the database exists. If it exists, it stores the CSV file in a variable called $file_to_upload then loop through the CSV file and inserts it into the database with the index value. In this case, the number of fields we have in the CSV file is 4 so the index from its array is 0,1,2,3.

I hope you found this article helpful in building your web application but for further discussion or questions, you can reach out to me. โ™ฅ๏ธ

ย