derafu / etl
Derafu: ETL - From Spreadsheets to Databases Seamlessly
Requires
- php: ^8.3
- derafu/spreadsheet: dev-main
- derafu/translation: dev-main
- doctrine/dbal: ^4.2
- phpoffice/phpspreadsheet: ^4.1
- symfony/console: ^7.2
Requires (Dev)
- ext-xdebug: *
- derafu/foundation: dev-main
- friendsofphp/php-cs-fixer: ^3.63
- phpstan/phpstan: ^1.12
- phpunit/phpunit: ^11.4
Suggests
- derafu/spreadsheet: Required for working with spreadsheets as sources or targets.
- doctrine/dbal: Required for working with databases using Doctrine DBAL.
- phpoffice/phpspreadsheet: Required for working with spreadsheets in XLSX, XLSX or ODS formats.
- symfony/console: Required for the command line interface (eg. derafu:etl).
- symfony/yaml: Required for working with spreadsheets in YAML format.
This package is auto-updated.
Last update: 2025-03-21 01:09:11 UTC
README
A PHP package that transforms spreadsheet data into database structures and content with minimal effort.
Overview
Derafu ETL provides a streamlined solution for converting data between spreadsheets and databases. With a clean, fluent API, it simplifies complex data integration tasks through a pipeline architecture.
$pipeline = new Pipeline(); $result = $pipeline ->extract('data.xlsx') // Extract data from a spreadsheet. ->transform($rules) // Apply transformations rules (optional). ->load('database.sqlite') // Load into a database. ->execute() ;
Key Features
{.list-unstyled}
- 📤 Extract data from various sources (XLSX, ODS, CSV, databases).
- 🔄 Transform data with customizable rules.
- 📥 Load data into different target systems.
- 🔁 Bidirectional conversion between spreadsheets and databases.
- 🏗️ Schema management with automatic table creation and structure updates.
- 📊 Data visualization capabilities with schema export to Markdown, D2, and more.
- 🧩 Extensible architecture for custom source and target systems.
Installation
Install via Composer:
composer require derafu/etl
Quick Start
Command Line
The quickest way to use Derafu ETL is through the command line:
php app/console.php derafu:etl data.xlsx database.sqlite
This extracts data from data.xlsx
and loads it into a new SQLite database on database.sqlite
.
Example
Run the example used in tests with:
php app/console.php derafu:etl tests/fixtures/spreadsheet-data.xlsx
This will create a spreadsheet-data.sqlite
in the current directory.
PHP Code
use Derafu\ETL\Pipeline\Pipeline; $pipeline = new Pipeline(); $result = $pipeline ->extract('data.xlsx') // Load data from a XLSX. ->transform() // This will use default transformations. ->load([ // You can specify the configuration for Doctrine. 'doctrine' => [ 'driver' => 'pdo_sqlite', 'path' => 'database.sqlite', ] ]) ->execute(); // This is will run the process. echo "Rows loaded: " . $result->rowsLoaded();
Understanding ETL Pipelines
An ETL pipeline consists of three main steps:
- Extract: Read data from a source (e.g., spreadsheet).
- Transform: Apply rules and transformations to the data.
- Load: Write the transformed data to a target (e.g., database).
Derafu ETL provides a clean interface for each step while handling the complex details behind the scenes.
More than just move data to a target
Export Database Schema to Markdown
use Derafu\ETL\Database\DatabaseManager; use Derafu\ETL\Schema\Target\MarkdownSchemaTarget; $manager = new DatabaseManager(); $database = $manager->connect('database.sqlite'); $target = new MarkdownSchemaTarget(); $markdown = $target->applySchema($database->schema()); file_put_contents('schema.md', $markdown);
Generate Database Diagram
use Derafu\ETL\Database\DatabaseManager; use Derafu\ETL\Schema\Target\D2SchemaTarget; $manager = new DatabaseManager(); $database = $manager->connect('database.sqlite'); $target = new D2SchemaTarget(); $d2 = $target->applySchema($database->schema()); file_put_contents('schema.d2', $d2);
Contributing
Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.
License
This package is open-sourced software licensed under the MIT license.