How to Convert Excel to CSV in PHP

Learn how to convert Excel to CSV in PHP using PhpSpreadsheet. Extract structured data from .xls and .xlsx files and save it in CSV format for better compatibility.

Introduction

CSV (Comma-Separated Values) files are widely used for data exchange, database imports, and system integrations. Converting Excel files to CSV in PHP allows applications to process data efficiently while maintaining compatibility with databases and other systems.

With PhpSpreadsheet, you can:

• Convert .xls and .xlsx files to .csv format
• Extract structured data and preserve accuracy
• Automate the conversion process for bulk file processing

This guide covers:

• Installing PhpSpreadsheet
• Converting Excel (.xls and .xlsx) to CSV
• Handling multiple sheets during conversion
• Saving, downloading, and automating the process

1. Installing PhpSpreadsheet for Excel Handling

To handle Excel files in PHP, install PhpSpreadsheet via Composer:

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in your PHP script:

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

Once installed, your PHP application is ready for Excel to CSV conversion.

2. Loading an Excel File and Converting It to CSV

To convert an Excel file (data.xlsx) into a CSV file:

$file = 'data.xlsx';
$spreadsheet = IOFactory::load($file);
$writer = new Csv($spreadsheet);

$writer->setDelimiter(",");
$writer->setEnclosure('"');
$writer->setLineEnding("\n");
$writer->save('output.csv');

echo "Excel file successfully converted to CSV.";

Explanation:

• Loads an existing Excel file (data.xlsx)
• Converts and saves it as output.csv
• Sets delimiters, enclosures, and line endings

The CSV output is now ready for further processing or database imports.

3. Handling Multiple Sheets During Conversion

If the Excel file has multiple sheets, convert each sheet separately:

$spreadsheet = IOFactory::load('multi_sheet.xlsx');

foreach ($spreadsheet->getSheetNames() as $index => $sheetName) {
    $spreadsheet->setActiveSheetIndex($index);
    $writer = new Csv($spreadsheet);
    $writer->save("sheet_{$index}.csv");
}

echo "All sheets converted to separate CSV files.";

Explanation:

• Iterates through each sheet in the Excel file
• Saves each sheet as a separate CSV file (sheet_0.csv, sheet_1.csv, etc.)

This is useful when Excel files contain multiple datasets in different sheets.

4. Extracting and Writing CSV Data Manually

Instead of converting an entire file, extract specific data and write it into a CSV file:

$spreadsheet = IOFactory::load('data.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
$outputFile = fopen('custom_output.csv', 'w');

foreach ($worksheet->getRowIterator() as $row) {
    $data = [];
    foreach ($row->getCellIterator() as $cell) {
        $data[] = $cell->getValue();
    }
    fputcsv($outputFile, $data);
}

fclose($outputFile);
echo "Selected Excel data successfully exported to CSV.";

Why Use This Method?

• Allows selective data extraction instead of full file conversion
• Useful when only specific columns or rows need exporting

5. Converting CSV to Excel (Reverse Process)

To convert a CSV file back to Excel (.xlsx), use:

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$inputFile = fopen('output.csv', 'r');
$row = 1;

while (($data = fgetcsv($inputFile)) !== FALSE) {
    $column = 'A';
    foreach ($data as $cellValue) {
        $sheet->setCellValue($column . $row, $cellValue);
        $column++;
    }
    $row++;
}

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('converted.xlsx');

echo "CSV file converted back to Excel.";

This ensures lossless data conversion between CSV and Excel formats.

6. Automating Excel to CSV Conversion with Cron Jobs

To automate conversion, schedule a cron job to process files periodically:

0 * * * * php /var/www/html/convert_excel_to_csv.php

This runs the conversion script every hour.

7. Downloading a CSV File in PHP

To allow users to download the converted CSV file, modify headers:

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="download.csv"');

$writer->save('php://output');

This triggers a CSV file download instead of saving it to the server.

8. Storing CSV Data in a MySQL Database

Once an Excel file is converted to CSV, import it into MySQL:

$conn = new mysqli("localhost", "root", "", "csv_database");
$file = fopen('output.csv', 'r');

while (($data = fgetcsv($file)) !== FALSE) {
    $stmt = $conn->prepare("INSERT INTO records (name, email, age) VALUES (?, ?, ?)");
    $stmt->bind_param("ssi", $data[0], $data[1], $data[2]);
    $stmt->execute();
}

fclose($file);
echo "CSV data imported into MySQL.";

This script automates the process of storing CSV data in a database.

9. Handling Large Excel Files Efficiently

For large Excel files, optimize performance using:

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('large_file.xlsx');

This prevents unnecessary memory consumption, making it efficient for large-scale data conversions.

10. Handling Different CSV Delimiters and Formats

When converting Excel to CSV, different applications may require custom delimiters.

Set Different CSV Formats

$writer->setDelimiter(";");  // Use semicolon instead of comma
$writer->setEnclosure('"');  // Wrap text in quotes
$writer->setLineEnding("\r\n"); // Use Windows line endings

This ensures CSV compatibility with various database systems.

Best Practices for Converting Excel to CSV in PHP

• Use PhpSpreadsheet’s built-in CSV writer for full file conversion
• Extract and convert specific data manually if only some rows/columns are needed
• Optimize performance by disabling unnecessary data processing
• Use correct delimiters to ensure compatibility across different software
• Automate conversion using cron jobs for scheduled file processing

Conclusion

With PhpSpreadsheet, PHP can efficiently convert Excel to CSV, making data more accessible for database imports, APIs, and system integrations.

This guide covered:

• Loading an Excel file and converting it to CSV
• Handling multiple sheets in an Excel file
• Downloading, automating, and storing CSV data
• Optimizing performance for large Excel files

By implementing these techniques, Excel-to-CSV conversion becomes seamless and efficient in PHP applications.