CARA IMPORT FILE EXCEL KE PHPMYADMIN

Are you struggling to import Excel data into your MySQLi database using PHP 7? Don’t worry, we’ve got you covered. In this article, we will discuss the step-by-step process of importing Excel data into MySQLi database using PHP 7.

Step-by-step Guide for Importing Excel Data into MySQLi Database

Before we dive into the step-by-step guide, it is important to note that there are different methods of importing Excel data into MySQLi database. However, in this article, we will be using one of the most efficient ways – PhpSpreadsheet – a PHP library for reading and writing spreadsheet files.

Step 1: Install PhpSpreadsheet Library

To get started, you need to install the PhpSpreadsheet library, which can be done using Composer. If you don’t have Composer installed, you can download it from https://getcomposer.org/download/.

Once you have installed Composer, run the following command in your project directory to install PhpSpreadsheet:

composer require phpoffice/phpspreadsheet

Step 2: Create a PHP Script

Next, we need to create a PHP script that will read the Excel file and insert it into the MySQLi database. Let us call this file “import.php”.

First, we will start by creating a connection to the database using the “mysqli” function. Here’s the code snippet:

//establishing connection with database
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "my_database";

$conn = mysqli_connect($servername, $username, $password, $dbname);

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

Replace “localhost”, “root”, “password”, “my_database” with your appropriate server name, user, password, and database name respectively.

Next, we will use the PhpSpreadsheet library to load the Excel file. Let’s assume our Excel file is named “data.xlsx” and is located in the same directory as “import.php”. Here’s the code snippet:

//loading the excel file
use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load("data.xlsx");

$worksheet = $spreadsheet->getActiveSheet();

We loaded the Excel file using the “IOFactory::load()” function and assigned it to a variable named “$spreadsheet”. We then fetched the active sheet using the “getActiveSheet()” function and assigned it to a variable named “$worksheet”.

The next step is to loop through the rows in the Excel file and insert them into the MySQLi database using the “mysqli_query()” function. Here’s the code snippet:

//looping through each row and inserting into mysqli database
foreach ($worksheet->getRowIterator() as $row) 
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); 
    
    $sql = "INSERT INTO data_table (column_1, column_2, column_3) VALUES (";
    
    foreach ($cellIterator as $cell) 
        $value = $cell->getValue();
        $sql .= "'" . mysqli_real_escape_string($conn, $value) . "', ";
    
    
    $sql = rtrim($sql, ", ");
    $sql .= ')';
    
    mysqli_query($conn, $sql);

In this code snippet, we used the “getRowIterator()” function to loop through each row in the Excel file. We then used the “$cellIterator” variable to loop through each cell in the row and we concatenated the cell values to form the “VALUES” string in our SQL statement. Note how we used the “mysqli_real_escape_string()” function to prevent SQL injection attacks.

We have successfully imported Excel data into our MySQLi database using PHP 7. Congratulations!

Frequently Asked Questions (FAQs)

Q1. What if my Excel file has multiple sheets?

A: You can use the “getActiveSheet()” function to fetch a specific sheet by passing an index number or a sheet name as a parameter. For example:

$worksheet = $spreadsheet->getSheetByName("Sheet2");

or

$worksheet = $spreadsheet->getSheet(1);

Q2. How do I verify that the data has been successfully imported into the database?

A: You can run a SELECT query to fetch the data from the MySQLi database. Here’s an example:

$select_query = "SELECT * FROM data_table";
$result = mysqli_query($conn, $select_query);

if (mysqli_num_rows($result) > 0) 
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) 
        echo "column_1: " . $row["column_1"]. " - column_2: " . $row["column_2"]. " - column_3: " . $row["column_3"]. "
";
    
 else 
    echo "0 results";

This code will fetch all the rows from the “data_table” table and output them to the browser.

Conclusion

Importing Excel data into MySQLi database using PHP 7 doesn’t have to be a daunting task. By following the simple steps outlined in this article, you can easily import your Excel data into MySQLi database using the PhpSpreadsheet library. Remember to always sanitize your data to prevent SQL injection attacks.

We hope that you found this article helpful. If you have any questions or comments, please feel free to leave them below. Don’t forget to share this article with your friends and colleagues!