How to import excel file into mysql using php

Managing large amounts of data is a crucial aspect of any business or organization, and databases are an essential tool for this purpose. MySQL is a popular open-source relational database management system used by many developers and organizations.

One common task is to import data from Excel spreadsheets into MySQL databases. In this blog post, we will explore how to import an Excel file into MySQL using PHP.

We will discuss the steps involved in the process, including setting up the database connection, reading the Excel file, and inserting the data into MySQL tables.

We will also provide sample code and tips to ensure a smooth import process. Whether you are a beginner or an experienced developer, this guide will help you import Excel data into MySQL quickly and easily.

How to import excel file into mysql using php

Importing an Excel file into MySQL using PHP involves several steps, including setting up the database connection, reading the Excel file, and inserting the data into MySQL tables. Here is a step-by-step guide on how to import an Excel file into MySQL using PHP:

Step 1: Set up the database connection The first step is to establish a connection to your MySQL database using PHP. This can be done using the mysqli or PDO extension in PHP. Here is an example of connecting to the database using mysqli:

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

Step 2: Read the Excel file
Next, you need to read the Excel file using a PHP library such as PHPExcel. You can download the PHPExcel library from its official website or install it using Composer. Here is an example of how to read an Excel file using PHPExcel:

require_once 'PHPExcel/Classes/PHPExcel.php';

// Load the Excel file
$objPHPExcel = PHPExcel_IOFactory::load("filename.xlsx");

// Get the active worksheet
$worksheet = $objPHPExcel->getActiveSheet();

// Get the highest row number and column letter
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();

Step 3: Insert the data into MySQL tables Finally, you can insert the data from the Excel file into MySQL tables using SQL queries. You need to loop through the rows in the Excel file and execute an INSERT query for each row. Here is an example of how to insert data into MySQL tables using mysqli:

// Loop through each row in the Excel file
for ($row = 1; $row <= $highestRow; $row++) {
    // Get the values of each cell in the row
    $rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
    
    // Build the SQL query
    $sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('" . $rowData[0][0] . "', '" . $rowData[0][1] . "', '" . $rowData[0][2] . "')";

    // Execute the SQL query
    if (mysqli_query($conn, $sql)) {
        echo "Record inserted successfully";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
}

Make sure to replace “table_name” and “column1”, “column2”, “column3” with your own table and column names. Also, be careful when inserting data into MySQL tables to avoid SQL injection attacks.

That’s it! With these steps, you can easily import Excel data into MySQL using PHP.

Hope this will help you to import your excel file into mysql db.
Thanks 🙂