Import CSV and insert data in different tables via "listCSV" plugin

[Gruber]

Hey there!
I'm playing around with the CSV import of Fabrik. I was wondering how to deal with a CSV file with multiple data from the same row that should be applied to different tables. Since I'm working on a Book Archive project, I'll receive a CSV file with single book data per row like title, description, author, genres, and so on... I found that Fabrik has the nice "listCSV" plugin that allow to execute PHP on every single row data import, exactly what I need!

This way I can do a specific database operation with each row so that the author data from the row can be inserted in the author table, the genres in the Many - to Many relationship table and so on...

... but how can I retrieve the data from the row when it's imported (or better before it's imported)? and store it in an object/array?

Unfortunately I can't find much about this in the docs of the plugin, then I noted in the code comments on the page:

[...]get a reference to the form model - as we are importing line by line, the form model contains the current data that we are going to submit to the table.

So I think that basically I can use much of the functionality of the PHP form plugin...
if that's the case, can I just skip the database query part in my script and simply set direclty each row data field inside the corresponding fields of my form? (this because I've set up my form to interact with multiple tables in my database). Can I do this also with joined list form fields?

 

[cheesegrits]

Did you look at the example code in the listcsv/scripts folder?

There's a couple. One simple one (import_cities.php) which shows you how to just modify the row data being imported. One more complex one which creates users based on the data.

You should be able to do everything you need in listcsv.

-- hugh

 

[Gruber]

Hey Hugh!
Thanks for the tip! I see that basically is like using the php form plugin as far as logic goes, more or less, that's great! Tho I can't really get how the record from the CSV rows are then matched to the form. I have a header row in the CSV, but the headings are't match the current name of the elements in my form.
I've setup my script this way just to test things and make a dump

<?php
//ensure the file will only be called from within Joomla
defined('_JEXEC') or die();
// get a reference to the form model - as we are importing line by line, the form model contains the current
// data that we are going to submit to the table.
$listModel = $this->getModel();
$formModel = $listModel->getFormModel();

$data = $formModel->formData;
dump($data, 'data');

return false;

The dump is returning my data in the form fields "shifted" in position. I guess it skipped flat out the joined list fields of the form and It skipped also a "fileupload" element, these elements are almost at the top of the form.
How is data from the CSV rows inserted inside the form fields? Also how come with a return false statement the script is not interrupted and the import is completed?

 

[cheesegrits]

To answer your last question first, you can't "return false" from an included file. The PHP require or include statements don't return a value. So at the moment, you can only return false if you use the inline code box, where we eval() your code, and you can return a value. Returning false from the "before row is processed" code should skip that row, but it won't halt the import.

In other plugins that have both an inline code and a file option, we work round this by actually using them both. So if there is a file, we "require" that, then if there is inline PHP we run that. That way, you can do something like ...

if (!function_exists('myFunc') {
   function myFunc() {
      // do stuff
      return $success;
   }
}

... and then in the inline code, you can do ...

return myFunc();

However ... for whatever reason, when I wrote the listcsv plugin, a long time ago, I didn't do that, and it just runs one or the other (with inline code taking precedence). And very few people use it, and even fewer need to return false to skip a line. So the issue has never really come up. I was thinking about this today when testing listcsv for someone else. I think I'll go ahead and modify it so it does that. I don't think that will cause any backward compat issues for existing usage.

On to your first question ... I'm not sure why it is skipping a column. It shouldn't. At the point your code is called, there is no concept of a "CSV row", we've already read the CSV row and processed it into the $formModel->formData array, just before we call the main form model processToDB. Pretty much equivalent to a PHP form submission script running onBeforeProcess. We've also (in theory) added any published elements from the form which don't exist in the CSV, and set them to their default value.

Ah, hang on ...

I have a header row in the CSV, but the headings are't match the current name of the elements in my form.

That won't work. Your header row MUST match your element names, otherwise we have no clue what to do with that column. You can choose which format of name - short name (just the field name), long name (full element name, tablename___element), or label, in the CSV settings (under Publishing, in List settings). I usually prefer to use long (full) names, as that removes any ambiguity.

As for joined data ... in theory we can handle that, if the CSV is formatted exactly right (best way to check is to export some test data, and see how it is set up). But that code is extremely convoluted, somewhat fragile, and I personally haven't tested it for a long time. CSV data just is not conducive to relational management. Hence spreadsheets vs databases.

So ... try again, and make sure that your header row matches exactly the existing elements.

-- hugh[/quote]

 

[Gruber]

Thanks Hugh! I realized later the return false thing with the include, silly me; for the stop form execution was mostly due to the assumption of the same mechanics of the PHP form plugin, in the docs I found this

Stopping form submission

// The error message 'woops' will be assinged to the element 'tablename_elementname'
$formModel->errors['tablename___elementname'][] = 'woops!';
return false;

I don't really need a stop form submission, it was just because I'm lazy and I don't want to clear the records imported for every test so don't bother with non necessary work, thank you!

I thought that the wrong name were tripping the whole process, but since a couple of fields where correctly matched I thought something else was going on.. I didn't know that also the label was good to match for the import.

I'm wondering tho, if I have a field with some separated words, I explode them and get them as separate data, how can I fetch them to elements of a repeatable group (you know the ominous genres repeat group for the m-to-m) ?

FWIW the import session is something I will take care off, and I think it should be more of a 1 time process, not something I will be asked to do often. So I was thinking if it could more sensed to just create an empty dummy list/table with only the default ID and DATE element, import the CSV and then let Fabrik create every element. After that I will simply grab each data from the form and do my own business with queries directly aimed to the tables I'm interested in. You think I can get in any trouble with this?

 

[cheesegrits]

OK, in this commit:

https://github.com/Fabrik/fabrik/commit/8d9f1be944c7854212aacd0649798b2297aba466

I've done what I mentioned in my last post. I've also added an onStartImportCSV hook, so you can define a class in a file there, then call it from your row code. And there's a $this->userClass variable you can instantiate your class into, so you can access it across row calls.

Here's a quick demo. Very simple, I'm just importing a two field CSV of US states, with 'state' (name) and 'state_code' (2 letter abbreviation), and this code turns the state name into upper case, then outputs a J! info msg of "YAY!" when complete.

http://screencast.com/t/DMcuzyMCoq

So the "Before Import" file is ...

<?php
/**
* Created by PhpStorm.
* User: Hugh
* Date: 2/11/2016
* Time: 11:56 PM
*/

class importStates
{
    function importStateRow($importModel)
    {
        $listModel = $importModel->getModel();
        $formModel = $listModel->getFormModel();

        $state = $formModel->formData['us_states_import___state'];
        $formModel->updateFormData('us_states_import___state', strtoupper($state));

        return true;
    }

    function afterImportStateRow($importModel)
    {

    }

    function importStateComplete($importModel)
    {
        JFactory::getApplication()->enqueueMessage('YAY!');
    }
}

... and in the inline code for the "Before Import" I instantiate that into ...

$this->userClass = new importStates();

... then in the "Import Row" inline code, I do ...

$this->userClass->importStateRow($this);

... and in the After Import inline code ...

$this->userClass->importStateComplete($this);

I think that makes things a lot easier and more efficient, if you need to do more than a few lines of coding.

 

[cheesegrits]

I'm wondering tho, if I have a field with some separated words, I explode them and get them as separate data, how can I fetch them to elements of a repeatable group (you know the ominous genres repeat group for the m-to-m) ?

OK, for that, you'll need to add something to the "After Import Row".

I extended my example above, to add this to the class ...

class importStates
{
    function _construct($importModel)
    {
        $importModel->userData = array();
    }

    function importStateRow($importModel)
    {
        $listModel = $importModel->getModel();
        $formModel = $listModel->getFormModel();

        $state = $formModel->formData['us_states_import___state'];
        $formModel->updateFormData('us_states_import___state', strtoupper($state));

        return true;
    }

    function afterImportStateRow($importModel)
    {
        $listModel = $importModel->getModel();
        $formModel = $listModel->getFormModel();
        $insertId = $formModel->getInsertId();
        $importModel->userData[] = $insertId;
    }

    function importStateComplete($importModel)
    {
        JFactory::getApplication()->enqueueMessage('YAY!  Import IDs: ' . implode(',', $importModel->userData));
    }
}

... and in my "Before import" I now do ...

$this->userClass = new importStates($this);

.. which inits the $importModel->userDatat to be an array.

The important part is the afterImportStateRow() bit where is does the $formModel->getInsertId(), which gets you the PK id of the row just inserted. Then you can use that to insert your related data.

What you would need to do is, in import row function, bust up your "words" into an array and stick it in $importModel->userData.

Then in the after row code, you then have the insert ID, and the words in $importModel->userData. You can then do the usual $db stuff to create the rows in the related table.

-- hugh[/quote][/quote]

 

[Gruber]

Oh wow, thanks a lot! This looks pretty useful, I have to read this few times but I think I got the general sense of it, really appreciate your effort thank you!