Please upload this excel file to the database

An occasional request to a website I maintain at a non-for-profit organization is to upload records to a MySQL database. The data is given to me as an attachment in an Excel file. These are the steps that get the task done in the fastest time.

  1. Make sure that I get the file to my Gmail account (either directly, or I just forward it from my .edu email to my personal Gmail).
  2. Click on the attachment (in Gmail), not the download, but the “Edit with Google Sheets” button.
  3. As needed, “fix” data, such as date values, and remove formatting (if any).
    1. Arrange the columns to match those of the table into which you want to load the records to.
  4. Download (from Google Sheets) as tab-separated values. “File” -> “Download as” -> “Tab-separated values (.tsv, current sheet)”
    • The benefits are:
      • not having to open Excel locally at all (or OpenOffice, LibreOffice)
      • not having to deal with windows/unix line endings upon exporting from your favorite (local) spreadsheet
  5. Open the terminal (ideally OSX, or Linux, but cywin gets the job done too under Windows).
  6. Get rid of the first line, which usually is the header line.
  7. “Copy” the file to the linux box where MySQL is located (such as via scp, or sftp).
  8. Login to the linux box where MySQL is located.
  9. Backup the database (and copy the backup to another linux box before the next step)
  10. Login to MySQL, and use the command LOAD DATA INFILE to load the data
    • Example: Load data infile ‘/tmp/strains.txt’ into table strains;
    • Pay attention to the message indicating how many records were inserted. It should match the number of records that you expected to insert.
    • Pay attention to the other messages such as “Warnings”. When “Warnings” is not a zero, then do a command: “Show warnings;” to help you identify what the problem was. It could be that lines are truncated because the field in the table was too small, or that you don’t have a value in a column that does not accept nulls.
  11. Check your data. Then tell your boss (or yourself) that the task is “done”.
  12. Disadvantages of this approach:
    • Your google-sheets will be cluttered with the opened excel files. (Not easy to get rid of those things, I’ve tried and just decided not to bother; if needed, just rename the file to something that starts with “z_useless_” so that it is listed alphabetically at the end, and you can remember that you don’t need it).
    • You may have to read the additional options of the LOAD DATA command, when you have columns that have values that depend on other fields. However, MySQL is quite powerful, with functions for strings, and simple arithmetic.

Mock testing in Zend with Doctrine: mimicking the mocking

A few notes about mock testing in Zend with Doctrine.

There is great material already about this topic (such as this, this, and this).

  • The goal is mocking the retrieval of the entity manager to avoid making a mess with the existing database.

“unable to create an instance for doctrine.entity_manager.orm_default”

The Bootstrap.php may be a place to fix the error “unable to create an instance for doctrine.entity_manager.orm_default” via adding the following line. It seems to “force” the service manager to initialize the instance.


However, a nice solution was to create the service via a constructor that receives both the service manger, and the entity manager. The service is not created in the controller. Instead, it should be placed in the Module.php file for the module. The service will then almost-never need to be created via ‘new’. This is how it looks like in Module.php:

public function getServiceConfig()
   return array(
      'factories' => array(
         'BagelService' =>  function($sm)
            $em = $sm->get('doctrine.entitymanager.orm_default');
            return new BagelService($sm, $em);

This facilitates mocking the entity manager. However, I did not follow that route.

Minor note: in our setup there is something not 100% perfect, which requires me to utilize ‘doctrine.entitymanager.orm_default’, which does not have an underscore between the words entity and manager.

My setup was to initialize the service passing only the service manager to its constructor. It is normally initialized inside of a controller. There is nothing about the service in the Module.php file.

Example of test setup and actual test.

Initialization in the setUp function is done through the Bootstrap class.

protected function setUp()
   $this->serviceManager = Bootstrap::getServiceManager();
   $this->bagelService = new BagelService($this->serviceManager);

Example test:

public function testBagelPromotion()
   $response = $this->bagelService->getPromotion();
   $this->assertEquals('Buy 1 bagel, get free coffee', $response);

In some tests, I’ve grouped several asserts together using an array as follows:

public function testObjectInitiallyEmptyValues()
    $fields = ['Items', 'Payments', 'Attachments'];
    $obj = new EntityABCDEF();
    foreach($fields as $each) {
        $this->assertCount(0, $obj->{'get' . $each }(),
           "'$each' should initially have zero elements.");

Example test mocking the entity manager:

public function testGetBagelById()
   // Create a dummy object
   $object = new BagelEntity();

   // Mock the entity manager
   $emMock = $this->getMock('EntityManager',
      ['getRepository', 'getClassMetadata', 'persist', 'flush', 'find'], [], '', false);

   // Create a mocked service, only mocking one method
   $mockedService = $this->getMockBuilder(BagelService::class)

   // setup the expectation of the method we are mocking

   // 'find' should return our dummy object
   $emMock->expects( $this->any() )

   // Call the mocked service requesting an object
   $response = $mockedService->getBagelById( 12 );

   // Check the response is the correct type of entity
   $this->assertInstanceOf(BagelEntity::class, $response);

   // Check that the returned entity is our dummy object
   $this->assertEquals('BAM12345', $response->getBagelNote());

There were tests where I did not utilize the mock entity manager because the functionality being tested did not require it. Examples: testing that requesting an entity by ID with an invalid ID returns null, and testing that QueryBuilder was generating a query correctly.

Why was all of this helpful?

  • I found out that we have functions that are repeated in other classes. My suggestion to the team lead (when she comes back from vacation) will be to have such functions moved to a Utility class.
  • I had to move some strings constants as actual defined constants in order to test the output of a few methods to avoid having the test-case break in case that our customer wants us to change something such as a description of an error message.