Sheet-Bridge

A reusable PHP library for importing CSV, XLS, XLSX, and ODS spreadsheet rows into an application-owned database table. It uses PDO, prepared statements, database transactions, and a persistent idempotency key so the same request cannot insert its rows twice.

Requirements

• PHP 8.5
• Composer
• A PDO driver for the target database
• A database table for imported rows and the idempotency table described below

Install the package dependencies:

composer install

The importer works with PDO drivers such as MySQL (pdo_mysql), SQL Server (pdo_sqlsrv), PostgreSQL (pdo_pgsql), and SQLite (pdo_sqlite). It does not create or alter the destination schema.

Database setup

Create an idempotency table in the same database used by the importer. The idempotency_key must be a primary key or have a unique constraint so concurrent retries are safe.

CREATE TABLE upload_idempotency (
    idempotency_key VARCHAR(255) PRIMARY KEY,
    request_hash CHAR(64) NOT NULL,
    rows_imported INTEGER NOT NULL
);

Create and manage the destination table in your application. For example:

CREATE TABLE loan_rows (
    registration VARCHAR(100) NOT NULL,
    amount DECIMAL(12, 2) NOT NULL
);

The same idempotency key and same file/mapping returns the original row count without inserting again. Reusing the key with a different file, destination table, column mapping, or header option throws IdempotencyConflictException. Keep idempotency records for at least as long as clients may retry requests.

Use in any PDO application

<?php

require __DIR__ . '/vendor/autoload.php';

use SheetBridge\Uploads\SpreadsheetImporter;

$pdo = new PDO(
    'mysql:host=localhost;dbname=app;charset=utf8mb4',
    'app_user',
    'secret',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$importer = new SpreadsheetImporter($pdo);
$result = $importer->import(
    '/path/to/upload.csv',
    'loan_rows',
    ['registration', 'amount'],
    $requestIdempotencyKey,
    hasHeaderRow: true
);

echo $result->alreadyProcessed
    ? 'Already imported ' . $result->rowsImported . ' rows.'
    : 'Imported ' . $result->rowsImported . ' rows.';

Column mapping is positional: the first spreadsheet column maps to the first configured database column. Only the active worksheet is imported. Empty rows are skipped; set hasHeaderRow when the first row contains headings. Database table and column names must be simple identifiers and are validated before use. File contents and values are parameterized; only identifiers are incorporated into SQL after validation.

SpreadsheetImporter accepts a readable local file path whose name ends in .csv, .xls, .xlsx, or .ods. It can be called from a framework/controller without using PHP upload globals.

For HTTP uploads, SheetBridge\Uploads\UploadController accepts the matching $_FILES entry, applies a 10 MB limit, and delegates to the importer:

use SheetBridge\Uploads\UploadController;

$uploads = new UploadController($pdo);
$result = $uploads->upload(
    $_FILES['spreadsheet'],
    'loan_rows',
    ['registration', 'amount'],
    $_SERVER['HTTP_IDEMPOTENCY_KEY'],
    hasHeaderRow: true
);

Generate and persist a unique idempotency key per client request. Do not generate a new key when retrying the same request.

Demo page

The included index.php demonstrates an eight-column loan import. Configure its PDO connection with environment variables:

• UPLOAD_DB_DSN — required PDO DSN (for example mysql:host=localhost;dbname=app;charset=utf8mb4)
• UPLOAD_DB_USER
• UPLOAD_DB_PASSWORD
• UPLOAD_TARGET_TABLE — optional; defaults to loan_imports

The configured destination table must have the columns RegNo, MemberName, Deductions, Savings, LoanRepayment, LoanInterest, Devlevy, and TransactionDate. The form includes an optional checkbox for skipping the first row when it contains headings. Configure the database and create both tables before serving the demo.

Tests

Run the PHPUnit tests with:

composer test

The tests use SQLite in memory and cover row mapping, header/blank-row handling, idempotent retries, conflicting keys, transaction rollback, and identifier validation.