Level 50
I'd use PHP's native str_getcsv or fgetcsv to parse the file. Pass # as the separator. No 3rd party libraries needed.
Feb 3, 2024
5
Level 1
How to import CSV with custom format in Laravel?
I'm creating a CSV import feature with a format like this:
16070251039000021#16070224021150003#"JOHN DOE"#"P"#"1990-03-11"#"SUNGSANG"#"S"#"0"#"KAMPUNG BARU JELITIK SUNGAILIAT"#000#005#"KEPULAUAN BANGKA BELITUNG"#"BANGKA"#"SUNGAILIAT"#1901011001#"SUNGAILIAT"#1
From the data format above, I want to retrieve data like this: getVillageId(KEPULAUAN BANGKA BELITUNG, BANGKA BARAT, TEMPILANG, AIR LINTANG)
I have coded as below:
TpsController.php
<?php
namespace App\Http\Controllers\Admin\Master;
use App\Models\Tps;
use App\Imports\TpsImport;
use Illuminate\Support\Str;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use Maatwebsite\Excel\Facades\Excel;
use Yajra\DataTables\Facades\DataTables;
use Illuminate\Support\Facades\Validator;
class TpsController extends Controller
{
public function import(Request $request)
{
DB::beginTransaction();
try {
$validator = Validator::make($request->all(), [
'file' => 'required|mimetypes:text/plain,text/csv',
],[
'file.required' => 'The file field is required.',
'file.mimes' => 'The file must be a file of type: csv.',
]);
if ($validator->fails()) {
return response()->json([
'success' => false,
'message' => $validator->messages()->all()[0]
], 422);
}
Excel::import(new TpsImport, $request->file('file'), null);
DB::commit();
return response()->json([
'success' => true,
'message' => 'Data imported successfully'
], 200);
} catch (\Throwable $th) {
DB::rollBack();
return response()->json([
'success' => false,
'message' => $th->getMessage()
], 422);
}
}
}
TpsImport.php
<?php
namespace App\Imports;
use App\Models\Tps;
use App\Traits\Location;
use Maatwebsite\Excel\Row;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
class TpsImport implements OnEachRow, WithBatchInserts, WithCustomCsvSettings
{
use Location;
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function onRow(Row $row)
{
//Format data: 16070251039000021#190501113101120003#"JOHN DOE"#"L"#"1984-04-04"#"TEMPILANG"#"S"#"0"#"JALAN CEMPAKA"#003#005#"KEPULAUAN BANGKA BELITUNG"#"BANGKA BARAT"#"TEMPILANG"#1905052007#"AIR LINTANG"#5
$row = array_map(function($item) {
return str_replace('"', '', $item);
}, $row->toArray());
//getVillageId(KEPULAUAN BANGKA BELITUNG, BANGKA BARAT, TEMPILANG, AIR LINTANG);
$area_id = $this->getVillageId($row[13], $row[14], $row[15], $row[17]);
//check if data already exists
$tps = Tps::where([
'area_id' => $area_id,
'name' => $row[18],
])->first();
if ($tps) {
return;
}
$tps = Tps::create([
'area_id' => $area_id,
'name' => $row[18],
]);
}
public function batchSize(): int
{
return 500;
}
public function getCsvSettings(): array
{
return [
'delimiter' => "#"
];
}
}
Location.php
<?php
namespace App\Traits;
trait Location
{
public function getVillageId($province = null, $city = null, $district = null, $village = null)
{
$village = \App\Models\Village::where([
'province_name' => $province,
'city_name' => $city,
'district_name' => $district,
'name' => $village,
])->first();
if ($village) {
return $village->id;
}
return null;
}
}
When I run it, I get an error message like this: Undefined array key 13
How to create a CSV import feature with a special format?
Level 1
@JussiMannisto Do you have an example of how to implement it? I want to import a large CSV.
Level 50
@hilmi Check the links that I provided. They show you how to use the functions and even provide examples. Anyway, here's an example on how to parse the CSV using fgetcsv:
$file = fopen('path-to-the-file', 'r');
while(($columns = fgetcsv($file, null, '#')) !== false) {
//TODO: Do what you need with $columns.
}
Or you can use the SplFileObject methods through the file object:
$file = $request->file('file')->openFile();
while(($columns = $file->fgetcsv('#')) !== false) {
//TODO: Do what you need with $columns.
}
You said you're importing a large CSV. I don't know what that means, but I'll assume it's 100k+ lines. Parsing a file like this takes no time, but storing the data to DB is another thing. Doing that during the request might not be feasible. Also, inserting everything in a single huge transaction like you did is asking for trouble. You should look into doing this in a background job instead, and inserting the data in smaller transactions so the table doesn't get locked for a long duration.
Level 73
I created a package for converting CSV file into Laravel Seeder files, you can take a look there to see how I did it.
Level 122
this code
$area_id = $this->getVillageId($row[13], $row[14], $row[15], $row[17]);
does not allow for a row not having these values. This could be the first row, the last row of the file or just some inconsistent row in the middle
You need to code for rubbish data quality and decide how you want to handle it
Please or to participate in this conversation.