Yii Framework Forum

[Resolved] Merge two files csv


(Jiuly Rojas) #1

Create a method or function, which given 2 paths of two csv files, unify them in a single file. The
headers (we assume that the header is always in the first row) that are in common in
both files must be unified and only be present once in the file returned

file 1

Header_1,Header_2,Header_3,Header_4
1,2,3,4
4,5,6,7

file 2

Header_5,Header_2,Header_6,Header_7
8,9,10,11
12,13,14,15

output

Header_1,Header_2,Header_3,Header_4,Header_5,Header_6,Header_7
1,2,3,4,,,
4,5,6,7,,,
,9,,,8,10,11
,13,,,12,14,15

(jomonjohnson) #2

read csv files as array
do merging operations in those arrays
then convert array to csv


(Softark) #3

/* Moved from Yii 2 Gereral Discussions to General PHP */

An interesting quiz. :thinking:

This is my answer:

// read CSV 1
$content1 = [];
$file1 = new SplFileObject($csv_file_path1); 
$file1->setFlags(SplFileObject::READ_CSV); 
foreach ($file1 as $line) {
    $content1[] = $line;
}
$header1 = array_shift($content1);

// read CSV 2
$content2 = [];
$file2 = new SplFileObject($csv_file_path2); 
$file2->setFlags(SplFileObject::READ_CSV); 
foreach ($file2 as $line) {
    $content2[] = $line;
}
$header2 = array_shift($content2);

// make header row

// header item array : key = header item, value = csv2 info
$header = [];

foreach($header1 as $item) {
    $header[$item] = false; // assumes it doesn't exist in csv2
}

$i = 0;
foreach($header2 as $item) {
    $header[$item] = $i;  // the item exists in $i column of csv2
    $i++;
}

$csv2only = count($header) - count($header1);

// make CSV content

$content = [];

// from csv 1
foreach($content1 as $line1) {
    $line = $line1;
    // appends csv 2 specific values
    for($i = 0; $i < $scv2only; $i++) {
        $line[] = null;
    }
    $content[] = $line;
}

// from csv 2
foreach($content2 as $line2) {
    $line = [];
    foreach($header as $item => $column) {
        if ($column === false) {
            $line[] = null;
        } else {
            $line[] = $line2[$column];
        }
    }
    $content[] = $line;
}

// add the header to the csv content
array_unshift($content, array_keys($header));

(Jiuly Rojas) #4

Error @softark

Error: Class ‘app\controllers\SplFileObject’ not found in C:\wamp\www\qashops\controllers\CsVController.php:62 Stack trace: #0 [internal function]: app\controllers\CsvController->actionUpload() #1 C:\wamp\www\qashops\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array) #2 C:\wamp\www\qashops\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array) #3 C:\wamp\www\qashops\vendor\yiisoft\yii2\base\Module.php(528): yii\base\Controller->runAction(‘upload’, Array) #4 C:\wamp\www\qashops\vendor\yiisoft\yii2\web\Application.php(103): yii\base\Module->runAction(‘csv/upload’, Array) #5 C:\wamp\www\qashops\vendor\yiisoft\yii2\base\Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request)) #6 C:\wamp\www\qashops\web\index.php(12): yii\base\Application->run() #7 {main}

Copy StacktraceSearch StackoverflowSearch GoogleException

Error

Class ‘app\controllers\SplFileObject’ not found


(jomonjohnson) #5
$file1 = new \SplFileObject($csv_file_path1); 

(Softark) #6

@jomonjohnson Thanks!


(Jiuly Rojas) #7

thanks @jomonjohnson @softark

now how output $content to CSV ?

or output file CSV

Header_1,Header_2,Header_3,Header_4,Header_5,Header_6,Header_7
1,2,3,4,,,
4,5,6,7,,,
,9,,,8,10,11
,13,,,12,14,15

I use CsvGrid of yii2tech but i dont know how make it

My code of view

<?php
/**
 * vista resultado despues de recibir el xml y llama a la funcion de yii2tech
 * para exportar a csv
 * 
 */

use yii\helpers\Html;
use yii\bootstrap\ActiveForm;
use yii2tech\csvgrid\CsvGrid;
use yii\data\ArrayDataProvider;

$this->title = 'output CSV';
$this->params['breadcrumbs'][] = $this->title;

?>
<div>
    <h1><?= Html::encode($this->title) ?></h1>
  
    <?php
    /**
     *En el arreglo de allModels se colocan los datos hijos de los nodos del xml 
     *En columns la cabecera de como quedaria
     * 
     */
    $exporter = new CsvGrid([
    'dataProvider' => new ArrayDataProvider([
       'allModels' => 
        [
                'here may is content'
        ],
       
    ]),
    'columns' => [
        $content[0][0].$content[0][1]
    ],
     'csvFileConfig' => [
        'cellDelimiter' => ";",
        'rowDelimiter' => "\n",
        'enclosure' => '',
    ],
]);
  
/**
 * Para exportar el archivo en formato csv y mostrar un boton si el cliente desea abrirlo
 *     
 */
if ($exporter->export()->saveAs('../files/output.csv')){
    echo  '<div class="alert alert-success">
            El archivo <strong>output.csv</strong> se ha cargado con exito.
        </div>';
   echo Html::a('Abrir archivo', ['../files/output.csv'], ['class' => 'btn btn-default']);
}
?>
</div>

(Jiuly Rojas) #8

the array that i have

Array
(
    [0] => Array
        (
            [0] => Header_1;Header_2;Header_3;Header_4;
            [1] => Header_5;Header_6;Header_7;
        )

[1] => Array
    (
        [0] => 1;2;3;4;
        [1] => 
    )

[2] => Array
    (
        [0] => 4;5;6;7
        [1] => 
    )

[3] => Array
    (
        [0] => 
        [1] => 8;9;10;11;
    )

[4] => Array
    (
        [0] => 
        [1] => 12;13;14;15
    )

)

the array that i want

Array
    (
	 [0] => Array
            (
                [0] => Header_1;Header_2;Header_3;Header_4;
                [1] => Header_5;Header_6;Header_7;
            )

    [1] => Array
        (
            [Header_1] => 1
            [Header_2] => 2
			[Header_3] => 3
			[Header_4] => 4
				
        )

    [2] => Array
        (
            [Header_1] => 4
            [Header_2] => 5
			[Header_3] => 6
			[Header_4] => 7
        )

    [3] => Array
        (
            [Header_5] => 8
            [Header_2] => 9
			[Header_6] => 10
			[Header_7] => 11
        )

    [4] => Array
       (
            [Header_5] => 12
            [Header_2] => 13
			[Header_6] => 14
			[Header_7] => 15
        )

 )

or a procedure or function that export to csv


(Softark) #9

Please check the PHP manual.
SplFileObject::fputcsv
http://php.net/manual/en/splfileobject.fputcsv.php

As for CsvGrid, I don’t know how to use it.

the array that i have

... (A)

the array that i want

... (B)

You should have made a mistake to describe them upside down. I mean (B) is the current format of your array and (A) is what you want.

But still this conversion is not the same with what you asked at the first post of this topic.

IMO (A) and (B) are in confused messy formats. I don’t like them. (B) may be working for you at the moment just to display the grid on the screen, but you’d be better reconsider those formats. (A) won’t work for CsvGrid to save the csv in the format you want.


(Jiuly Rojas) #10

Ready . Working perfect

Model UploadForm.php

<?php

namespace app\models;
use yii\base\Model;

/**
* UploadForm es el modelo que controla como cargar archivos.
*/
class UploadForm extends Model
{

public $file;
public $file2;

/**
 * @return arreglo de reglas.
 */
public function rules()
{
    /*
     * checkExtensionByMimeType=>false permite reconocer archivos csv para la carga
     * solo para el scenario csvfile
     */
    return [
        [['file'], 'file', 'skipOnEmpty' => false,'extensions' => 'xml','on'=>'xmlfile'], // escenario para el segundo ejercicio
        [['file'], 'file', 'skipOnEmpty' => false,'extensions' => ['xls', 'csv'], 'checkExtensionByMimeType'=>false,'on'=>'csvfile'], // escenario para el tercer ejercicio
        [['file2'], 'file', 'skipOnEmpty' => false,'extensions' => ['xls', 'csv'], 'checkExtensionByMimeType'=>false,'on'=>'csvfile'], // escenario para el tercer ejercicio

    ];
}

/**
 * @return arreglo de etiquetas.
 */
public function attributeLabels()
    {
        return [
            'file' => 'Archivo',
            'file2' => 'Archivo 2',
        ];
    }

}

Controller CsVController.php

<?php
/**
 * Controlador para dado dos archivos unificarlos en uno solo
*/
namespace app\controllers;

use Yii;
use yii\web\Controller;
use yii\filters\AccessControl;
use yii\filters\VerbFilter;
use app\models\UploadForm;
use yii\web\UploadedFile;

class CsvController extends Controller
{
    /**
     * {@inheritdoc}
     */
    public function behaviors()
    {
        return [
            'access' => [
                'class' => AccessControl::className(),
                'rules' => [
                    [
                        'actions' => ['upload','view'],
                        'allow' => true,
                        'roles' => ['@'],
                    ],
                ],
            ],
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'logout' => ['post'],
                ],
            ],
        ];
    }

/**
* Funcion para cargar y convertir archivo xml a csv
*/
public function actionUpload()
{
    $model = new UploadForm();
    $model->scenario = 'csvfile';

    if (Yii::$app->request->isPost) {
        $model->file = UploadedFile::getInstance($model, 'file'); // carga archivo
        $model->file2 = UploadedFile::getInstance($model, 'file2'); // carga archivo 2

        if ($model->validate()) {   
            $csv_file_path1='../files/' . $model->file->baseName . '.' . $model->file->extension;
            $csv_file_path2='../files/' . $model->file2->baseName . '.' . $model->file2->extension;
            $model->file->saveAs($csv_file_path1); // guarda el archivo
            $model->file2->saveAs($csv_file_path2); // guarda el archivo 2
         
        // Lee CSV 1 y crea el primer contenido del csv    
        $content1=$this->readFile($csv_file_path1);
//        elimina la cabecera
        $header1 = array_shift($content1);
       
        // Lee CSV 2 y crea el segundo contenido del csv
        $content2 = $this->readFile($csv_file_path2);
//        elimina la cabecera
        $header2 = array_shift($content2);

        // une ambos header en uno solo
        $header = array_unique(array_merge($header1,$header2) );

        // fusiona el contenido de los csv
        $content = array_merge ($content1,$content2);

        return $this->render('view',['content' => $content,'header'=>$header]);
        
        }
    }

    return $this->render('upload', ['model' => $model]);
}
/**
 * Ver el csv
 */
public function actionView()
    {

        return $this->render('view');
    }

/**
 * funcion propia de php.net para cambiar los indices en un array
 */    
function change_index(&$tableau, $old_key, $new_key) {
    $changed = FALSE;    $temp = 0;
    
    foreach ($tableau as $key => $value) {
        switch ($changed) {
            case FALSE :
                //creates the new key and deletes the old
                if ($key == $old_key) {
                    $tableau[$new_key] = $tableau[$old_key];
                    unset($tableau[$old_key]);
                    $changed = TRUE;
                }
                break;

            case TRUE :
                //moves following keys
                if ($key != $new_key){
                $temp= $tableau[$key];
                unset($tableau[$key]);
                $tableau[$key] = $temp;
                break;
                }  else {$changed = FALSE;} //stop
        }
    }
    array_values($tableau); //free_memory
}
/**
 * funcion para leer el archivo csv y adecuarlo al yii2tech
 */  

function readFile($file){
    
        $content = [];
        $file = new \SplFileObject($file); 
        $file->setFlags(\SplFileObject::READ_CSV); // lee csv
        foreach ($file as $line) {
            $content[] = $line;
        }
        $count= count($content); // cuenta las filas
        $countValues = count($content[0]); //cuenta las columnas
        $i=0;
        for ($i=0;$i<$countValues;$i++){

            for ($j=1;$j<$count;$j++){
                $this->change_index ($content[$j], $i, $content[0][$i]); // cambia los indices
            }
        }
    return $content;
}
}

on Views

View upload.php

<?php

/* 
 * Vista para cargar los archivos csv
 */

use yii\widgets\ActiveForm;
use yii\helpers\Html;

$this->title = 'Merge de dos ficheros CSV';
$this->params['breadcrumbs'][] = $this->title;

?>
    <h1 class="box-title"><?= Html::encode($this->title) ?></h1>
    
<div class="row">
    <div class="col-lg-12">
        <div class="alert alert-warning">
            El nombre del archivo fusionado sera <strong>output.csv</strong><br/>
        </div>
        
        <?php 
        $form = ActiveForm::begin(['options' => ['enctype' => 'multipart/form-data']]); ?>

        <?= $form->field($model, 'file')->fileInput() ?>

        <?= $form->field($model, 'file2')->fileInput() ?>

        <div class="form-group">
            <?= Html::submitButton('Enviar', ['class' => 'btn btn-primary', 'name' => 'enviar']) ?>
        </div>

        <?php ActiveForm::end(); ?>

    </div>  
</div>  

view view.php

<?php
/**
 * vista resultado despues de recibir los archivos csv y fusionarlo en uno solo
 * para exportar a csv usando la libreria yii2tech
 * 
 */

use yii\helpers\Html;
use yii2tech\csvgrid\CsvGrid;
use yii\data\ArrayDataProvider;

$this->title = 'output CSV';
$this->params['breadcrumbs'][] = $this->title;

?>
<div>
    <h1><?= Html::encode($this->title) ?></h1>
  
    <?php
    /**
     *En el arreglo de allModels se colocan el contenido del resultado de los csv
     *En columns el header del csv
     * 
     */
    $exporter = new CsvGrid([
    'dataProvider' => new ArrayDataProvider([
       'allModels' => $content,
    ]),
    'columns' => 
       $header,
    
     'csvFileConfig' => [
        'cellDelimiter' => ";",
        'rowDelimiter' => "\n",
        'enclosure' => '',
    ],
]);
  
/**
 * Para exportar el archivo en formato csv y mostrar un boton si el cliente desea abrirlo
 *     
 */
if ($exporter->export()->saveAs('../files/output.csv')){
    echo  '<div class="alert alert-success">
            El archivo <strong>output.csv</strong> se ha cargado con exito.
        </div>';
    
   echo Html::a('Abrir archivo', ['../files/output.csv'], ['class' => 'btn btn-default']);
}
?>
</div>

I use yii2tech for export the csv

Thanks !!


(Softark) #11

But I don’t believe it’s working perfect. While you are making header items unique, you are not doing the corresponding operation on the content. Just check your output.

[EDIT]
You are just doing this:

file 1
> Header_1,Header_2,Header_3,Header_4
> 1,2,3,4
> 4,5,6,7

file 2
> Header_5,Header_2,Header_6,Header_7
> 8,9,10,11
> 12,13,14,15

output
> Header_1,Header_2,Header_3,Header_4,Header_5,Header_6,Header_7
> 1,2,3,4
> 4,5,6,7
> 8,9,10,11
> 12,13,14,15

[EDIT 2]
Ah, I got it. I had overlooked your readFile and changeIndex method. Your code should work as expected. Congrats!


(Jiuly Rojas) #12

I uploaded the project on https://github.com/jiuly256/qashoptest

Thanks you for helpme! Greetings