EasyPopulate 2.60
EasyPopulate (EP) lets you quickly populate and update thousands of products in many categories for an OSC store with data from an:
Excel spreadsheet
OpenOffice spreasheet
Filemaker database
Access database
Any delimited text file (you can now set a config var for the separator character)
Other features:
Supports Froogle export format
Supports multiple languages
Creates Categories and Manufacturers on the fly
Unlimited levels of categories/subcategories are supported.
Products without pictures can be defaulted to a standard "no picture available" image.
Upload a file via browser or grab a file from the temp directory.
EasyPopulate is released under the terms of the General Public License. Please see the license.txt for more details.
EasyPopulate comes with no warranty. If it hoses your data, it's not my problem. You always test everything on your dev store first, right? (hint, hint!).
EP will support both pre and post Nov 1, 2002 2.2 snapshots
Credits
Thanks to these sponsors - their financial support made the 2.4 release possible!
Support for Froogle downloads
Sponsored by Ted Joffs
This module was derived by Tim Wasson from the Excel Import 1.51 by dynamok.
Elari has contributed much of the multi-language support - Thanks!
Donations
If you'd like to voluntarily donate to help keep improving this contrib, or just to express your undying gratitiude, you can paypal me at wasson65@nc.rr.com, and I will return the undying gratitude! ;-)
Support
Free support for this contrib is available at forums.oscommerce.com. This is the preferred place to get support, since it is not dependent on me alone, and your posts help build the knowledge base for everyone. I do try to answer email requests, but don't guarantee that. Note that free email and forum support does not include me doing your work on your site. If you want this type of help, you can contact me via email to discuss my rates.
Requirements
EasyPopulate requires an OSC 2.2 snapshot. It will not work with 2.1. If you're stuck on 2.1 and desperately need this to work, contact me about a quote for custom programming to do the backport, or feel free to do it, and I'll include it in the next release.
EP expects a directory to exist at catalog/temp, and it needs to have permissions set so that EP can read and write files there. To set the perms, you can ftp in and do a "chmod 777 temp".
If you have made database changes to the products, products_description, or other product or category related tables, you may have some issues running the stock EasyPopulate.
EasyPopulate requires the use of the product_model field (model number). It won't work without the model number. The model numbers you assign must be unique, i.e. you can't give all your products a product_model of "test".
EasyPopulate will import tab, comma, semi-colon, or any other user-defined-separator delimited files. The default separator is a tab. If you have tabs in your product descriptions, it's going to cause problems. If you save from Excel as a "csv" file, it won't work. If you want to change the separator, there is a variable in the configuration section of easypopulate.php. Whatever you change it to, test it! Remember that any of those separator characters anywhere in your data will cause EP to think that's another column of data!
Each EP file must have the header row in place. If you upload a file without the headers, EP will throw out your first row of data.
EP should work fine on shared servers, servers with safe_mode set, and servers with magic_quotes turned on or off.
If you set the quantity of an item to zero, EP will automatically make the item Inactive in the database. To change the behaviour, see the Configuration Variables section.
EP does NO formatting or conversion of date formats. It gives you exactly what came out of mysql, and it takes exactly the data you give EP and shoves it into mysql. If you use a format that is not the mysql default, you'll push bad data into your date fields in your database. Use this format and you'll avoid problems: YYYY-MM-DD.
Also, be aware that backslashes can confuse EP and the database because the backslash character is the escape character for strings in mysql.
Installation and Configuration Instructions
1. The files in the EasyPopulate contribution are:
easypopulate.php - the code
easypopulate_functions.php - some functions that Pre-Nov-01-2002 2.2 OSC snapshots will need
license.txt - the GPL license text that EasyPopulate (EP) is released under.
and the instruction manual in various formats.
2. Copy file easypopulate.php and easypopulate_functions into admin/.
3. For adding link to the Admin, edit file admin/includes/boxes/catalog.php by adding this line:
'<a href="' . tep_href_link('easypopulate.php', '', 'NONSSL') . '" class="menuBoxContentLink">Easy Populate</a><br>'.
After:
'<a href="' . tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, '', 'NONSSL') . '"
class="menuBoxContentLink">' . BOX_CATALOG_CATEGORIES_PRODUCTS_ATTRIBUTES .
'</a><br>' .
4. Make sure that you have a directory called catalog/temp, and that it's permissions are set to allow writes to that directory (while you're in the catalog directory, execute "chmod 777 temp").
If you have changed the directory structure of your OSC install (i.e. you don't have /catalog/temp/ in your document root anymore), then you'll need to go into easypopulate.php and change the setting of "$tempdir".
5. PLEASE DO THIS!!!!!: It's a good idea to go to easypopulate.php and read the section marked "Configuration Variables". It will allow you to adjust EP's operation for your store. I meant what I said, Please, Pretty please, do read the entire configuration stuff. Really. You'll be glad you did. You'll find all kinds of neat configurable stuff there.
Froogle configuration:
EasyPopulate supports the extended froogle format. You'll need to contact Google to set up your froogle account. You'll also need to rename the output file as required by google.
Froogle downloads will include the specials price if one is set and active and applies to today.
To support the froogle export format, you must configure two configuration variables:
$froogle_product_info_path
This should be set to the full address that an external person would have to type into their browser to go directly to your product info page. Simplest way to set this is to go look at a product in your store with your browser, then copy/paste the url, and chop off everything after 'product_info.php'.
$froogle_image_path
This should be set to the full address to your images directory, with the trailing slash.
See the default settings in the code, it should be fairly obvious.
Once you've set these, test by doing a froogle export, and copy/paste the product_info url into your browser. Do the same for the image. Troubleshoot until this works as expected.
For use with the Separate Price per Customer contrib:
Edit easypopulate.php. Find function ep_create_file_layout around line 655. Uncomment all rows in that function that look like this:
#'v_customer_price_1' => $iii++,
#'v_customer_group_id_1' => $iii++,
Only uncomment the pairs of lines for as many prices as you are using. I.E. If you are using Retail and Wholesale, then only umcomment the lines that end in _1 and _2. If you have 3 levels of prices, uncomment the _1, _2, and _3 lines.
The comments in the code should help you locate these rows.
For use with Linda's Header Controller v2.0 contrib:
Edit easypopulate.php. Find function ep_create_file_layout around line 655. Uncomment the rows in that function that look like:
#'v_products_head_title_tag_'.$l_id => $iii++,
#'v_products_head_desc_tag_'.$l_id => $iii++,
#'v_products_head_keywords_'.$l_id => $iii++,
For use with the multiple image mods:
Search in the code for "bimage", and follow the directions about uncommenting lines.
The following configuration options are found in easypopulate.php in the configuration variables section of the code.
To include the taxes in your product prices:
Set the variable $price_with_tax to true. Default is false (prices do not contain taxes).
Number of records in a file when it is split:
Set the variable $maxrecs to the number of products to put in one split file. Default is 300.
Image Defaults:
Set the variables $default_image_manufacturer, $default_image_product, and $default_image_category to the desired image names.
Longer product_model in database:
The stock OSC product_model field size is a paltry 12 chars, much to small. If you do increase the size in the database, you must also set the variable $modelsize to match. EP is very picky about the length of this because bad length settings could cause data corruption. Default is 12.
Controlling replacement of the quotation marks in the descriptions:
If you want to turn off the conversion of single and double quotation marks in your descriptions, set the $replace_quotes variable to false. Default is true.
To turn off the "zero qty products are made inactive automatically":
Change the setting of the $zero_qty_inactive from true to false.
To create your own custom file layouts:
This is not hard, but is more than setting a few config vars. Look in the function ep_create_file_layout, and happy hacking!
There are more in the config section that are not discussed here. You did read the entire config section like I suggested, right? ;-)
Usage
1. Go to Admin -> Catalog -> Easy Populate
2. Click one of the links at the bottom of the box marked "Download a txt file to edit" and save the tab-delimited txt file to your local machine.
3. Edit the file in Excel or OpenOffice. I don't think KSpread supports tab delimited files right now. Windows users just right click and "Open with" -> Excel.
4. After you are done editing, save the file:
Excel users: as a "Text (tab delimited)" file from Excel
OpenOffice users: as a "Text" file, check the "edit MORE HERE MORE HERE
5. If you have more that 300 items in your store, read the SPECIAL INSTRUCTIONS FOR LARGE STORES
5. Now go back to Admin -> Catalog -> Easy Populate and click the upper Browse button, find your file that you want to upload, then click Upload and Insert Into Database.
6. The file will be uploaded to your store. You should see a display that lists the items you are importing. If the last row appears blank, and complains that it can't import this record, all is fine, it's not unusual to get this error at the end of the file.
7. If you'd like to use FTP and upload the file, put it in the $tempdir directory, and then put the filename in the box under "Upload from Temp dir" and click Insert Into Database. Remember the file must be readable by the username that the web server is running as. You might have to chmod 777 the uploaded file to let the script read it.
SPECIAL INSTRUCTIONS FOR LARGE STORES
If your store has over 300 products, you will likely run into PHP timeout issues. The maximum time that a script can run is set in the php.ini file. The default is 30 seconds, and it seems that most people can upload about 300 items within that time.
If your store is over 300 items, you must split the file before it can be uploaded. You can do this by hand, or you can use the file-splitting feature of EP. If you split by hand remember that EP expects the first row in every file to be the headers, and will throw this line out. So if you don't have headers, it's going to throw out the first row of data.
To split a file with EP:
Go to Admin -> Catalog -> Easy Populate
Click the lower browse button and get the file you want to split.
Click the button named "Upload and Split file".
The file will be split into sections, each with 300 products, the last one being shorter as needed.
These files will be located in the catalog/temp directory.
You can download these files using the file manager tool under Admin -> Tools -> File Manager, or you can use your ftp client to download them.
Upload each split file independently in the normal way described above.
These new files will be saved in the /catalog/temp directory, and will be named like this: EP_Split1.txt, EP_Split2.txt, etc.
If these files are still to large, edit easypopulate and change the configuration variable $maxrecs to a smaller number. This controls how many records are put into a single file when you split it.
SPECIAL INSTRUCTIONS FOR MAC USERS
Because of the differences in the end-of-line characters used by Apple's operating systems, after editing and saving the tab-delimited file, you'll need to open the file in BBedit or equivalent and change the line endings to "Unix".
What does EP do with my data? (How it works):
When you upload a file, this is what EP does:
Breaks the file into lines based on the explicit field named "EOREOR". This word "EOREOR" must appear in the last field in each row of your data, or EP won't work.
Reads the first row to get the names of the fields you are importing. This is why you must have the header row in the file.
Checks to see if there is a valid product_model in the row, if it's not there, throw an error and go on to the next row
Checks to see that the product_model is not too long. Stock OSC comes with just 12 characters available for the product_model. If you need more than 12, change your database and edit easypopulate.php and change the configuration variable $modelsize. If it is not the right size, the import stops right then.
If the product_model does not exist in the database, the product is created with the given information (price, weight, qty). If it does exist, then the given information is updated.
Checks to see if the category(ies) given exist in the database. If they do, it puts the product in to that category/subcategory. If they do not exist, it creates the category/subcategories as needed, and then puts the product into that category/subcategory.
Tab-delimited file format:
This describes the file layout for the "Complete" download.
1. fields are delimited with a single tab character
2. fields do not need to be enclosedin quotes
The names of all the fields in order are:
'v_products_model'
'v_products_image'
'v_products_name_1
'v_products_description_1
....
... If you have muliple languages, there will be more pairs of name/description here
...
'v_products_name_N
'v_products_description_N
... If you have Linda's Header Controller v2.0 enabled, you will see these fields
'v_products_head_title_tag_N'
'v_products_head_desc_tag_N'
v_products_head_keywords_N'
... end of Header Controller fields
'v_products_price'
'v_products_weight'
'v_date_avail'
'v_products_quantity'
... If you have turned on support for the Multiple Prices per Product, you'll see these fields here:
'v_customer_price_N'
'v_customer_group_id_N'
... Note: these must appear in pairs!! You cannot logically have a price without an ID to apply it to.
... End of Multiple Prices per Product fields
'v_manufacturers_name'
'v_categories_name_1'
'v_categories_name_2'
'v_categories_name_3'
'v_categories_name_4'
'v_categories_name_5'
'v_categories_name_6'
'v_categories_name_7'
'v_date_added'
'v_tax_class_id'
'v_status'
hejno