Plugin: CSVGrab

Dan, perhaps you’d be comfortable with just publishing a diff file? I can put together some quickie instructions for everyone about applying a diff to a textfile – it’s a pretty trivial process and there are several free tools that will handle it for you. (I’d like this feature too, as you might guess!)

Dan, perhaps you’d be comfortable with just publishing a diff file?

Well… It’s still Andrew’s code, at the base. Or, moderators, is a diff file OK? But I’d really just rather hear from Andrew first.

While composing this reply, I checked Andrew’s site, and just yesterday he posted, saying new add-ons and features are coming soon.

I apologize if I’m seeming overly cautious. Elsewhere in the forums I’ve seen issues about modifying and publishing changes to someone else’s plugin. Using EE is not my day job; it’s just part of some volunteer work I’m doing. However, other people do make money from EE, and I do not want to tread on Andrew’s business possibilities without his permission.

Hi Dan,

Sorry. I’d not been getting emails saying the thread had been updated and haven’t checked back here recently. You are welcome to post the changes and if you can send me a copy I’ll look to merge them with the current version. I’ve a new version of the plugin ready to release at some point soon so (with your permission) I’ll try and add your changes to it.

Cheers, Andrew

Hi Andrew (et al),

Fantastic! Attached is my patched-up version of CSVgrab, with the following additions and changes. Take whatever you wish:

1. Can specify expiration_date=… in the tag and have it extracted and stored.

2. Use strtotime() to parse the date and expiration date fields, instead of requiring YYYY-MM-DD format. This allows freer-form input, and also allows specifying times along with dates.

3. Figure out whether DST applies to date= field, and set flag in weblog database entry accordingly. (Note that the database table only has such a flag for the entry date, not expiration_date.)

4. Work around a bug in Location->set_localized_offset() which may cause a time to be off by a second or two (see here; bug will be fixed post-EE1.6.4).

5. Print an error when an unknown custom field name is used, rather than just quietly dying.

6. Use explode() instead of split() when splitting up categories. split() treats the delimiter as a regexp, which didn’t work for my choice of delimiter. (I think that’s the reason – I can’t quite remember!)

I did not change the documentation string; I should have.

I am somewhat mystified by the code following the comment ”// Specific category by feed field”. I left it in, but I wonder if it is a relic from FeedGrab.

All these changes were motivated by a single CSV import task, which I am planning to do just once or twice when converting over an old calendar system to EE.

Regards, Dan

I’m having difficulty getting the plugin to import data. The problem appears to be related to the location of my csv file, but my attempts to debug my situation have me confused.

The EE installation is hosted on EngineHosting. I’m using the latest version of CSVGrab.

My csv file is at: http://orel.pmhclients.com/import/testWorks.csv

I initially had the csv file at the root level of the site and then moved it into a directory I created called “import”, the results are the same in both cases.

My CSVGrab template is:

{exp:csvgrab url="http://orel.pmhclients.com/import/testWorks.csv"
    delimiter=","
    encloser="QUOTE"
    weblog="12"
    title="1"
    skip="2"
    use="2|3"
    fields="testOpus|testFullTitle"
}

If I use an absolute URL: http://orel.pmhclients.com/import/testWorks.csv nothing is imported, but I get no errors.

If I use an absolute URL, but do not have the line: encloser=”QUOTE” in the go.php template, I get this error:

Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /www/userid/public_html/systemDirectory/plugins/pi.csvgrab.php on line 202

I don’t know if that’s any indication of whether it is finding the csv file or not.

But if I use a relative URL: url=”/import/testWorks.csv”

I get a long sheet of errors, headed by these two lines:

Warning: fopen() [function.fopen]: open_basedir restriction in effect. File(/import/testWorks.csv) is not within the allowed path(s): (/www/userid) in /www/userid/public_html/systemDirectory/plugins/pi.csvgrab.php on line 192

Warning: fopen(/import/testWorks.csv) [function.fopen]: failed to open stream: Operation not permitted in /www/userid/public_html/systemDirectory/plugins/pi.csvgrab.php on line 192

and after that repeating:

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in /www/userid/public_html/systemDirectory/plugins/pi.csvgrab.php on line 202

Warning: array_unshift() [function.array-unshift]: The first argument should be an array in /www/userid/public_html/systemDirectory/plugins/pi.csvgrab.php on line 211 Checking:

This will continue to repeat and add to the page of errors until I stop the page from loading.

I assume this just means it can’t load the csv file, but I don’t understand why. Is it a permissions issue? Has anyone else had trouble using the plug-in on an EngineHosting site?

Each time I load the relative URL version of go.php again, it adds a blank entry to the weblog. The version with the absolute URL doesn’t do this.

Addendum: On the advice of the reply below, I’ve changed the system directory name to “sysetemDirectory” and the user ID to “userid” in the quoted code and errors above.

… My csv file is at: http://orel.pmhclients.com/import/testWorks.csv I initially had the csv file at the root level of the site and then moved it into a directory I created called “import”, the results are the same in both cases.

If you don’t have “http://” on the front, then the file is referenced as a local file (not fetched via http). So try this:

url="/www/eh8372/public_html/import/testWorks.csv"

If I use an absolute URL, but do not have the line: encloser=”QUOTE” in the go.php template, I get this error: Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /www/eh8372/public_html/orelsys/plugins/pi.csvgrab.php on line 202

If the encloser is not specified, then the empty string is used. fgetcsv() used to be OK with this, I think, but in newer versions of PHP, it must be specified. This does not have to do with the file location.

Also add

trace="true"

which will print out some helpful information as the import is happening. And make sure your custom field names are correct: if they are not, it will fail silently.

Side note: It’s probably a good idea to obfuscate the name of your system directory when you post error messages, since it is nominally a secret (and the same for your EH user id)

Thanks for your help, Dan.

I’ve added the trace line to the template and used the URL format you suggested, but it seems to produce the same lack of results as the absolute URL.

The trace return doesn’t seem to indicate anything about the csv file itself, and refers to fields that are in the weblog, but not currently referenced by the csv file or the CSVGrab template, in addition to the fields that are included:

TRACE: Array ( [title] => Array ( [is_custom] => 0 [field] => 1 ) [date] => Array ( [is_custom] => 0 [field] => ) [testOpus] => Array ( [is_custom] => 1 [field] => 2 [id] => 46 [format] => none ) [testFullTitle] => Array ( [is_custom] => 1 [field] => 3 [id] => 47 [format] => xhtml ) [testDate] => Array ( [id] => 48 [format] => none ) [testScoreLink] => Array ( [id] => 49 [format] => xhtml ) [testComposerLastName] => Array ( [id] => 50 [format] => xhtml ) [testComposerName] => Array ( [id] => 51 [format] => xhtml ) )
TRACE: Resource id #49

When I try the relative URL of url=”/import/testWorks.csv” it produces the same trace info (minus the Resource id line) and essentially the same errors as before.

If you see this or something like it:

TRACE: Resource id #49

it means the file is being opened. If you don’t see that, it could not access the file. The “relative URL” you mention doesn’t work because it’s not a URL at all without “http://”, it’s a file path. (url= is misleading in this case).

Could you post at least the first 5 lines or so of your .csv file? Note that skip=2 means you are skipping the first two lines. I assume that’s want you want.

Also, if you exported your .csv from Excel, it probably does not have “quoted text” for each item. The quotes should be optional even though you said encloser=”QUOTE”. However, you might try making up a test file of a few lines with every item quoted. That should not make a difference for fgetcsv(), but it might depend on the PHP version being used.

One other thing to check: Make sure you upload the .csv file in text mode, so that the line endings come out right. From what kind of machine are you uploading (Mac, Windows, Linux)?

Could you post at least the first 5 lines or so of your .csv file? Note that skip=2 means you are skipping the first two lines. I assume that’s want you want. One other thing to check: Make sure you upload the .csv file in text mode, so that the line endings come out right. From what kind of machine are you uploading (Mac, Windows, Linux)?

Never mind! I have answered my own question. I see you gave the URL of the file: http://orel.pmhclients.com/import/testWorks.csv . When I download that file I see it contains only carriage returns (\r), not regular Unix newlines (\n) or Windows end-of-lines (\r\n). In the browser you don’t see this difference

I think that’s the problem. So upload the .csv again, but make sure to do it in text mode, so the end-of-lines will be converted.

I’m using a simplified CSV file with only 4 lines:

"title","testOpus","testFullTitle"

"Three Songs op1","Op. 1","Three Songs op1"
"String Quartet op2","Op 2","String Quartet op2"

I originally saved it out of Excel as csv.

When I read earlier in this thread that someone had trouble with Excel files, I opened and re-saved it out of BBEdit as a text file with the .csv suffix.

I tried it without the quotes and then put them in manually with BBEdit.

I’m working on a recent Mac and uploading with Transmit, which I use for all kinds of code oriented files.

Our posts crossed. I don’t use Transmit, but I looked up how to force Text Mode:

(from http://kb.iu.edu/data/agiz.html)

“When using Transmit to move a text file between Unix and Mac OS, be sure to transfer the file in ASCII mode. This will ensure that the document is transformed into a text format appropriate for the host. Normally, Transmit will automatically decide what mode to transfer a file in; to force it to transfer files as text, from the File menu, select ASCII Mode.”

By the way, my guess is that you shouldn’t need the quotes, so once it’s working with the quotes, try it without.

I did a search and replace on the line endings and that did it!

Thanks! I never would have figured that out without your help!

Having trouble using the plugin as well, I seem to be getting a different error. Here is the error in Safari (get a similar error in Firefox).

Safari can’t open the page “http://daktech.philleitch.com/index.php/products/csv_import/”. The error was: “cannot decode raw data” (NSURLErrorDomain:-1015) Please choose Report Bugs to Apple from the Safari menu, note the error number, and describe what you did before you saw this message.

I’ve gone through and tried all the different fixes in this thread but keep coming back to the same error.

My code:

{exp:csvgrab url="http://daktech.philleitch.com/csv_import/hard_drives.csv" 
site_id="1" 
weblog="9" 
title="1" 
delimiter="," 
encloser="" 
use="2|4|5|6" 
fields="products_part_number|products_cost|products_cost_difference|products_add_sub"}

Does anyone know if this plugin allows for custom status fields to be populated from the CSV data? I have a set of data that needs to have a custom status applied to it. I’m working with data that I don’t want to take a chance at messing up with an import, so I want to get it right the first time. My first thought would be like so:

{exp:csvgrab url="http://mysite/mydata.csv"
    weblog="2"
    title="1"
    delimiter=","
    encloser="QUOTE"
    use="1|2|3|4"
    fields="title|display_name|price|status"}

The above example would put the 4th column data in the “status” field. Is this even possible?

Thanks, Sean