5 Ways to Import Excel into MySQL with PHP

Handling large volumes of data efficiently is a necessity for many websites and applications, and when it comes to database management, MySQL stands out as one of the most popular choices for backend storage. Excel, on the other hand, is widely used for data manipulation and analysis. Integrating the two allows for seamless data transfer and management. This blog post will explore five robust methods to import Excel files into MySQL using PHP, ensuring you can leverage both tools' strengths effortlessly.

Why Import Excel into MySQL with PHP?

Before diving into the methods, let’s understand why integrating Excel data into MySQL can be beneficial:

• Data Integration: It’s the starting point for integrating varied data sources into your web application.
• Automation: Automate data import, reducing manual input errors and time consumption.
• Large Data Sets: Handle large datasets that are typical in Excel but cumbersome to enter manually.
• User-Friendly Interface: Excel’s familiar interface is easier for non-technical users to input or edit data.
• Data Validation: Excel can perform simple validations, easing the data preparation process before import.

Prerequisites

• PHP 7.4+
• MySQL 5.7 or MariaDB 10.3
• Apache or Nginx Server
• Excel files to import
• PHP extensions for Excel file handling (optional, depending on the method used)

Method 1: Using PHPExcel Library

PHPExcel is a library for reading and writing Excel files in PHP. Here’s how to use it:

1. Install PHPExcel via Composer: composer require phpoffice/phpspreadsheet
2. Read the Excel file into PHP:

reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
spreadsheet = reader->load('path/to/yourfile.xlsx');
worksheet = spreadsheet->getActiveSheet();
</code></pre>
<ol start="3">
    <li>Loop through the worksheet and extract the data:</li>
</ol>
<pre><code>
rowCount = worksheet->getHighestRow();
for (row = 2; row <= rowCount; row++) {
    data[] = [
        ‘column1’ => worksheet->getCellByColumnAndRow(1, row)->getValue(),
        ‘column2’ => worksheet->getCellByColumnAndRow(2, row)->getValue(),
        // Add more columns as needed
    ];
}

4. Prepare the SQL insert statement:

insertSQL = "INSERT INTO yourtable (column1, column2) VALUES (?, ?)";
stmt = mysqli->prepare(insertSQL);
foreach (data as datum) {
    stmt->bind_param("ss", datum[‘column1’], datum['column2']);
    stmt->execute();
}
$stmt->close();

💡 Note: Ensure to validate data before inserting into MySQL to prevent SQL injection attacks.

Method 2: Using PHP-MySQL Direct Import

This method uses PHP functions like fgetcsv to read CSV files, which can be created from Excel files:

1. Export the Excel file to CSV manually or via code:

filename = "yourfile.csv";
if ((handle = fopen(filename, "r")) !== FALSE) {
    while ((data = fgetcsv(handle, 1000, ",")) !== FALSE) {
        num = count(data);
        row++;
        if (row == 1) continue; // Skip the header row
        sql = “INSERT INTO yourtable (column1, column2) VALUES (‘” . data[0] . "', '" . data[1] . “’)”;
        mysqli_query(con, sql);
    }
    fclose($handle);
}

🔒 Note: Direct SQL string interpolation can be dangerous. Sanitize inputs to avoid SQL injection.

Method 3: Using LOAD DATA INFILE SQL Statement

This method is efficient for larger datasets:

1. Save Excel as a CSV:
2. Use the following SQL statement:

LOAD DATA INFILE ‘path/to/yourfile.csv’
INTO TABLE yourtable
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘“’
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES
(column1, column2);

🛈 Note: The LOAD DATA INFILE statement requires specific MySQL privileges and file path permissions.

Method 4: Using Google Spreadsheets API with PHP

While not directly an Excel import, Google Spreadsheets can be used as an intermediary:

1. Upload your Excel file to Google Spreadsheets.
2. Use Google Spreadsheets API to fetch the data in PHP:

client = new Google_Client();
client->setAuthConfig(‘path/to/client_secret.json’);
client->addScope(Google_Service_Sheets::SPREADSHEETS);
service = new Google_Service_Sheets(client);
spreadsheetId = ‘your_spreadsheet_id’;
$range = ‘Sheet1!A1:B’; // Define the range you want to read

response = service->spreadsheets_values->get(spreadsheetId, range);
values = response->getValues();

3. Insert the fetched data into MySQL:

foreach (values as row) {
    stmt->bind_param("ss", row[0], row[1]);
    stmt->execute();
}

📋 Note: Google Spreadsheet API requires authentication setup; refer to Google’s API documentation for setup.

Method 5: Using an ETL Tool like Talend

ETL (Extract, Transform, Load) tools can automate the process:

1. Install and configure Talend.
2. Create a job in Talend to read Excel:

tFileInputExcel_1 – tMap – tMysqlOutput_1

3. Map Excel columns to MySQL table columns in Talend’s tMap.
4. Run the job to import data from Excel to MySQL.

⚠️ Note: Setting up ETL tools might require learning new software, but it automates processes and can be reused for various imports.

To wrap things up, integrating Excel data into MySQL via PHP provides a versatile and efficient way to manage and manipulate large datasets. Each method outlined above caters to different needs and scenarios, from direct database manipulation to using APIs or external tools for ETL processes. By leveraging these techniques, you can ensure your data pipeline is both user-friendly and robust, allowing for better data integrity, automation, and scalability in your web applications or data-driven projects.

Can I import large Excel files with these methods?

+

Yes, methods like LOAD DATA INFILE are particularly efficient for large files, as they’re optimized for bulk data loading. However, ensure your server can handle the file size, and consider using chunked imports for extremely large datasets.

How do I handle different Excel formats like .xls and .xlsx?

+

PHPExcel and similar libraries support both .xls (Excel 97-2003) and .xlsx (Excel 2007+) formats. Ensure to use the appropriate reader class based on the file extension.

What are the security considerations when importing data?

+

Prevent SQL injection by sanitizing data, validate and clean inputs before importing, ensure proper file permissions, and handle errors gracefully to avoid exposing database structures.