15

I am working on a module that requires access to some tabular data provided from an external source. It's only two columns but has roughly 40000 rows.

Currently, my module is just parsing the CSV in entirety when necessary. This works well, but since the file is about 450Kb, this will eat up server resources once deployed to a production site.

I'd like to move this data to a Magento table and am having trouble.

I'm using the RDBMS methods in my setup script, ala:

$installer = $this;
$installer->startSetup();

$table = $installer->getConnection()
    ->newTable($installer->getTable('my_table'))
    ->addColumn('column_a', Varien_Db_Ddl_Table::TYPE_TEXT, 5, array(
        'nullable' => false,
        ), 'Column A')
    ->addColumn('column_b', Varien_Db_Ddl_Table::TYPE_TEXT, 3, array(
        'nullable' => false,
        ), 'Column B');

$installer->getConnection()->createTable($table);

$installer->endSetup();

This works just fine to create my table, but actually getting my data in is wracking my head (I'm still a noob when it comes to database management).

Ideally, I would just parse my CSV file and insert values into the table without having to deal with a bunch of copy/paste.

Are there any built-in methods for handling arbitrary CSV data, or are they all built to handle just what they need to? What would be the best approach to get my data into Magento?

15

Never done this. Let's play!

After the createTable() call, or in a subsequent script:

/* @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;
$installer->startSetup();

//Extract data from CSV file
$csv = new Varien_File_Csv;
$data = $csv->getData('path/to/file.csv');

$resultNum = $installer->getConnection()->insertArray(
    $installer->getTable({TABLE NAME}),
    array({COL 1},{COL 2}),    //column names
    $data
);

//EDIT: if this is done using a data setup script, can even log the results:
Mage::log(
    __FILE__." added $resultNum records to $installer->getTable({TABLE NAME})",
    Zend_Log::INFO,
    "setup.log",
    true
);

$installer->endSetup();
  • wow I didn't expect, that this might be so easy! :) Hope this works – Fabian Blechschmidt Jan 26 '13 at 13:00
  • Adjusted to show how to log results, which requires data setup script. – benmarks Jan 26 '13 at 15:53
  • Varien_File_Csv, I knew it had to be there somewhere. I'll give this a shot and update with results. – pspahn Jan 26 '13 at 18:56
  • So brilliantly simple. You just made my Saturday. Thanks @benmarks. – pspahn Jan 26 '13 at 19:41
  • 2
    Also worth noting here is that in my situation, Magento was running out of memory when I added additional columns. Two columns of data was fine (just barely) and as soon as I added a third column and ran the upgrade script, Magento would just fail with a white page and no error messages/logs. I was forced to split my CSV into multiple files to get it to work. – pspahn Jan 28 '13 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.