Jump to content


Funtion to implement import of xml or csv datasheet

import xml csv datasheet funtion implement

  • Please log in to reply
8 replies to this topic

#1 Juan Manuel Pedro Villalba

Juan Manuel Pedro Villalba

    Newbie

  • Members
  • Pip
  • 3 posts
  • LocationPosadas 3300, Misiones, Argentina

Posted 29 October 2014 - 02:26 PM

Hello i need know if it posible solution Funtion to implement import of xml or csv datasheet in gregorycrud. Regards for Posadas, Misiones, Argentina.:D



#2 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 29 October 2014 - 02:57 PM

There is no inbuilt code for the same ... you have to write on the same.

What you can do is .. use the following

 

$crud->set_field_upload('file_url','assets/uploads/files');

 

$crud->callback_after_insert(array($this, 'import_uploaded_file'));

 

function log_user_after_update($post_array,$primary_key)
{

       //here is where u can put your code to do the import. of XML / CSV into grocery crud.

       return true;
}



#3 Juan Manuel Pedro Villalba

Juan Manuel Pedro Villalba

    Newbie

  • Members
  • Pip
  • 3 posts
  • LocationPosadas 3300, Misiones, Argentina

Posted 29 October 2014 - 04:18 PM

Woww, is very fast respond. Thanks  B)

Exelent information



#4 Juan Manuel Pedro Villalba

Juan Manuel Pedro Villalba

    Newbie

  • Members
  • Pip
  • 3 posts
  • LocationPosadas 3300, Misiones, Argentina

Posted 29 October 2014 - 04:24 PM

I think I am fairly newbie, eh loaded the code at the same example.php controller with this result: D

 

 

 

Parse error: syntax error, unexpected T_VARIABLE, expecting T_FUNCTION in C:\Program Files\Ampps\www\CI\application\controllers\examples.php on line 248



#5 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 30 October 2014 - 06:37 AM

well that must be some code issues.. without access to the code it will be difficult to give any kind of comment over it.. but truely speaking its more to do with syntax error.. If you can use eclipse / netbeaks as editor.. it will show you where your error is... with that u can resolve it..

even as mentioned.. if you go on the mentioned line, the error will be somewhere nearby to the same.



#6 marcelo066

marcelo066

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 15 October 2015 - 07:15 PM

Almost a year later, ouh yeah, i restart this thread;

 

i do the code above, using PHPExcel;

class Arquivosimportado extends Admin_Controller {

    /**
     * Arquivosimportado Management page (e.g. CRUD operations)
     */
    public function index() {
        $this->load->library('excel');
        $crud = $this->crud->generate_crud('arquivosimportados');
        $crud->set_field_upload('descricao', 'assets/uploads/files');
        $crud->callback_after_upload(array($this, 'import_uploaded_file'));
        $crud->set_subject('Arquivo');
        $this->mViewData['crud_data'] = $this->crud->render();
        $this->render('crud');
    }

    function import_uploaded_file($uploader_response,$field_info, $files_to_upload) {
        

        $objPHPExcel = PHPExcel_IOFactory::load($field_info->upload_path.'/'.$uploader_response[0]->name);

        $myArray = array();
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $worksheetTitle = $worksheet->getTitle();
            $highestRow = $worksheet->getHighestRow(); // e.g. 10
            $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $nrColumns = ord($highestColumn) - 64;

            for ($row = 1; $row <= $highestRow; ++$row) {

                for ($col = 0; $col < $highestColumnIndex; ++$col) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
                    $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);

                    $myArray[] = $val;
                }
                $this->db->insert('participantetemp',$myArray);
            }

        }

        return true;
    }

}

the above code is working ok, but the question is :

- if i have more then 10.000 lines on my Excel worksheet, i do 10.000 or more "$this->db->insert" ,  so, theres another way with a better way to do this, with  a better perfomance????



#7 titu

titu

    Advanced Member

  • Members
  • PipPipPip
  • 43 posts

Posted 15 October 2015 - 09:41 PM

This is more related with codeigniter, anyway, you could use insert_batch.

 

First of all, are you sure the example is working? shuldn you re-initialize $myArray[] ?

 

If not all cells of all rows are being loaded into the array.

 

However, using insert_batch instead of insert you will have a significant performance improvement, I think something like this will work:

            for ($row = 1; $row <= $highestRow; ++$row) {
 
                for ($col = 0; $col < $highestColumnIndex; ++$col) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
                    $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
 
                    $myArray[] = $val;
                }
                $batch[] = $myArray;
            }

            $this->db->insert_batch('participantetemp',$batch);


#8 marcelo066

marcelo066

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 19 October 2015 - 08:40 PM

thanks titu

i did some improvements to realli works better , check out the code above

 function import_uploaded_file($uploader_response, $field_info, $files_to_upload) {

        $objPHPExcel = PHPExcel_IOFactory::load($field_info->upload_path . '/' . $uploader_response[0]->name);
        $arrayKeys =array('nome','data','cpf','condicao','segmento');
        $combineArray =array();
        $myArray = array();
        $batch = array();

        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $worksheetTitle = $worksheet->getTitle();
            $highestRow = $worksheet->getHighestRow(); // e.g. 10
            $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $nrColumns = ord($highestColumn) - 64;

            for ($row = 1; $row <= $highestRow; ++$row) {

                for ($col = 0; $col < $highestColumnIndex; ++$col) {
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
//                    $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
                    $myArray[] = $val;
                }
             
//the array was formed like (1,2,3,4), so, i need a associative one with the name from the
// database fields to the right insert_batch so use array combine to generate the
//array with my needs

                $combineArray = array_combine($arrayKeys, $myArray);
                $batch[] = $combineArray;
                $myArray = array();
                $combineArray = array();
            }
        }
        $this->db->insert_batch('participantetemp', $batch);
        return true;
    }

see ya;



#9 titu

titu

    Advanced Member

  • Members
  • PipPipPip
  • 43 posts

Posted 20 October 2015 - 02:41 PM

any benchmarking? how much did performance improved?







Also tagged with one or more of these keywords: import, xml, csv, datasheet, funtion, implement

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users