Creating Task to export data from CSV into database using sfCsvPlugin

This article has moved to this location.


When we are working with symfony, we always related to symfony tasks. Tasks such clearing cache, creating models, creating forms, creating filters, generating application, deploying project are the most often used tasks when we are developing an application with symfony framework. What the Task itself? A task is a piece of code that is run from the command line using the symfony php script at the root of your project. For more details, you can read here.
In this article, i’ll share my “Task” that i’ve made a few months ago, when i have to transfer all data in csv files into database, with the help of sfCsvPlugin as csv reader.
To make it general, the csv file should have a header that represent the table or schema fields name. Below is the example of csv format with ‘|’ as delimiter and ‘”‘ as character enclosing :

"name"|"address"|"phone_number" ==> csv header
"nibsirahsieu"|"Klampis Ngasem 16.A"|"0319105xxxx" ==> csv data

The task have several arguments and options.
Arguments :

  1. filepath : the path of csv file to read
  2. model : class model that handle data saving

Options :

  1. delimiter : Character delimiting fields
  2. enclosure : Character enclosing fields
  3. connection : database connection

and below is the full source code.

class csvTodbTask extends sfBaseTask
{
  protected function configure()
  {
    $this->addArguments(array(
      new sfCommandArgument('filepath', sfCommandArgument::REQUIRED, 'Path to the file to read'),
      new sfCommandArgument('model', sfCommandArgument::REQUIRED, 'class model'),
    ));
		
    $this->addOptions(array(
	new sfCommandOption('delimiter', null, sfCommandOption::PARAMETER_REQUIRED, 'Character delimiting fields', '|'),
	new sfCommandOption('enclosure', null, sfCommandOption::PARAMETER_REQUIRED, 'Character enclosing fields', '"'),
	new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'connection name', 'propel'),
    ));
    
    $this->namespace        = 'csv';
    $this->name             = 'todb';
    $this->briefDescription = 'export data from formatted csv into database';
    $this->detailedDescription = <<<EOF
Task [csv:todb|INFO] is a task to export data from formatted csv into database.
  [./symfony csv:todb /path/to/filename Author --delimiter='|' --enclosure='"' --connection=propel|INFO]
  The header of csv (first row) should represent the table or schema fields name. For example : name|address|phone_number
EOF;
  }

  protected function execute($arguments = array(), $options = array())
  {
    $databaseManager = new sfDatabaseManager($this->configuration);
    $connection = $databaseManager->getDatabase($options['connection'] ? $options['connection'] : null)->getConnection();
    $delimiter = $options['delimiter'] ? $options['delimiter'] : '|'; 
    $enclosure = $options['enclosure'] ? $options['enclosure'] : '"';
    $model = $arguments['model'];
    $model_peer = constant($model.'::PEER');
    $first = true;
    $headers = array();
    
    $reader = new sfCsvReader($arguments['filepath'], $delimiter, $enclosure);
    $reader->open();
    try
    {
    	$this->logSection('csv', 'Transfering...');
    	$connection->beginTransaction();
    	while($data = $reader->read())
    	{
    		if ($first)
    		{
    			$first = false;
    			foreach ($data as $v)
    			{
    				$headers[] = 'set'.call_user_func(array($model_peer, 'translateFieldName'), $v, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_PHPNAME); 
    			}
    		}
    		else
    		{
    			$obj = new $model;
    			foreach ($headers as $key=>$v)
    			{
    				$obj->$v($data[$key]);
    			}
    			$obj->save($connection);
    			$this->logSection('csv', 'Inserting id = '.$obj->getPrimaryKey());
    		}
    	}
    	$connection->commit();
    	$this->logSection('csv', 'Done.');
    }
    catch (Exception $e)
    {
    	$connection->rollback();
    	throw $e;
    }
  }
}

I hope this usefull. Thank you for visiting my blog.
Let’s rock the world with symfony đŸ˜€

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: