Skip to content

Instantly share code, notes, and snippets.

@adamcrampton
Last active January 18, 2024 03:17
Show Gist options
  • Save adamcrampton/97d6002886f6d6b167fef806238617b8 to your computer and use it in GitHub Desktop.
Save adamcrampton/97d6002886f6d6b167fef806238617b8 to your computer and use it in GitHub Desktop.
Laravel - Parse a CSV file, map to database columns and upsert
<?php
use Exception;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
final class CsvService {
/**
* CSV data from file into an array using the provided mappings, then insert to database.
*
* Mappings should be db column name => csv column #
*
* Important (but obvious): Do not pass in any null or mismatched values.
* If there is no value or null value for a column, pass it in with the array value null.
*
* Example:
*
* [
* 0 => 'stock_code',
* 1 => 'warehouse',
* 2 => 'demand',
* 3 => 'actual',
* 4 => 'date_in',
* 5 => 'quantity_in',
* 6 => 'territory_demand_1_month',
* 7 => 'actual_available',
* ]
*
* @param string $connection
* @param string $table
* @param string $path
* @param array $columnMappings
* @param array $whereMappings
* @param int $chunkSize
* @return void
*/
public static function parseAndUpsert(
string $connection = '',
string $table = '',
string $path = '',
array $columnMappings = [],
array $whereMappings = [],
int $chunkSize = 200
): void
{
try {
// Extract CSV data to array.
$data = array_map('str_getcsv', file($path));
// Format our columns against values.
$formatted = collect($data)->map(function ($csvRow) use ($columnMappings) {
$mapped = [];
for ($i = 0; $i < count($columnMappings); $i++) {
$mapped[$columnMappings[$i]] = trim($csvRow[$i]);
}
return $mapped;
});
// Run the upsert in chunks of 200.
$total = count($formatted);
$updated = $formatted->chunk($chunkSize)
->each(function ($chunk, $index) use ($connection, $table, $whereMappings, $chunkSize, $total) {
Hoblog::info('CsvService: ParseAndUpsert - Processing chunk #' . ($index + 1) . ' of ' . intval(round($total / $chunkSize)));
DB::connection($connection)
->table($table)
->upsert($chunk->toArray(), $whereMappings);
});
} catch (\Throwable $th) {
Log::channel('hoblog')
->error('Error parsing and inserting CSV in CsvService: ' . $th->getMessage());
// dd($th->__toString());
throw new Exception('Error in CsvService - see log for details');
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment