CARA IMPORT FILE EXCEL PHP

Are you struggling with importing Excel data into your MySQL database using PHP? You’re not alone! Many developers encounter issues with importing Excel files into their database. That’s why we’ve put together this informative article to guide you through the process.

What is Excel Data Import?

Excel data import is the process of importing data from an Excel spreadsheet into a database, like MySQL, using PHP programming language. This is usually done to transfer data from one system to another or to keep records of data in a database for future use.

When importing Excel data into a MySQL database, each column in the Excel spreadsheet is mapped to a table column in the database. It’s essential to use a parser to read the data from the file and insert it into the database.

How to Import Excel Data into MySQL Database with PHP?

Now that we have a good understanding of what Excel data import is let’s go through the process of importing Excel data into a MySQL database using PHP.

Step 1: Create a Database

The first step when importing Excel data into MySQL is to have a database set up where you will insert the data. If you already have a database, you can skip to the next step. If not, you can use phpMyAdmin to create a new database.

Step 2: Create a Table

The next step is to create a table for the data to be inserted. In our example, we will create a table named “users”, with four columns: “id”, “name”, “email”, and “phone”.

<?php
    $host = "localhost";
    $user = "username";
    $pass = "password";
    $db = "database_name";
    $conn = mysqli_connect($host, $user, $pass, $db);

    if (!$conn) 
        die("Connection Failed: " . mysqli_connect_error());
    

    $sql = "CREATE TABLE users (
                id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) NOT NULL,
                phone VARCHAR(255) NOT NULL
            )";

    if (mysqli_query($conn, $sql)) 
        echo "Table created successfully";
     else 
        echo "Error creating table: " . mysqli_error($conn);
    

    mysqli_close($conn);
?>

Step 3: Parse Excel Data

The next step is to parse the Excel data and insert it into the MySQL database. We’ll use the PHPExcel library to parse our Excel file. First, we’ll need to download it from https://github.com/PHPOffice/PHPExcel.

<?php
    require_once 'Classes/PHPExcel/IOFactory.php';

    $file_path = 'path/to/excel-file.xlsx';
    $objPHPExcel = PHPExcel_IOFactory::load($file_path);

    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) 
        $worksheetTitle = $worksheet->getTitle();
        $highestRow = $worksheet->getHighestRow(); // e.g. 10
        $highestColumn = $worksheet->getHighestColumn(); // e.g. 'F'
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

        for ($row = 2; $row <= $highestRow; ++$row) 
            $rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                NULL, TRUE, FALSE);

            $name = $rowData[0][0];
            $email = $rowData[0][1];
            $phone = $rowData[0][2];

            $conn = mysqli_connect("localhost", "username", "password", "database_name");

            $sql = "INSERT INTO users (name, email, phone) VALUES ('$name', '$email', '$phone')";

            if (mysqli_query($conn, $sql)) 
                echo "Data inserted successfully";
             else 
                echo "Error inserting data: " . mysqli_error($conn);
            

            mysqli_close($conn);
        
    
?>

FAQs:

Q: Can we import multiple Excel files simultaneously?

A: Yes, you can import multiple Excel files simultaneously. All you need is to process the files one by one and insert the data into the database.

Q: How do we handle Excel files with different formats?

A: You can use a library like PHPExcel to handle Excel files with different formats. PHPExcel automatically detects the format of the Excel file and reads it accordingly.

Videos:

Here is a useful video on how to import Excel data into MySQL database using PHP:

In conclusion, importing Excel data into MySQL database using PHP is not a difficult process once you understand the basic concepts and have the right tools. With the right steps and knowledge, you can easily import your Excel data into your database and use it for further analysis and processing.