Etl Adapter Csv Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require flow-php/etl-adapter-csv
   

   Ensure flow-php/etl is also installed (required dependency).

2. Basic Usage

   use Flow\ETL\Adapter\CSV\CSVAdapter;
   use Flow\ETL\Reader\ReaderInterface;
   
   // Create a CSV reader
   $reader = new CSVAdapter();
   $reader->setFilePath('/path/to/your/file.csv');
   
   // Read rows
   foreach ($reader as $row) {
       print_r($row); // Each row is an associative array
   }
   

3. First Use Case: Simple Data Import

   $reader = new CSVAdapter();
   $reader->setFilePath(storage_path('import/users.csv'));
   $reader->setDelimiter(';'); // Custom delimiter if needed
   
   foreach ($reader as $userData) {
       User::create([
           'name' => $userData['name'],
           'email' => $userData['email'],
       ]);
   }
   

Key Classes to Know

• CSVAdapter: Main adapter class for reading/writing CSV.
• CSVReader: For reading CSV files (extends ReaderInterface).
• CSVWriter: For writing CSV files (if needed).

Implementation Patterns

Common Workflows

1. Reading CSV with Headers

$reader = new CSVAdapter();
$reader->setFilePath('data.csv');
$reader->setHeaderRow(0); // Use first row as headers

foreach ($reader as $row) {
    // $row['column_name'] instead of $row[0]
}

2. Chunked Processing (Memory Efficiency)

$reader = new CSVAdapter();
$reader->setFilePath('large_file.csv');
$reader->setChunkSize(1000); // Process 1000 rows at a time

foreach ($reader as $chunk) {
    foreach ($chunk as $row) {
        // Process row
    }
}

3. Writing CSV Data

use Flow\ETL\Writer\CSVWriter;

$writer = new CSVWriter();
$writer->setFilePath(storage_path('export/users.csv'));
$writer->setDelimiter(';');

$writer->writeHeader(['id', 'name', 'email']);
$writer->writeRow([1, 'John Doe', 'john@example.com']);

4. Integration with Laravel Queues

// Dispatch a job for each CSV row
$reader = new CSVAdapter();
$reader->setFilePath('data.csv');

foreach ($reader as $row) {
    ProcessUser::dispatch($row);
}

5. Validation and Transformation

$reader = new CSVAdapter();
$reader->setFilePath('data.csv');
$reader->setHeaderRow(0);

foreach ($reader as $row) {
    $validated = validator()->validate([
        'email' => $row['email'] ?? '',
        'name' => $row['name'] ?? '',
    ], [
        'email' => 'required|email',
        'name' => 'required|string',
    ]);

    // Proceed with valid data
}

Integration Tips

Laravel Service Provider

// app/Providers/ETLServiceProvider.php
public function register()
{
    $this->app->singleton(ReaderInterface::class, function ($app) {
        $reader = new CSVAdapter();
        $reader->setFilePath(config('etl.csv_path'));
        return $reader;
    });
}

Configurable Delimiters

// config/etl.php
'csv' => [
    'delimiter' => ';',
    'enclosure' => '"',
    'escape' => '\\',
],

$reader = new CSVAdapter();
$reader->setDelimiter(config('etl.csv.delimiter'));

Error Handling

try {
    $reader = new CSVAdapter();
    $reader->setFilePath('missing.csv');
    foreach ($reader as $row) {
        // ...
    }
} catch (\Flow\ETL\Exception\FileNotFoundException $e) {
    Log::error('CSV file not found: ' . $e->getMessage());
}

Gotchas and Tips

Pitfalls

1. Memory Issues with Large Files

   • Always use setChunkSize() for large CSV files to avoid memory exhaustion.
   • Example: $reader->setChunkSize(500);

2. Header Row Misconfiguration

   • If setHeaderRow() is not set, the adapter assumes no headers, and columns are accessed numerically ($row[0], $row[1]).
   • Fix: Always explicitly set headers if your CSV has them:

     $reader->setHeaderRow(0); // First row is headers
     

3. Encoding Problems

   • CSV files may use different encodings (e.g., UTF-8, ISO-8859-1). Use setEncoding():

     $reader->setEncoding('UTF-8');
     

   • If not set, the system default encoding is used.

4. Line Endings (\n vs \r\n)

   • The adapter automatically handles both, but malformed line endings can cause parsing issues.
   • Debug Tip: Log raw lines if parsing fails:

     $reader->setRawMode(true); // Access raw lines if needed
     

5. Empty or Malformed Rows

   • Skip empty rows or handle them explicitly:

     foreach ($reader as $row) {
         if (empty(array_filter($row))) {
             continue; // Skip empty rows
         }
     }
     

Debugging Tips

1. Inspect Raw Data

   $reader = new CSVAdapter();
   $reader->setFilePath('data.csv');
   $reader->setRawMode(true); // Access raw lines
   
   foreach ($reader as $rawLine) {
       logger()->debug('Raw line: ' . $rawLine);
   }
   

2. Validate CSV Structure

   • Use a tool like CSVLint to validate your CSV before processing.

3. Log Adapter Configuration

   $reader = new CSVAdapter();
   $reader->setFilePath('data.csv');
   $reader->setDelimiter(';');
   $reader->setHeaderRow(0);
   
   logger()->debug('CSV Adapter Config:', [
       'delimiter' => $reader->getDelimiter(),
       'header_row' => $reader->getHeaderRow(),
       'encoding' => $reader->getEncoding(),
   ]);
   

Extension Points

1. Custom Row Processing

   • Extend CSVAdapter to add preprocessing:

     class CustomCSVAdapter extends CSVAdapter
     {
         public function processRow($row)
         {
             // Transform or validate row before yielding
             return $this->transformRow($row);
         }
     
         protected function transformRow($row)
         {
             // Custom logic
             $row['processed_at'] = now()->toDateTimeString();
             return $row;
         }
     }
     

2. Custom Writers

   • Extend CSVWriter to add features like:
     • Automatic column ordering.
     • Custom escaping logic.
     • Batch writing for performance.

3. Event Dispatching

   • Trigger events before/after row processing:

     $reader = new CSVAdapter();
     $reader->setFilePath('data.csv');
     $reader->on('row', function ($row) {
         event(new CSVRowProcessed($row));
     });
     

4. Integration with Laravel Events

   // Listen for CSV row events
   event(new CSVRowProcessed($row));
   
   // In EventServiceProvider
   protected $listen = [
       CSVRowProcessed::class => [
          HandleCSVRow::class,
       ],
   ];
   

Performance Tips

1. Stream Large Files

   • Use PHP streams to avoid loading the entire file into memory:

     $reader = new CSVAdapter();
     $reader->setFilePath('huge_file.csv');
     $reader->setStream(true); // Stream rows one by one
     

2. Disable Unused Features

   • If you don’t need headers or chunking, skip setting them to reduce overhead.

3. Batch Database Inserts

   • Use Laravel’s DB::insert with batch inserts for better performance:

     $batch = [];
     foreach ($reader as $row) {
         $batch[] = [
             'name' => $row['name'],
             'email' => $row['email'],
         ];
     
         if (count($batch) >= 1000) {
             DB::table('users')->insert($batch);
             $batch = [];
         }
     }
     // Insert remaining rows
     if (!empty($batch)) {
         DB::table('