Jump to content
Sign in to follow this  
wasson65

EasyPopulate 1.0 - Populate your store from Excel / csv

Recommended Posts

Hello everyone,

 

I just turned in a contribution called EasyPopulate 1.0 in Features.

 

It is a pretty major enhancement of the Excel Upload 1.51 that dynamok had contributed.

 

EasyPopulate is intended to get your products moved into your store from an Excel or other delimited file format, and then facilitate easy maintenance of your products by providing a downloadable csv file that you can edit and upload, thus updating your store.

 

An important feature is that EasyPopulate will handle category names instead of having to use numeric codes. If EasyPopulate finds new category names, they are added on the fly.

 

The number and order of the columns in the csv can easily be modified for your needs by simply editing a single array in the php.

 

It also allows you to set a different default "no pic available" image for products, categories, and manufacturers.

 

With this, you can put your entire store on-line without manually adding categories or products.

 

NOTE: large data sets may cause php timeouts depending on the settings in your php.ini file. If this is the case, either bump up the number of seconds that a php script can run in the php.ini file (grep for 30, the current timeout), or break the upload into smaller parts.

 

Enjoy!

 

Tim

Share this post


Link to post
Share on other sites

Nice one mate.

Sounds very good.

 

Will give it a go and report back...

 

CC.

Share this post


Link to post
Share on other sites

Let me know more about this wierdness of columns if it persists. I don't actually have excel, I just use OpenOffice which doesn't seem to mind the files.

 

I put version 1.1 out there, it fixes this bug you found, plus another where if you downloaded a file, then tried to upload the same file, it would fail.

 

Also added a note in the instructions: if you already have Excel Upload on your system, you do not need to do the alter table sql statement in the install procedure.

 

Both Excel Upload and EasyPopulate use the same field, they are compatible and can be used on the same site.

 

More comments welcome!

 

Thanks,

Tim

Share this post


Link to post
Share on other sites

Fantastic addition thanks.

 

With a combination of the Excel import from dynamok (which I couldn't get to work first time) ;

 

the comments on semicolon separators from wasson65 and the Easypopulate contribution it was successful and I got this.

 

File uploaded.  

Temporary filename: /tmp/phpk2F8bo

User filename: download.csv

Size: 517

| supermouse | HOLGM200PS | Holux SUPE | Holux SUPE | 102.12 | 3 | 01/11/02 | 10 | | Joranda 5x | Microsoft | Updated

| supermouse | Test | British Me | British Me | 9.99 | 1 | 01/11/02 | 1 | | Joranda 5x | Microsoft | Updated

Thats all!

 

Point to note though ---if you get the categories wrong, it will move your products all over the place. Remember GIGO.

 

Thanks everyone


Citizen of US Minor Outlying Islands

Share this post


Link to post
Share on other sites

I uploaded a file with no problems, and then I changed the image file names and uploaded it again, but the image field wasn't updated in the UPDATE PRODUCTS statement. I inserted it and it worked Ok. I thought I'd just let you know.

 

There was another problem due to a mistake I made, I used more than 12 characters for MODEL which created quite a number of extra records as these were treated as new records when I tried to update them.

Share this post


Link to post
Share on other sites

Hello all,

 

I uploaded a file with no problems, and then I changed the image file names and uploaded it again, but the image field wasn't updated in the UPDATE PRODUCTS statement. I inserted it and it worked Ok. I thought I'd just let you know.

 

There was another problem due to a mistake I made, I used more than 12 characters for MODEL which created quite a number of extra records as these were treated as new records when I tried to update them.

 

OK, I just checked in a new version of EasyPopulate, version 1.2.

 

It fixes the bug with the product image, and also checks the length of the product_model, and will stop importing if they are too long.

 

Keep the comments coming! This is fun... 8-)

 

Get the latest Easy Populate here:

http://www.oscommerce.com/downloads.php/co...ions,700/type,3

 

Tim

Share this post


Link to post
Share on other sites

If I want to update prices for 50 products,

can I just edit my excel file and re-upload the whole cvs file and

let the easypopulate to overwrite old data in the db?

 

 

Stonez :roll:

Share this post


Link to post
Share on other sites

Dear Tim Wasson,

 

I just tried your Easy Populate today and couldn't get it to work...

does it support the Preview Release 2.1 of OSC? I noticed some

differences in the code that I was to edit in catalog.php (ie. don't have

reference to CLASS="menuBoxContentLink"). However, I do see the new

link to Easy Populate in the admin of my OSC but when I follow your

instructions I get the following messages:

 

"Screen 1" - when clicking "Easy Populate" in Admin

---------

 

Easy Populate 1.2

 

Warning: Supplied argument is not a valid MySQL result resource in ..../public_html/ocart/admin/includes/functions/database.php on line 39

 

Warning: Supplied argument is not a valid MySQL result resource in ..../public_html/ocart/admin/includes/functions/database.php on line 39

You can download files to excel, edit them and upload them again.

Save the Excel file as CSV (Comma Delimited - option when you save an excel file) and then upload.

The file has to be CSV format.

The separator between fields must be a semicolon or this WON'T WORK

Windows/Excel users will have to set this in Settings->ControlPanel->RegionalSettings->Number

OpenOffice users, when you save to Text CSV format, check the 'edit filter' box, and set the separator to a semicolon.

View category code and root code: Ukrainian <easypopulate.php?param=ukr> or English <easypopulate.php?param=eng>

 

 

"Screen 2" - when clicking "generate CSV"

---------

 

Warning: Failed opening 'includes/languages/english/easypopulate.php' for inclusion (include_path='.:/usr/local/lib/php') in ..../public_html/ocart/admin/includes/include_once.php on line 4

 

Warning: Cannot add header information - headers already sent by (output started at ..../public_html/ocart/admin/includes/include_once.php:4) in ..../public_html/ocart/admin/easypopulate.php on line 162

 

Warning: Cannot add header information - headers already sent by (output started at ..../public_html/ocart/admin/includes/include_once.php:4) in ..../public_html/ocart/admin/easypopulate.php on line 163

 

Warning: Cannot add header information - headers already sent by (output started at ..../public_html/ocart/admin/includes/include_once.php:4) in ..../public_html/ocart/admin/easypopulate.php on line 164

 

Warning: Cannot add header information - headers already sent by (output started at ..../public_html/ocart/admin/includes/include_once.php:4) in ..../public_html/ocart/admin/easypopulate.php on line 165

"v_products_image";"v_products_model";"v_categories_id";"v_products_name_u";"v_products_description_u";"v_products_name_e";"v_products_description_e";"v_products_price";"v_products_weight";"v_date_avail";"v_category_root";"v_tax_class_id";"v_products_quantity";"v_category_root_name";"v_categories_name";"v_manufacturers_name";"v_manufacturers_id";"v_status";

Warning: Supplied argument is not a valid MySQL result resource in ..../public_html/ocart/admin/includes/functions/database.php on line 39

 

Warning: Supplied argument is not a valid MySQL result resource in ..../public_html/ocart/admin/includes/functions/database.php on line 39

 

Warning: Supplied argument is not a valid MySQL result resource in ..../public_html/ocart/admin/includes/functions/database.php on line 39

 

Warning: Supplied argument is not a valid MySQL result resource in ..../public_html/ocart/admin/includes/functions/database.php on line 39

 

***The above error appears more times after this line - probably due to each of my products records in the database (over 300).

 

Thanks, I think this new add-on is a great idea - it took me about 3

months to upload my shopping cart data via the web interface - if only I

could get it to work.... anyway I would appreciate your comments.

 

sampolis

Share this post


Link to post
Share on other sites
If I want to update prices for 50 products,

can I just edit my excel file and re-upload the whole cvs file and

let the easypopulate to overwrite old data in the db?

 

 

Stonez :roll:

 

I think you mean that if you have 100 products, and change prices of 50 of them, then can you just upload the entire 100. The answer to that question is yes, you can upload the entire file. EasyPopulate will update all the products, only 50 of them will have data that looks different than before.

 

The one caveat I would include is to remember that EasyPopulate also overwrites the <b>quantity</b>, so if you are betting on OSC keeping track of your inventory levels, you could end up with a situation like this:

 

1. You upload a csv that says you have 20 of item A

2. 10 people buy item A, so you have 10 as a current quantity.

3. You decide to change the price of item A in your spreadsheet, but your spreadsheet still has 20 as the quantity.

4. You upload the new csv file, and the 10 quantity in the OSC database gets overwritten with the 20 quantity from your file.

 

Would it be helpful to have an option to turn off quantity updating in EasyPopulate for just these occasions?

 

Of course, the easy fix to the problem I described is to simply have EasyPopulate export a csv, then edit that in Excel, and it will have the correct quantities, assuming nobody buys anything between the time you download and upload the edited file.

Share this post


Link to post
Share on other sites
Dear Tim Wasson,

 

I just tried your Easy Populate today and couldn't get it to work...

does it support the Preview Release 2.1 of OSC? <big snip>

sampolis

 

I wrote it to work with the latest snapshot, I don't have a copy of 2.1 that I play with.

 

Having said that, the problems you are having start with the database connection.. I'm guessing there is a difference in how the database connection is made and named between 2.1 and 2.2. So that's the first thing to figure out. Now, once the connection is made, I'm not guaranteeing that it will work, as I'm not familiar with how much the database has changed from 2.1 to 2.2.

 

I'm sure EasyPopulate could be backported to 2.1, but I don't have any current clients who need this. If you'd like to, send me an email and we can discuss what I would charge for this work. Of course, it would be GPL'd and put in the contributions for everyone, and you'd be the hero to all the 2.1er's out there!

 

Tim

Share this post


Link to post
Share on other sites

Hello All,

 

First of all BIG THANKS TO wasson65 and Dynamok for comming up with these mods and really making it easy for those of us who have 1000's of items :!: :!: :!:

 

Tim --- 2 Questions

 

First - Is there a way to add the basic tax class to excel so that it can be imported as well? Other wise it has to be done manually for each product. How can we do this?

 

Second - Is there a way not to use the sub-catagory entry. I now leave mine blank, and only use the first one. After upload, it shows a sub-catagory within the main catagory.

And I get this in the catagories box...

  • Test--->(11),

(11)

 

Thanks Again Tim,

 

Steve

Share this post


Link to post
Share on other sites

I've successfully loaded the easypopulate contribution, but when click on [Generate a CSV to edit] from the Admin Catalog page I get the following response:

 

"Internet explorer was not able to open this internet site. The site is either unavailable or cannot be found".

 

What's wrong?

Share this post


Link to post
Share on other sites
I've successfully loaded the easypopulate contribution, but when click on [Generate a CSV to edit] from the Admin Catalog page I get the following response:

 

"Internet explorer was not able to open this internet site. The site is either unavailable or cannot be found".

 

What's wrong?

 

I'm getting the same thing...

Share this post


Link to post
Share on other sites
I've successfully loaded the easypopulate contribution, but when click on [Generate a CSV to edit] from the Admin Catalog page I get the following response:

 

"Internet explorer was not able to open this internet site. The site is either unavailable or cannot be found".

 

What's wrong?

 

I'm getting the same thing...

 

I had a really stupid bug in the first release (1.0), I had forgotten to change all the links from excel.php to easypopulate.php. I had based EasyPopulate on dynamok's Excel Import, which was named excel.php

 

If you are running 1.0 this will happen.

 

Try installing the latest version (1.2 at this particular moment) and that should take care of things...

 

Sorry.

 

Tim

Share this post


Link to post
Share on other sites
I just installed 1.2 and I get the same problem ...

 

 

any ideas?

 

I'm troubleshooting as we speak, and will post as soon as I know something.

 

Sorry to everyone..

 

Tim

Share this post


Link to post
Share on other sites

Wow! This is great! Except that Excel cannot read csv files written in EUC very well.

 

See, I run the Japanese version of OSC 2.2-CSV. There are three major Japanese encoding schemes, S-JIS, JIS, and EUC. Japanese UNIX programmers have always used EUC as their character encoding scheme. So PHP still likes to use flat text files in EUC. But with the popularity of Windows and Mac OS, programs like Excel use S-JIS as its encoding scheme.

 

So, if I try to import the CSV file downloaded using EasyPopulate, all the Japanese character become jumbled up. So, I have to first use a text editing program that can read/write EUC and S-JIS, to open the EUC csv file, save as a S-JIS csv file, then finally open it using Excel.

 

This is a minor inconvenience but if anybody else asks, I did it and so can they.

Share this post


Link to post
Share on other sites
I just installed 1.2 and I get the same problem ...

 

 

any ideas?

 

I'm troubleshooting as we speak, and will post as soon as I know something.

 

Sorry to everyone..

 

Tim

 

OK, there is workaround/fix in place. Many thanks to Vincent at www.trendsandgadgets.com for his help in troubleshooting and verifying the workaround.

 

Here is the link to the new EasyPopulate v1.3. Where there used to be one link to download the csv file, there are now two links. The second link will create the csv file and put in in your catalog/temp directory where you can use the Tools->Files to get to it or you can ftp it down to your machine.

 

Get the latest here:

http://www.oscommerce.com/downloads.php/co...ions,705/type,3

 

Let me know how this works.

 

Tim

Share this post


Link to post
Share on other sites

I must have missed something in all of the forums,,,

 

I installed the easy populate 1.2 and then tried 1.3 and got the same thing.

 

I installed it and it shows up in admin page, download the csv fine, modify it and then save it under a different name. no prob, but when I try to upload it, it seems to upload, however I get no conformation or error, and when I go to look at the site there is nothing new there.

 

Is this something stupid that I am doing?

 

Thanks, Kirk


Programmers are currently trying to make the next idiot proof software

 

The universe is currently trying to make the next idiot

 

The universe is winning

Share this post


Link to post
Share on other sites
I must have missed something in all of the forums,,,

 

I installed the easy populate 1.2 and then tried 1.3 and got the same thing.

 

I installed it and it shows up in admin page, download the csv fine, modify it and then save it under a different name. no prob, but when I try to upload it, it seems to upload, however I get no conformation or error, and when I go to look at the site there is nothing new there.

 

Is this something stupid that I am doing?

 

Thanks, Kirk

 

OK, I just had an email from somebody else with the similar problem. The upload tries to upload the file into the /tmp dir, which is the *servers* temp directory, not the directory we use for temp stuff in OSC, which is in your_document_root/catalog/temp.

 

The reason you're not seeing any errors is that I'll be your host has the errors turned off in php, which helps security (it doesn't show db usernames and passwords) but can make debugging less than optimal.

 

I'm going to look at changing the code to get it to upload into a different dir... if it can't do that, then I'll have to think some more...

 

The filemanager part of OSC allows uploads, so I'll probalby start there....

 

Thanks for the comments, I'll try to make this thing work...

Tim

Share this post


Link to post
Share on other sites

I was having the earlier problem with downloading the CSV file, I've now installed v1.3 and getting the following error:

 

Warning: open_basedir restriction in effect. File is in wrong directory in /usr/local/psa/home/vhosts/eurekaDVD.com/httpsdocs/admin/easypopulate.php on line 274

 

Warning: fopen("/usr/local/psa/home/vhosts/eurekaDVD.com/httpdocs/catalog/temp/EP1038006856.csv", "w+") - Operation not permitted in /usr/local/psa/home/vhosts/eurekaDVD.com/httpsdocs/admin/easypopulate.php on line 274

 

Warning: Supplied argument is not a valid File-Handle resource in /usr/local/psa/home/vhosts/eurekaDVD.com/httpsdocs/admin/easypopulate.php on line 275

 

Warning: Supplied argument is not a valid File-Handle resource in /usr/local/psa/home/vhosts/eurekaDVD.com/httpsdocs/admin/easypopulate.php on line 276

You can get your file in the Tools/Files under /catalog/temp/EP1038006856.csv

 

Any suggestions?

Share this post


Link to post
Share on other sites

PHP has a "safe mode" that many shared servers use. It's in place to prevent you from getting at people's files when you aren't supposed to.

 

What is happening is that the code is trying to write to a directory that the safe mode settings won't allow.

 

One test: Can you upload a file to the catalog/temp directory using the Tools->Files filemanager?

 

If you can, but EasyPopulate doesn't work, then I can address that by making EasyPopulate work like filemanager.

 

But if filemanager can't upload files to the catalog/temp dir, then you have one of two problems:

A) permissions problem on the temp dir - fix with ftping to your catalog dir, then chmod 777 temp

 

B) configuration problem. Possibly associated with trying to run OSC in the document root directory.

 

At any rate I've got to fix EP to work like Filemanager, and on the other hand, we need some docs about setting up OSC on safe-mode servers...

 

Anyone with config advice around safe-mode?

 

Tim

Share this post


Link to post
Share on other sites

Check this out:

 

I have everything working, but when I try to upload the file, I get this message:

 

File uploaded.

Temporary filename: /var/tmp/phpIjxGTZ

User filename: testimport.csv

Size: 1745

ERROR - I didn't find enough fields!

There should be 11 fields and I found 4 fields.

Are you using the right separation character in your file?

Does the number of fields in easypopulate.php match the number in your file?

 

 

I have tried importing the empty file and that works fine. I also have made the necessary changes to my regional settings.

 

Thanks Tim, I can see the smoke

Share this post


Link to post
Share on other sites

Ok well I think that that somewhat explains it. I appreciate your help.

 

I am going to work on it over the weekend, as I have set myself a deadline for thanksgiving.

 

and if I find anything else that might help I will let you know.

 

Just to make sure I am using microsoft excel 2002 on windows 2000, I set the field separator to ; and downloaded the file.

 

osc is running on my own personal linux redhat 7.3 box with apache and mysql and php, all of the default normal stuff i suppose.

 

Also my directory is like so /var/www/html/catalog/admin/

inside my /var/www/html/catalog is another catalog which I assume is what contains mysql files.

 

Do you see a problem with my config?

 

 

Sorry but I am a iis person and I have basicaly learned linux, mysql, and php in the last three weeks,, my brain is kinda fried!!

 

Thanks, Kirk


Programmers are currently trying to make the next idiot proof software

 

The universe is currently trying to make the next idiot

 

The universe is winning

Share this post


Link to post
Share on other sites
Check this out:  

 

I have everything working, but when I try to upload the file, I get this message:

 

File uploaded.  

Temporary filename: /var/tmp/phpIjxGTZ

User filename: testimport.csv

Size: 1745

ERROR - I didn't find enough fields!

There should be 11 fields and I found 4 fields.

Are you using the right separation character in your file?

Does the number of fields in easypopulate.php match the number in your file?

 

 

I have tried importing the empty file and that works fine.  I also have made the necessary changes to my regional settings.

 

Thanks Tim, I can see the smoke

 

This means that the code only found 4 fields in your data, instead of the expected 11.

 

If there are carriage returns in any of your data fields, it could cause this. Try opening the file in a text editor and see if it looks right.

 

Another simple test is to download the csv file, and upload it without changing anything. It should go in OK. If this doesn't work, then you've got really basic problems. If this does work, then try exporting, opening in excel, saving from excel to csv, and import that. If that goes OK then move on to actual modifications.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.
Sign in to follow this  

×