How To Import Xlsx File Into MySQL Database Using PHP
Last Updated : Mar 11, 2024
In this article we will show you the solution of how to import xlsx file into MySQL database using PHP, reading data from an XLSX Excel spreadsheet file and entering it into a MySQL database table are the two steps involved in importing an XLSX file using PHP.
The procedure normally entails reading the XLSX file using a PHP library like PHPExcel and then connecting to the MySQL database using the MySQLi or PDO extensions.
The data from the XLSX file is then parsed and transformed into a format that's able to be entered into database utilising SQL INSERT commands.
This method is helpful when you need to rapidly and effectively utilise PHP to import big volumes of data from an Excel file into a MySQL database.
So let's talk about the idea of utilising php to import an xlsx file into a mysql database.
Step By Step Guide On How To Import Xlsx File Into MySQL Database Using PHP :-
<?php // Establish connection to MySQL database $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "database_name"; $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Include PHPExcel library require_once 'PHPExcel/PHPExcel.php'; // Create new PHPExcel object $objPHPExcel = PHPExcel_IOFactory::load('example.xlsx'); // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // Loop through each row of the worksheet for ($row = 1; $row <= $highestRow; $row++) { // Retrieve cell values $cellA = $sheet->getCellByColumnAndRow(0, $row)->getValue(); $cellB = $sheet->getCellByColumnAndRow(1, $row)->getValue(); $cellC = $sheet->getCellByColumnAndRow(2, $row)->getValue(); // Construct query to insert data into MySQL database $sql = "INSERT INTO table_name (columnA, columnB, columnC) VALUES ('$cellA', '$cellB', '$cellC')"; // Execute query if ($conn->query($sql) === TRUE) { echo "Row inserted successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } } // Close connection to MySQL database $conn->close(); ?>
- You can see that we have written the PHP code to import an xlsx file into a MySQL database here.
- This code initially creates a new object of the mysqli class and passes in the database credentials to establish a connection to a MySQL database.
- The script is then terminated and an error message is displayed if the connection wasn't successful.
- The PHPExcel library is then included, and the xlsx file is loaded to generate a new PHPExcel object.
- It then loops through every row of the worksheet after retrieving the dimensions—that is, the number of rows and columns—of the first worksheet in the file.
- The code retrieves the first three cell values for each row before creating a SQL query to insert the information into the MySQL database.
- Using the mysqli object's query() method, the query is carried out.
- The script produces an error message if the query is unsuccessful and a success message if it is.
- The MySQL database connection is then terminated by the script.
Conclusion :-
Thus, we were able to understand the principle of utilising php to import an xlsx file into a mysql database.
We also discovered that such a PHP code import an xlsx file into a MySQL database by cycling through every row of the first worksheet, extracting the cell values, and then creating and running a SQL query to enter the data into a MySQL table.
By changing the relevant portions of the code, this programme can be modified to work with various file formats & database systems.
I hope this article on how to import xlsx file into MySQL database using PHP helps you and the steps and method mentioned above are easy to follow and implement.