I'm trying to walk through as CSV file and extract all its contents.
After the contents are imported I need to insert them into my mysql database table but before I can do that I need to check if these records exist. If a record does not, import this register.
I've got headers and all content and I had created a arrays with this information, but I don't know if it's 100% correct. This is my actual code:
function csv_content_parser($content) {
foreach (explode("\n", $content) as $line) {
// Generator saves state and can be resumed when the next value is required.
yield str_getcsv($line);
}
}
$archivo = fopen($route, "r");
// Get content from csv file.
$content = file_get_contents($adjunto);
// Create arrays from csv file's lines.
$data = array();
$headers = array();
$rows = array();
//get all content
foreach ($this->csv_content_parser($content) as $fields) {
array_push($data, $fields);
}
//get headers
foreach($data[0] as $dat){
array_push($headers, $dat);
}
//get all content of csv without headers
for($i=1; $i<count($data); $i++){
array_push($rows, $data[$i]);
}
//close the file
fclose($archivo);
The content of the csv file (it's an example file) is for example (I need to create a generic importer):
Array
(
[0] => Array
(
[0] => ggggg@gmail.com
[1] => david
[2] => 005
[3] => hola
[4] => eee
[5] => eee
)
[1] => Array
(
[0] => ggggg@gmail.com
[1] => david
[2] => 005
[3] => hola
[4] => eee
[5] => eee
)
)
And my headers:
Array
(
[0] => Email
[1] => Name
[2] => Identification
[3] => Note
[4] => Field Label 1
[5] => Field Label 2
)
My question is: Is this a good solution or is there any better solution to do this? I need to insert this data into my database. How would can to do this?
updated:
model
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Listado extends Model
{
protected $table = 'listado';
protected $fillable = [
'nomape', 'direccion', 'provincia', 'ciudad', 'cp', 'telefono', 'movil', 'id_teleoperadora'
];
/**
* The attributes that should be hidden for arrays.
*
* @var array
*/
protected $hidden = [];
public function scopeTodos( $query )
{
return $query->orderBy('nomape', 'ASC')->orderBy('telefono', 'ASC');
}
public function scopeSinAsignar( $query, $id_llamadas )
{
$query->whereIn('id', $id_llamadas)->whereNull('id_teleoperadora');
}
public static function Filtrado($request)
{
$query = self::query();
if($request['direccion']) {
$query->where('direccion', 'like', '%' .$request['direccion']. '%');
}
if($request['ciudad']) {
$query->where('ciudad', $request['ciudad']);
}
if($request['cp']) {
$query->where('cp', $request['cp']);
}
/*if($request['teleoperadora']) {
$query->where('id_teleoperadora', $request['teleoperadora']);
}*/
return $query;
}
public function scopeConEstado( $query, $nombreEstado )
{
return $query->whereHas('llamada.estado', function ($query) use ($nombreEstado) {
$query->whereNombre($nombreEstado);
})->orWhereDoesntHave('llamada');
}
public function scopeConEstados( $query, $nombresEstado )
{
return $query->whereHas('llamada.estado', function ($query) use ($nombresEstado) {
$query->whereIn('nombre', $nombresEstado);
})->orWhereDoesntHave('llamada');
}
public function llamada()
{
return $this->hasOne('App\Llamada', 'id_listado', 'id')->latest();
}
public function llamada_test()
{
return $this->hasOne('App\Llamada', 'id', 'id_listado');
}
/**
* RETURN OPERATOR
*/
public function teleoperadora()
{
return $this->hasOne('App\User', 'id', 'id_teleoperadora')->withDefault(['nombre' => 'NINGUNA']);
}
/**
* RETURN CALL DATA
*/
public function callStatusName()
{
return $this->hasOne('App\llamada', 'id_listado', 'id');
}
}
update 2
all my function code
function csv_content_parser($content) {
foreach (explode("\n", $content) as $line) {
// Generator saves state and can be resumed when the next value is required.
yield str_getcsv($line);
}
}
public function uploadListing(Request $request)
{
$adjunto = $request->file('attached');
$route = "";
if(isset($adjunto)){
$name = $adjunto->getClientOriginalName();
$result = $adjunto->storeAs('importaciones', $name, 's4');
$route = public_path('storage/importaciones/'.$name);
}else{
return "Error al adjuntar recibo de envío, consulte al administrador del sistema";
}
//Abrimos nuestro archivo
$archivo = fopen($route, "r");
// Get content from csv file.
$content = file_get_contents($adjunto);
// Create arrays from csv file's lines.
$data = array();
$headers = array();
$rows = array();
//get all content
foreach ($this->csv_content_parser($content) as $fields) {
array_push($data, $fields);
}
//get headers
foreach($data[0] as $dat){
array_push($headers, $dat);
}
//get all content of csv without headers
for($i=1; $i<count($data); $i++){
array_push($rows, $data[$i]);
}
Listado::insertOrIgnore(array_map(function($row) {
return array_combine($headers, $row);
}, $rows));
//Cerramos el archivo
fclose($archivo);
}
updated 3
function csv_content_parser($content) {
foreach (explode("\n", $content) as $line) {
// Generator saves state and can be resumed when the next value is required.
yield str_getcsv($line);
}
}
public function uploadListing(Request $request)
{
$adjunto = $request->file('attached');
$route = "";
if(isset($adjunto)){
$name = $adjunto->getClientOriginalName();
$result = $adjunto->storeAs('importaciones', $name, 's4');
$route = public_path('storage/importaciones/'.$name);
}else{
return "Error al adjuntar recibo de envío, consulte al administrador del sistema";
}
//Abrimos nuestro archivo
$archivo = fopen($route, "r");
// Get content from csv file.
$content = file_get_contents($adjunto);
// Create arrays from csv file's lines.
$data = array();
$headers = array();
$rows = array();
//get all content
foreach ($this->csv_content_parser($content) as $fields) {
array_push($data, $fields);
}
//get headers
foreach($data[0] as $dat){
array_push($headers, $dat);
}
//get all content of csv without headers
for($i=1; $i<count($data); $i++){
array_push($rows, $data[$i]);
}
Listado::insertOrIgnore(array_map(function($row) use($headers) {
return array_combine($headers, $row);
}, $rows));
//Cerramos el archivo
fclose($archivo);
}
As per docs:
DB::table('listado')->insertOrIgnore(array_map(fn($row) => array_combine($headers, $row), $rows));
Since you have Eloquent models, you can use Eloquent's insert() method:
Listado::insertOrIgnore(array_map(fn($row) => array_combine($headers, $row), $rows));
Since you said the following
I need to check if these records exist. If a record does not, import this register.
I chose the method insertOrIgnore() but maybe you want something else. Luckily, Laravel comes with a bunch of similar methods for slightly different use cases.
Prior to PHP 7.4, array_map(fn($row) => array_combine($headers, $row), $rows) has to be:
array_map(function($row) use($headers) {
return array_combine($headers, $row);
}, $rows)