How Can PHP Import Excel to MySQL using an PHP XLSX Reader and Excel XLSX Converter

XLSX is a file format used to create an Excel spreadsheet. It was first intruduced by Microsoft in Excel 2007. It is XML-based, which makes it easier to transfer data between applications.

A XLSX file stores data in worksheets in the form of cells. Cell are contained in rows and columns which can have multiple properties of the cell data like styles, formatting, alignment, etc..

XLSX is in reality a simple ZIP archive. It contains multiple XML files, which have all the information of the spreadsheet properties.

There are multiple ways to parse XLSX with PHP. As it is was mentioned above, a XLSX file is a XML-based ZIP archive.

You just need to extract the ZIP file content files and parse them using any XML parser. But it is easier to use a ready-made XLSX parser as it might contain more functionality. There are few popular packages available to parse XLSX using PHP:

2. PHPExcel

Using the SimpleXLSX package you can get data from the spreadsheet in form of rows. You can use that information for your own purposes.

Since a XLSX file contains multiple files, it cannot be imported directly into MySQL. So the recommended way is to convert the XLSX file into a CSV and then import that to the DB.

There is also plenty of desktop software like e.g. Excel2MySQL, Navicat, MySQL for Excel, Mr. Data Converter etc.. You can use the XLSX parsers in PHP to do this too. In the next section I will be going into more details about that approach.

As I mentioned above, you can use XLSX parsers written in PHP to import XLSX file data into a MySQL database. You might wonder why go to all the trouble of using parsers when there are other easier ways of importing XLSX into a MySQL database.

Well there can be many uses for this approache including, importing given user provided XLSX files into a database automatically. This can be done using SimpleXLSX in a couple of ways.

The first method is to parse the XLSX file in PHP and then change it to CSV and import that in MySQL, it can be done using a code similar to the one below:

Using the above code you can parse an XLSX file in to a CSV file and then you can easily import the CSV file into the MySQL database.

Now the other method to do this is to parse the XLSX file into an array and import it into the db using the mysqli or PDO extensions. You can do this by using a code simillar to the following:

It is fairly easy to parse an XLSX file. The SimpleXLSX package provides an easy way to read and convert any XLSX file so it can be processed in any way your PHP application needs or even insert the data into a MySQL database for instance.

Share this article with other colleague PHP developers that can benefit from this information. If you have questions post a comment below

Related posts:

Decentralized Finance (DeFi) is one of the most eye-catching fields in DApps, and the automated market maker, also known as AMM, is the rising star in the DeFi space. AMM has changed the classic…

The Washington Wizards had a great season, winning 49 games and making it to a game seven in the Eastern Conference semi-finals. It was the most games that the Wizards have won since the 1978–79…

Buy Google Voice account numbers used as virtual numbers Through which you can have a lot of safety. The US uses this number for business purposes and hides their personal phone numbers. In many…