View Plugin:CSVGrab
Introduction
The CSVGrab plugin allows you to insert the contents of CSV formatted files into Expression Engine weblogs.
Step 1: Get data into spreadsheet and export as CSV
Typically spreadsheets exports the data as a plain text file with fields separated by a comma and enclosed (where necessary) in quotes.
I’m going to be using a Excel spreadsheet of golf course data and I’ll export this to a file called golf.csv
For this example, I want to store golf course name in the weblog title field, use the course type as the category, and store the address fields in EE custom fields.
Step 2: Upload this file somewhere accessible to your website
I’ll upload mine to http://www.brandnewbox.co.uk/golf.csv
Step 3: Set up a weblog to hold the data
Before creating the weblog I usually set up the custom fields and categories.
Create a new weblog field group and add some new fields for the address.
I’ll make them all text fields and call them golf_addr, golf_city, golf_state, golf_zip and golf_phone.
Create a new category group and make a not of its id.
The category group can be left empty of categories - we’ll use the plugin to fill it.
Create a weblog called golf and assign the custom fields and category groups to it.
Tip: Edit the preferences for this weblog ( Weblog Administration > Weblog Management > Edit Preferences) and in
Weblog Posting Preferences set Automatically turn URLs and email addresses into links? to No.
From the Weblog Management page make a note of the weblog’s id.
Step 4: Create a template to view the results
Tip: At this stage, you might want to put some dummy data into the new weblog through the publish form.
Create a new template group. It should have an index template automatically into which add:
<dl>
{exp:weblog:entries weblog="golf" limit="10"}
<dt>{title}</dt>
<dt>{categories backspace="9"}{category_name} • {/categories}</dt>
<dd>{golf_addr}</dd>
<dd>{golf_city}</dd>
<dd>{golf_state}</dd>
<dd>{golf_zip}</dd>
<dd>{golf_phone}</dd>
{paginate}
<div class="paginate">
<span class="pagecount">Page {current_page} of {total_pages} pages</span> {pagination_links}
</div>
{/paginate}
{/exp:weblog:entries}
</dl>
Step 5: Set up the import template and plugin
Create a new template called go and add the call to the plugin:
{exp:csvgrab url="http://ee.bnb-dev.co.uk/grab/golf.csv"
delimiter=","
encloser="QUOTE"
site_id="2"
weblog="4"
category_group="2"
title="2"
category_field="3"
use="7|8|9|10|11"
fields="golf_addr|golf_city|golf_state|golf_zip|golf_phone"
unique="golf_phone"
}
The url, delimiter, and encloser parameters describe the CSV file.
The site_id parameter should only be used if you have the MSM expansion installed.
The weblog and category_group parameters are the values you recorder earlier in the tutorial.
title="2" tells the plugin to use the 2nd column in the csv file for the entry’s title
category_field="3" tells it to use the 3rd field for the category
the use and fields parameters map columns to custom field, so column 7 goes into the golf_addr field, etc.
The unique parameter prevents further calls to the plugin adding the same
data again. In this case the plugin checks to see if the value of the golf_phone
already exists in the weblog. If not it adds a new entry, otherwise it ignores it.
Step 6: Check the results
To call the plugin, simply go to http://www.yoursite.com/index.php/newtemplategroup/go
Returning to the index template we should see a list of golf courses and addresses.
Parameters
- url="http://www.path.to/feed.rss”
- weblog="1”
- site_id="1”
- delimiter=",”
- encloser="QUOTE”
- skip="2”
- author="3”
- title="1”
- date="3”
- use="5|6”
- fields="extended|body”
- category="2" or category="flickr”
- category_field="5”
- category_delimiter="SPACE”
- category_group="2”
- unique="extended”
url=
url="http://www.path.to/file.csv”
The url of the CSV file to import
weblog=
weblog="1”
The id of the weblog to import entries into
site_id=
site_id="1”
For use with the Multiple Site Manager. The id of the site to import entries into. This is a required field if the MSM is installed
delimiter=
delimiter=",”
The character that separates fields in the CSV file. Use the value “SPACE” for a space delimiter, “TAB” for a tab character.
encloser=
encloser="QUOTE”
The character that encloses fields in the CSV file. Use the value “QUOTE” for a ‘”’ character.
skip=
skip="2”
Ignore the first ‘n’ lines of the CSV file, to avoid importing headers etc.
author=
author="1”
The id of the author to associate with the imported entries
title=
title="1”
The column to use as the entry’s title
date=
date="2”
The column to use as the entry’s publish date (currently only accepts dates in YYYY-MM-DD format)
use=
use="5|6”
A ‘pipe’ delimited list of columns that you want to import
fields=
fields="data_url|data_body”
The (custom) fields that you want the use= fields inserted into.
The number of fields must match the number in the use= parameter.
category=
category="2”
or
category="flickr”
The id or name of the category to put entries in by default.
More on Categories.
category_field=
category_field="5”
The column that contains the category (or list of categories) to add the entry to
More on Categories.
category_delimiter=
category_delimiter="SPACE”
The delimiter between categories in the category_field element. Use the value “SPACE” for a space delimiter.
category_group=
category_group="2”
Categories that do not exist are created automatically. This parameter sets the id of the category group to create new categories in.
unique=
unique="guid”
The unique field determines which fields are checked to see if the current feed element ahas already been imported. Many feeds have a guid element which identifies a unique item, but you can also define your own combinations to check, eg,
unique="title,date”
will not import any items with the same title and date as an existing element in the selected weblog.
Note, the field names are the fields set up within your own weblog (ie, title, date, and any custom fields)
Additional links
See forum thread: Plugin: CSVGrab | ExpressionEngine Forum