Handling data imports and exports is a fundamental task for modern web applications, but dealing with spreadsheet formats can quickly turn into a massive resource bottleneck. Many developers search for ways to php convert excel to csv because parsing flat text files like CSV is dramatically faster, more predictable, and consumes far less memory than reading heavy, XML-zipped .xlsx or legacy binary .xls files. Whether you are building an automated import pipeline, syncing inventory, or preparing data for analysis, converting your spreadsheets to a clean CSV format is an essential optimization step.

In this comprehensive guide, we will explore how to convert Excel to CSV in PHP using multiple methods. We will look at both the standard, feature-rich PhpSpreadsheet library and high-performance, stream-based alternatives like OpenSpout for handling massive datasets. Additionally, we will cover the reverse workflow, demonstrating how to convert csv to excel php to help you output beautifully formatted reports for your business users.

1. Excel vs. CSV: The PHP Developer's Performance Paradox

To understand why converting spreadsheets is such a common requirement, we first must look at the structural differences between these file formats.

An Excel file (specifically .xlsx) is not actually a single file. It is a compressed ZIP archive containing a structured hierarchy of XML files that define metadata, sheet definitions, cell styles, fonts, formulas, and string tables. When a PHP library parses an XLSX file, it has to unzip the archive, load these heavy XML documents into memory, map relationships, and hold the entire grid representation in RAM.

This is why loading a 10MB Excel file can easily consume hundreds of megabytes—or even gigabytes—of RAM in PHP. If your server is running with a standard memory limit of 128MB or 256MB, processing a large spreadsheet will instantly trigger a fatal error.

In contrast, a Comma-Separated Values (CSV) file is a raw, flat plain text file. Every line represents a row, and columns are split by a delimiter (like a comma or semicolon). CSV files do not contain formulas, charts, or cell styles. This simplicity allows PHP to stream and process CSV files row-by-row, keeping the memory footprint extremely low (often under 2MB) regardless of whether the file contains one hundred rows or one million rows.

By converting Excel to CSV, you strip away the presentation layer and archive overhead, leaving behind clean data that your backend can digest instantly.

2. Converting Excel to CSV with PhpSpreadsheet

If you are working with small-to-medium datasets (under 10,000 rows) or need to process calculations and preserve specific formulas, PhpSpreadsheet is the absolute industry standard. It is the modern successor to the deprecated PHPExcel library and is actively maintained by the community.

Step 1: Install PhpSpreadsheet

First, ensure you have Composer installed. Run the following command in your terminal to install the package:

composer require phpoffice/phpspreadsheet

Step 2: Write the Conversion Script

Here is a production-ready script that converts an Excel (.xlsx) workbook to CSV. A common mistake developers make is forgetting that an Excel file can contain multiple worksheets, whereas a CSV file can only represent a single worksheet. To avoid data loss, our script dynamically loops through each worksheet and saves it as a separate CSV file.

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

function convertExcelToCsv(string $inputFile, string $outputDir): array {
    if (!file_exists($inputFile)) {
        throw new InvalidArgumentException('Input file does not exist: ' . $inputFile);
    }


    if (!is_dir($outputDir)) {
        mkdir($outputDir, 0755, true);
    }

    // Optimize reader by ignoring styling and formatting
    $reader = IOFactory::createReaderForFile($inputFile);
    $reader->setReadDataOnly(true);

    // Load spreadsheet and retrieve sheets
    $spreadsheet = $reader->load($inputFile);
    $sheetNames = $spreadsheet->getSheetNames();
    $generatedFiles = [];

    // Initialize CSV Writer
    $writer = new Csv($spreadsheet);
    $writer->setDelimiter(',');
    $writer->setEnclosure('"');
    $writer->setLineEnding("\n");

    // Loop through each worksheet and export individually
    foreach ($sheetNames as $index => $name) {
        $writer->setSheetIndex($index);
        
        // Sanitize worksheet name for filename safety
        $safeName = preg_replace('/[^a-zA-Z0-9_\-]/', '_', $name);
        $outputFile = rtrim($outputDir, '/') . '/' . $safeName . '.csv';
        
        $writer->save($outputFile);
        $generatedFiles[] = $outputFile;
    }

    // Free memory
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);

    return $generatedFiles;
}

// Example Usage:
try {
    $files = convertExcelToCsv('data/annual_report.xlsx', 'exports/');
    echo 'Conversion successful! Output files: ' . implode(', ', $files);
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage();
}

Critical Performance Tweaks for PhpSpreadsheet

1. setReadDataOnly(true): This is crucial. It instructs the reader to completely bypass style parsing (background colors, borders, font weights, custom margins). This can decrease RAM consumption by up to 60%.
2. disconnectWorksheets(): Because PhpSpreadsheet models have bidirectional references, PHP's standard garbage collection cannot easily clean them up. Explicitly calling this function breaks those cycles to prevent memory leaks during long-running queue workers or batch scripts.

3. High-Performance Excel to CSV Conversion via OpenSpout

When your spreadsheets exceed tens of thousands of rows or cross into double-digit megabytes, PhpSpreadsheet will struggle and likely crash. For enterprise workflows or memory-constrained servers, we need a stream-based parser.

OpenSpout is a community-supported fork of the legendary Box/Spout library. It uses XMLReader and PHP streams to extract workbook data row-by-row. This means memory usage remains completely flat (typically under 10MB) whether the spreadsheet has 5,000 or 5,000,000 rows.

Step 1: Install OpenSpout

Run this Composer command to install OpenSpout:

composer require openspout/openspout

Step 2: Implement the Streaming Converter Script

In the script below, we use OpenSpout's reader to extract rows and write them directly to disk using PHP's native fputcsv function.

<?php

require 'vendor/autoload.php';

use OpenSpout\Reader\XLSX\Reader;

function streamXlsxToCsv(string $inputXlsx, string $outputCsv): void {
    if (!file_exists($inputXlsx)) {
        throw new Exception('Input file does not exist.');
    }

    $reader = new Reader();
    $reader->open($inputXlsx);

    // Open native file handle for the CSV destination
    $csvFile = fopen($outputCsv, 'w');
    if (!$csvFile) {
        throw new Exception('Failed to write CSV output.');
    }

    // Add UTF-8 BOM so Microsoft Excel loads special characters correctly
    fwrite($csvFile, "\xEF\xBB\xBF");

    // Loop through sheets
    foreach ($reader->getSheetIterator() as $sheetIndex => $sheet) {
        // CSV files only support single sheets, so we only parse sheet 1
        if ($sheetIndex !== 1) {
            continue;
        }

        foreach ($sheet->getRowIterator() as $row) {
            // OpenSpout v4 rows are wrapped in a Row object
            $cells = $row->toArray();

            // Sanitize DateTime cells
            $rowData = array_map(function ($value) {
                if ($value instanceof DateTime) {
                    return $value->format('Y-m-d H:i:s');
                }
                return $value;
            }, $cells);

            fputcsv($csvFile, $rowData);
        }
    }

    $reader->close();
    fclose($csvFile);
}

// Example usage and benchmark:
try {
    $startTime = microtime(true);
    $startMemory = memory_get_peak_usage(true);

    streamXlsxToCsv('data/massive_database.xlsx', 'exports/massive_data.csv');

    $endTime = microtime(true);
    $endMemory = memory_get_peak_usage(true);

    echo 'Conversion completed! ' . "\n";
    echo 'Execution Time: ' . round($endTime - $startTime, 2) . 's' . "\n";
    echo 'Peak Memory Usage: ' . round($endMemory / 1024 / 1024, 2) . 'MB' . "\n";
} catch (Exception $e) {
    echo 'Error during conversion: ' . $e->getMessage();
}

Why Developers Prefer OpenSpout for Large Files

Because OpenSpout reads data line-by-line, it only loads the current active row into memory. In benchmark tests, a 50MB spreadsheet containing 500,000 rows parses in roughly 15 seconds while maintaining a constant RAM usage profile of around 6.5MB. It is the ultimate tool for background jobs.

4. How to Convert CSV Back to Excel in PHP

Data parsing goes both ways. While system administrators and databases love CSVs, your non-technical stakeholders or customers will inevitably ask for Excel documents. They want the convenience of formatting, pre-styled headers, and multiple sheets. Thus, you need a robust strategy to convert csv to excel php safely.

Let's look at both a stylized approach using PhpSpreadsheet and a high-performance streaming approach using OpenSpout.

Approach A: Stylized Conversion via PhpSpreadsheet

This is best for when your final output needs to look highly professional, with proper columns sizes, colored headers, and numeric cell alignments.

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;

function convertCsvToFormattedExcel(string $inputCsv, string $outputExcel): void {
    // Configure CSV Reader
    $reader = IOFactory::createReader('Csv');
    $reader->setInputEncoding('UTF-8');
    $reader->setDelimiter(',');

    $spreadsheet = $reader->load($inputCsv);
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Formatted Data');

    $highestColumn = $sheet->getHighestColumn();
    $headerRange = 'A1:' . $highestColumn . '1';

    // Style headers for a professional appearance
    $sheet->getStyle($headerRange)->applyFromArray([
        'font' => [
            'bold' => true,
            'color' => ['rgb' => 'FFFFFF'],
            'size' => 11
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => ['rgb' => '1E3A8A']
        ]
    ]);

    // Prevent content truncation by auto-sizing all columns
    foreach (range('A', $highestColumn) as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    // Write formatted workbook to XLSX format
    $writer = new Xlsx($spreadsheet);
    $writer->save($outputExcel);

    $spreadsheet->disconnectWorksheets();
}

// Convert
convertCsvToFormattedExcel('exports/raw_data.csv', 'exports/polished_report.xlsx');

Approach B: Streaming CSV to Excel via OpenSpout

If your database dump is too large for PhpSpreadsheet but must be delivered in Excel format, you can stream the CSV rows directly into an XLSX write stream using OpenSpout.

<?php

require 'vendor/autoload.php';

use OpenSpout\Reader\CSV\Reader as CsvReader;
use OpenSpout\Writer\XLSX\Writer as XlsxWriter;

function fastCsvToExcel(string $inputCsv, string $outputXlsx): void {
    // Initialize standard CSV Reader
    $csvReader = new CsvReader();
    $csvReader->open($inputCsv);

    // Initialize high-performance XLSX Writer
    $xlsxWriter = new XlsxWriter();
    $xlsxWriter->openToFile($outputXlsx);

    foreach ($csvReader->getSheetIterator() as $sheet) {
        foreach ($sheet->getRowIterator() as $row) {
            // Write each row to Excel zip stream instantly
            $xlsxWriter->addRow($row);
        }
    }

    $csvReader->close();
    $xlsxWriter->close();
}

// Convert huge CSV output safely
fastCsvToExcel('exports/large_dump.csv', 'exports/large_dump.xlsx');

5. Troubleshooting Common Pitfalls (Dates, BOMs, and Limits)

Even with the correct libraries, spreadsheets present quirky edge cases. Below are real-world errors and how to solve them.

Excel Date Code Serialization

Excel stores dates as floating-point numbers representing the days elapsed since January 1, 1900. For instance, the date 2024-05-15 parses as the integer 45427 when loaded using raw data modes.

The Fix: When reading cells via PhpSpreadsheet, check if a cell contains an Excel date, then convert it using the built-in translation utility:

use PhpOffice\PhpSpreadsheet\Shared\Date;

if (Date::isDateTime($cell)) {
    $dateTimeObject = Date::excelToDateTimeObject($cell->getValue());
    $readableDate = $dateTimeObject->format('Y-m-d');
}

Garbled UTF-8 Characters in Microsoft Excel

You convert your file to UTF-8 CSV, but when stakeholders open it in Microsoft Excel on Windows, characters like € or accented letters (é) display as weird symbols.

The Fix: Always write a UTF-8 Byte Order Mark (BOM) to your CSV destination before writing any line of text. The BOM byte sequence is \xEF\xBB\xBF. This explicitly tells Microsoft Excel to interpret the incoming plain text file as UTF-8.

PHP Memory Exhaustion Warnings

If you receive the error Fatal error: Allowed memory size of ... bytes exhausted, it means you loaded a spreadsheet too large for PhpSpreadsheet. Swap your workflow to OpenSpout or increase your script limit:

ini_set('memory_limit', '512M');

However, increasing memory limits is a temporary bandage. Moving to Method 3 (OpenSpout streaming) is the correct architectural solution.

Frequently Asked Questions (FAQ)

Can PHP convert older .xls Excel files into CSV?

Yes. PhpSpreadsheet supports legacy binary formats (Excel 97-2003 .xls). You simply load them using the legacy reader and write them as CSV:

$reader = PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
$spreadsheet = $reader->load('old_file.xls');
$writer = new PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$writer->save('new_file.csv');

Why does my CSV file show formula strings like =A1+B1 instead of evaluated numbers?

If you loaded the spreadsheet using $reader->setReadDataOnly(true) with cached values disabled, it might read formula text. To force evaluation of formulas during the write stage, run:

$writer->setPreCalculateFormulas(true);

What is the maximum file size PHP can convert?

There is no limit when using stream-based approaches like OpenSpout. The limitation only depends on your server's timeout settings. For extremely large files, run the conversion scripts inside command-line CLI tasks, queue workers, or cron jobs instead of web requests.

Conclusion

Converting Excel to CSV in PHP does not need to crash your servers. When you need high-fidelity styling, formula calculations, and layout adjustments, PhpSpreadsheet is an incredibly powerful option. On the other hand, if your files are heavy, memory-constrained, or simply require raw processing speed, OpenSpout's stream-based parsing is the gold standard.

By selecting the optimal library and implementing date handling, proper garbage collection, and BOM signatures, you can write highly reliable spreadsheet automation scripts in PHP that scale effortlessly.