Plugin: CSVGrab

I’ve modified your code a bit (removed some extra parameters and changed the order) plus added the trace parameter. Try running this and tell me what’s output/tripping up:

{exp:csvgrab url="./files/quote.csv"
weblog="2"
delimiter="TAB"
encloser=""
skip="1"
title="22"
use="8"
fields="artnrfabrikant"
trace="true"
}

I’ve used CSVGrab countless times and have seen that Checking, Checking…error. In all of my cases, it’s because the plug-in can’t find the CSV file to read.

That outputs the following code:

TRACE: Array
(
    [title] => Array
        (
            [is_custom] => 0
            [field] => 22
        )

    [date] => Array
        (
            [is_custom] => 0
            [field] => 
        )

    [artnrfabrikant] => Array
        (
            [is_custom] => 1
            [field] => 8
            [id] => 14
            [format] => none
        )

    [obr] => Array
        (
            [id] => 4
            [format] => none
        )

    [artikelnummer] => Array
        (
            [id] => 5
            [format] => none
        )

    [staffelminimum] => Array
        (
            [id] => 6
            [format] => none
        )

    [ontvangstdatum] => Array
        (
            [id] => 7
            [format] => none
        )

    [prijs_inclBTW] => Array
        (
            [id] => 8
            [format] => none
        )

    [combi_omschrijving] => Array
        (
            [id] => 9
            [format] => none
        )

    [unspsc] => Array
        (
            [id] => 10
            [format] => none
        )

    [prijsverkoop] => Array
        (
            [id] => 11
            [format] => none
        )

    [ondergroep] => Array
        (
            [id] => 12
            [format] => none
        )

    [groepomschrijving] => Array
        (
            [id] => 13
            [format] => none
        )

    [verpakkingseenheid] => Array
        (
            [id] => 15
            [format] => none
        )

    [EAN] => Array
        (
            [id] => 16
            [format] => none
        )

    [productgroepnr] => Array
        (
            [id] => 17
            [format] => none
        )

    [merk] => Array
        (
            [id] => 18
            [format] => none
        )

    [voorraad] => Array
        (
            [id] => 19
            [format] => none
        )

    [URLfoto] => Array
        (
            [id] => 20
            [format] => none
        )

    [prijsverkoop_inclBTW] => Array
        (
            [id] => 21
            [format] => none
        )

    [prijsdealer] => Array
        (
            [id] => 22
            [format] => none
        )

    [hoofdgroep] => Array
        (
            [id] => 23
            [format] => none
        )

    [afbeelding] => Array
        (
            [id] => 24
            [format] => none
        )

    [artikelomschrijving] => Array
        (
            [id] => 25
            [format] => none
        )

    [prijsdealer2] => Array
        (
            [id] => 26
            [format] => none
        )

    [prijs2_inclBTW] => Array
        (
            [id] => 27
            [format] => none
        )

    [staffel2] => Array
        (
            [id] => 28
            [format] => br
        )

)

TRACE: Resource id #55

And noting is added.

I’m sorry for asking so much about this module, but it’s error reporting is limited, so i cant debug myself that good. 😊

I’ve figured it out. It seems to have to do with the enclosure.

after replacing

while (($item = fgetcsv($handle, 10000, $this->delimiter, $this->encloser)) !== FALSE)

with

while (($item = fgetcsv($handle, 10000, $this->delimiter)) !== FALSE)

everything works like a charm!

I’ve modified the source code a bit to create a category tree instead of categories. I assumed the following: “parent/child/subchild/etc/eds”

change the foreachloop

foreach ($names as $name)
{
....
}

to

$parent_id = 0;
foreach ($names as $name)
{
    
    $name = trim($name);
    $query = $DB->query("SELECT cat_id, parent_id
            FROM exp_categories
            WHERE exp_categories.cat_url_title = '".$DB->escape_str($name)."'
            AND exp_categories.parent_id = '".$parent_id."'
            AND exp_categories.group_id = '".$DB->escape_str($this->cat_group)."'");

    if ($query->num_rows == 0)
    {

        // Create primary category
        $insert_array = array ('group_id'=>$this->cat_group,
        'cat_name'=>$name,
        'cat_url_title'=>$name,
        'cat_image'=>'',
        'parent_id'=>$parent_id
        );

        print "Try to add category ".$name."";

        if ($this->runsql)
        {

            $DB->query($DB->insert_string('exp_categories', $insert_array));
            $cat_ids[] = $DB->insert_id;
            $parent_id = $DB->insert_id;
            
            //Fixed a bug, else it does not display a category tree in your template.
            $cat_field_data = array(
            'cat_id' => $parent_id,
            'group_id' => $this->cat_group
            );
            $DB->query($DB->insert_string('exp_category_field_data', $cat_field_data));


        } else
        {

            print ($DB->insert_string('exp_categories', $insert_array));
            $cat_ids[] = 0;

        }
        
    } else
    {

        print "Category ".$name." exists";
        $cat_ids[] = $query->row['cat_id'];
        
        $parent_id =  $query->row['cat_id'];
        
    }

}

Now, all of your added products have a category tree instead of a flat categories.

Edit: Fixed a bug where categories weren’t displaying on the frontpage. The categories data wasn’t inserted in all the tables.

Absolutely fantastic plugin,

Will truly save me tens of hours of entry, but one odd issue I’ve run into is dates are being entered a whole day behind the date listed in the csv file. I’ve run the import with trace on, and it appears they’re initially entering properly, but showing up in the entry wrong. Thanks for any help you can provide.

Great Plugin Andrew, I just like to make a feature request for CSVGrab to support FieldFrame Matrix. As of now, importing data is requiring data to be parsed using serialize() in PHP. For example to get this

'test post'|'Lorem ipsum dolor sit amet'|'a:2:{i:0;a:2:{i:1;s:22:"Image - Soaring_Higher";i:2;a:1:{s:9:"file_name";s:18:"Soaring_Higher.jpg";}}i:1;a:2:{i:1;s:29:"Image - Orca_Whale_Underwater";i:2;a:1:{s:9:"file_name";s:25:"Orca_Whale_Underwater.jpg";}}}'

I have to run a similar code e.g.

<h1>partial data</h1>
<?
$array_1 = array(
    array('1' => 'Image - Soaring_Higher', array('file_name' => 'Soaring_Higher.jpg')), 
    array('1' => 'Image - Orca_Whale_Underwater', array('file_name' => 'Orca_Whale_Underwater.jpg')),
);

echo serialize($array_1) . '
';
echo '<pre>';
print_r ($array_1);
echo '</pre>’;
?>

</pre>

i’m not sure i follow this - i’d like to use csvgrab to insert a FF field (specifically, sc_countries_select). i’m not sure where you’re running this php? thanks for any advice on how i could do this.

eta: i went ahead and un-FF’ed this field as i really needed it working on import than anything else and the field didn’t have that many options (for now). but i’d still like to know how to go about doing this for future ref!

Absolutely fantastic plugin, Will truly save me tens of hours of entry, but one odd issue I’ve run into is dates are being entered a whole day behind the date listed in the csv file. I’ve run the import with trace on, and it appears they’re initially entering properly, but showing up in the entry wrong. Thanks for any help you can provide.

you’re not alone. i’m running into this right now - i have my date formatted yyyy-mm-dd as it should be, and trace shows it correctly as well. WEIRD.

i looked in the plugin file, there’s a lot of date formatting going on (localization, etc.) and there is an offset field that is an input param. i tried setting that, no luck. i put it as 1 and -1, really, i was just trying to affect some change! i can’t figure this out. i turned off daylight savings in the global preferences, no change.

does someone shed some light on the offset field, or the time_offset field, as this is clearly a little issue with this plugin! 😊 help.

UPDATE: set offset=”-86400” in your import template (that’s a full day in seconds) and you’ll get the right day – at least, that worked for me!!

I can’t get this to work! I have been trying for a few hours… A little help please?

{exp:csvgrab 
url="http://208.43.169.39/2009_calendar.txt" 
encloser="QUOTE" 
delimiter="," 
weblog="10" 
title="1" 
date="2"
category_field="5" 
use="3|4" 
fields="event_time|event_location"
}

You can check the file format at the link above and the custom field names are correct.

Thanks, David

url="http://208.43.169.39/2009_calendar.txt"

HI David - I looked at the file above, and it does not have proper line endings. The lines end in carriage returns (\r) only. They should be newlines (\n). Assuming you are on a Mac, make sure you upload the file in text mode, which will fix the line endings on upload. Or edit the file and fix the line endings before uploading.

You can check the file on a Mac by starting a Terminal and doing:

% curl http://208.43.169.39/2009_calendar.txt >test.txt
% od -c test.txt

I believe curl exists on OS X: I don’t have one to check. If not, download the file in a browser and save it to a file using the browser (don’t cut and paste). Firefox, at least on Windows, does not change the line endings when saving to a file.

The od will do a character dump of your file and you’ll be able to see the line endings.

If you have SSH access to your webhost, and it’s Linux, you could also run od -c there.

Thank you Dan. Just needed to re-encode and update in text format and it worked perfectly!

Has anyone been able to import multiple fields (columns) into multiple category groups with this plugin? It was discussed some time ago as a feature request in this thread, but it seems it was never added.

I really need a way to import around 700 records that have TWO categorizations, and therefore need to be classified via TWO category groups – one for ‘product type’ and one for ‘product application’. I can use CSVgrab as-is, and just import the first category group, but it means I’m going to have to go through each and every record and manually assign categories for the second category group – a major pain!

If anyone knows a workaround, I would LOVE to hear it!! 😛

Nick

Do you HAVE to use a space for a category delimiter?

category_delimiter=“SPACE”

I’m trying to use a pipe ( | ) but it isn’t working. If I put category_delimiter=”|” it doesn’t import the categories.

Here is the code I’m trying:

{exp:csvgrab url="http://####.com/bd.csv"
    delimiter=","
    encloser="QUOTE"
    site_id="3" 
    weblog="14"
    category_field="2"
    category_group="12"
    category_delimiter="|"
    title="1"
    use="1|2|3|4|5|6|7|8|9|10|11|12|13|14"
    fields="busdir_busname|busdir_cats|busdir_streetaddress|busdir_city|busdir_state|busdir_zip|busdir_phone|busdir_website|busdir_lat|busdir_longitude|busdir_amenities|busdir_aka|busdir_summary|busdir_products"
}

It was a while ago, but I think I had trouble with “|” as well, due to it being treated as part of a regexp, because CSVgrab was using PHP split(). See post #184. That post contains a modified CSVgrab that fixes that problem and a few other ones.

Another gotcha you may know already: The category names are the actual names, not the url titles.

One final thing: you are using field 2 both as a category field and a regular input field. Do you mean to store field 2 in a custom field? You might want to remove 2 from the regular fields:

use="1|3|4|5|6|7|8|9|10|11|12|13|14"
    fields="busdir_busname|busdir_streetaddress|busdir_city|busdir_state|busdir_zip|busdir_phone|busdir_website|busdir_lat|busdir_longitude|busdir_amenities|busdir_aka|busdir_summary|busdir_products"

Fantabulous!

Thank you so much, I need to try this out.

You know, now that you point out the redundancy of importing the second column into a separate weblog (originally to add some keywords to the page as ‘other categories this business is listed in) I now realize that a simple call for the cats would work. der….

Thanks again, off to try your modified CSVGrab (excellent plugin BTW and I hope it graduates to EE2.x)

Kevin

Brilliant! This worked perfectly, thank you and Andrew for the work on this amazing plugin.