EasyPopulate 1.0 - Populate your store from Excel / csv

[Guest]

kborup I like the style and the way that the site looks.

 

and to you and Tim, I did take the counter off and that made all of the difference in the world. But the odd thing is that say I go to the home page, no counter, I click on the categorie and it takes me to the page that shows my categories to the left, and then there are counters,, then I click on a sub category and the counter go away agian, it is just acting sparatic, I deleated my temps and cookies and so on, and I am rebooting the server soon, mabey this will solve it.

[Guest]

Kirk6677.

 

Did you change it the following way in /catalog/includes/boxes/categories.php ? :

 

Code (original):

if (tep_has_category_subcategories($counter)) {

$categories_string .= '->';

}

 

 

to something like this:

 

Code:

if (tep_has_category_subcategories($counter)) {

$categories_string .= ' ';

}

 

_________________

[dreamscape]

2)'OUT' or 'OOS'  = set product to "out of stock" in the database, but keeping the entry and the existing product dates

 

I thought that OSC had an option in the admin panel to control stock... it automatically sets a product as "out of stock" if a certain level is reached..

 

so then turning that on in the admin panel, setting a product out of stock would as easy as changing the quantity available to 0.

 

I just tested and it works... though products do not show out of stock in the descriptions, but only in the cart... but that is a function of OSC (that I'm sure could be changed) and not easy populate.

[wasson65]

Tim : I am really looking forward to see if you can modify the import thing so I can import my spreadsheet with all the subcategories

Am I missing something with the issue of multiple sub-categories? I'm thinking that you could put a product into any category you want, as long as they all existed if it's a 3+ deep tree. When you look in the database, all the categories are listed linearly anyway, it's just the variables for parents and children that creates the trees.

 

If you mean having the upload create a tree from scratch for a new product, what about just putting in several sample products into a small "tree-building" upload? It would have one product entry for each category that would need to be cleaned up later, but at least you'd get the functionality of EP now! :)

 

product1; cat_root_name = "Main Cat 1"; categories_name = "sub cat 1";

product2; cat_root_name = "sub cat 1"; categories_name = "sub cat 2";

continue...

Now, when you add your products, you just fill in the proper categories to add them in.

 

Again, I may be missing something in this discussion about what you're after. I haven't played with anything more than a one-deep tree for now, so I might be way off on the topic.

 

--Hoza

 

This is along the right lines, but right now the code expects that "category_root" has a parent_id of zero. And if we remove that requirement, then if someone had the same subcategories under a root category, then you'd have an ambiguous case -- if two categories have names that match, how do we know which one they meant?

Example:

Movies: Bugs Bunny: Pre 1950

Movies: Bugs Bunny: Post 1950

Souveniers: Bugs Bunny: Pre 1950

Souveniers: Bugs Bunny: Post 1950

 

See? If an item comes in with a root of Bugs Bunny and a category of Post 1950, we don't have enough information to handle this properly.

 

The right fix is to change the export/import so it will support however many columns of categories you want. I'm planning on doing this sometime this week.

 

Tim

[dreamscape]

After uploading excel file i get below error .  

File uploaded.  

Temporary filename: /tmp/phpEgJSim

User filename: EP1038153967.csv

Size: 3087

| matrox/mg2 | "299.9900 | "23.00" | "2002-10- | "32" | "Hardware | "Graphics | "Matrox" | | | | !New Product!

1064 - You have an error in your SQL syntax near '' at line 8

 

SELECT products_to_categories.products_id, products_to_categories.categories_id FROM products_to_categories WHERE products_to_categories.products_id=31 AND products_to_categories.categories_id=

 

this is because wasson hasn't gotten around to accounting for magic_quotes yet. I can tell that they are turned on in your system.. thats ok, they are turn on in my system too... until wasson releases the version that will look for the magic_quotes, you can use my fix I came up with so that I could run easypopulate. its really quite simple to install... just open easypopulate.php and around line 371 replace this code:

	// make sure all non-set things are set to '';

// and strip the quotes from the start and end of the stings.

// escape any special chars for the database.

for ( $i=0; $i<=$filelayout_count; $i++ ){

 if (isset($items[$i]) == false) {

	 $items[$i]='';

 } else {

	 // if there are double quotes on the ends of the string, chop them off

	 if (substr($items[$i],0,1) == '"'){

   $items[$i] = substr($items[$i],1,strlen($items[$i])-1);

	 }

	 if (substr($items[$i], strlen($items[$i])-1 ,1 ) == '"'){

   $items[$i] = substr($items[$i],0,strlen($items[$i])-1);

	 }

	 // now any remaining doubled double quotes should be converted to one doublequote

	 $items[$i] = str_replace('""','"',$items[$i]);

	 $items[$i] = addslashes($items[$i]);

 }

}

 

with this code:

   // make sure all non-set things are set to ''; 

  // and strip the quotes from the start and end of the stings. 

  // escape any special chars for the database. 

  for ( $i=0; $i<=$filelayout_count; $i++ ){ 

   

     if (isset($items[$i]) == false) { 

        $items[$i]=''; 

     } else { 

        // Check to see if either of the magic_quotes are turned on or off; 

        // And apply filtering accordingly. 

     if (function_exists('ini_get')) { 

        if (ini_get('magic_quotes_runtime') == 1){ // The magic_quotes_runtime are on, so lets account for them 

           // check if the last character is a quote; 

           // if it is, chop off the quotes. 

           if (substr($items[$i],-1) == '"'){ 

           $items[$i] = substr($items[$i],2,strlen($items[$i])-4); 

           } 

           // now any remaining doubled double quotes should be converted to one doublequote 

           $items[$i] = str_replace('""',""",$items[$i]); 

           $items[$i] = str_replace('"',""",$items[$i]); 

           $items[$i] = str_replace("'","'",$items[$i]); 

  } else { // no magic_quotes are on

     // check if the last character is a quote; 

           // if it is, chop off the 1st and last character of the string.

           if (substr($items[$i],-1) == '"'){ 

           $items[$i] = substr($items[$i],1,strlen($items[$i])-2); 

	 }

           $items[$i] = str_replace('""',""",$items[$i]); 

           $items[$i] = str_replace('"',""",$items[$i]); 

           $items[$i] = str_replace("'","'",$items[$i]);

        }

  }

     } 

  }

 

that will check if magic_quotes are turned on, and if they are, it cleans them up so you don't get those errors. enjoy

[wasson65]

never mind on the indexing,,,,, I had not had any coffee yet.

I pulled a mysqlcheck and verified everything and the optimized it. then I turned on the cache feature in admin.php and I think that helped some but not alot,, I am going to try to make some new indexes to see if this helps.

 

Kirk

 

I'm curious which indexes you created. The item counters would of course create a huge load on the database, and I've never use mysqlcheck to do optimizations, so I have no idea what it would actually tell you to do. The reason the counters would hammer the db into the ground with no indexes is because to calculate the number of items in each category, it would have to walk the entire products_to_categories table with 15k items. So 20 categories * 15000 records each time = 300000 row reads...

 

Maybe when I get bored (yeah, right!) I'll do some investigation and add some indexes. There was an indexing contribution, but I haven't delved into that yet...

 

Tim

[Guest]

kborup, Yea I did it just like that, after rebooting it worked fine,,, I guess it was on my client side, freakin IE anyways,

 

Tim, I actually havent created any indexes yet other than what was created when I made the db. once I turned off the counter I didn't need to, However I am looking into it, and If it can make an improvment in performance then I might try to make some new ones.

[skarra]

Hello I have been following with great interest :D

 

Was having trouble opening up in Excel, renaming the file to .txt was a huge help. Should I save the file in the same format?

 

I am also having the same trouble with the magic quote receiving this error message

File uploaded.  

Temporary filename: /tmp/phpHNpqpG

User filename: 0.txt

Size: 4063

| adlsmodemt | ADSLBIPACM | Billion Ex | Support | 150 | 0 | 19/11/2002 | 3 | | ADSL Produ | | Updated

1064 - You have an error in your SQL syntax near '' at line 8

 

SELECT products_to_categories.products_id, products_to_categories.categories_id FROM products_to_categories WHERE products_to_categories.products_id=30 AND products_to_categories.categories_id=

 

[TEP STOP]

 

I implemented the code change suggested by dreamscape and am still unfortunately having the same trouble.

 

Dave

[wasson65]

Hello I have been following with great interest  :D  

 

Was having trouble opening up in Excel, renaming the file to .txt was a huge help.  Should I save the file in the same format?  

 

I am also having the same trouble with the magic quote receiving this error message

File uploaded.  

Temporary filename: /tmp/phpHNpqpG

User filename: 0.txt

Size: 4063

| adlsmodemt | ADSLBIPACM | Billion Ex | Support | 150 | 0 | 19/11/2002 | 3 | | ADSL Produ | | Updated

1064 - You have an error in your SQL syntax near '' at line 8

 

SELECT products_to_categories.products_id, products_to_categories.categories_id FROM products_to_categories WHERE products_to_categories.products_id=30 AND products_to_categories.categories_id=

 

[TEP STOP]

 

The problem is that you have a single level of category.  I am, as we speak , working on a fix to EP that will let you have anywhere from 1 to 6 levels of categories.  I will be releasing this in less than an hour...

 

Tim

 

I implemented the code change suggested by dreamscape and am still unfortunately having the same trouble.

 

Dave

[skarra]

Sorry you lost me there

[wasson65]

Sorry you lost me there

 

I'm betting that your store only has one level of categories. I.E. you have a category with products in it.

 

Versions of EasyPopulate prior to 2.0 only supported stores that had a 2 levels of categories. For example, a category, with subcategories, each products in them

 

So, since I just put 2.0 out there, upgrade and then you should be OK!

 

Tim

[wasson65]

Hello all you fellow EPer's out there...

 

I've just turned in EasyPopulate 2.0!

 

Includes support for magic-quotes (thanks to the code contributors!), and 1-6 levels of categories/subcategories!

 

(Kim, this means you!)

 

Also, it now exports ".txt" files, since the concensus seems to be that excel handles tab-delimited files better if they are names .txt instead of .csv.

 

You don't need to have the same number of category levels for products. This means you can have a "Specials" category with product A in it, and you can also have product A in "Network cards"-"PCI"-"Gigabit Ethernet".

 

One note: The order of the category names in the text file is from lowest category to highest category. Taking our above example, the order of the category fields in the text file would be "Gigabit Ethernet" "PCI" "Network cards".

 

Let me know! (And I know you will!) 8-)

 

Tim

[dreamscape]

mucho gracias... I will be trying it out shortly

[skarra]

The new version fixed up the problem. Thank you.

[skarra]

I am finding that the tax class is being changed to none for products that were already in the catalog. Should I be modifying the easy populate file to include this field?

 

Also have the header tag controller which inserts a couple more fields, should these also be added?

[Guest]

Tim !!!

 

It works... almost :)

I got a strange error, where it not will import it all but just a part of it.

Can I email you my excel file, then you proberly can advise me what i am doing wrong ?

 

 

Kim

[dreamscape]

Tim !!!

 

It works... almost :)

I got a strange error, where it not will import it all but just a part of it.

Can I email you my excel file, then you proberly can advise me what i am doing wrong ?

 

 

Kim

 

Kim, I see you are importing many many products... are you using the 2nd uload box where it splits it into smaller parts and then you upload the smaller files (cause anything over 300 lines will probably cause a time out)

[Guest]

Ok, I have been staring at products too long.

 

Can anyone tell me how to change the delimiter to a tab in the global setings in windows 2000?

comma was easy but you very well can not just hit the tab button because that just takes you to the next field in the window?

 

Also this may have been addressed allready but on the pictures, is it necessary to have the extention on the end such as .jpg or .gif?

If so is there a way to merge to cells together in excell. I just have the actuall name of the picture for my product. like

9557

04732

801187

and so on, they are all gifs but I do not want to have to type .gih for 15000 products so I thought mabey I could just populate two columns, one with the picture names and the other with nothing but .gif all of the way down and then merge them together, cut the column and past it into my upload file

 

Its amazing how the simple things bogle my mind

 

Kirk

[Guest]

Kim can you paste the error in the post so we can see what it is? I had the problem with to many products and I could probably tell you if that is what is is or not.

[dreamscape]

Can anyone tell me how to change the delimiter to a tab in the global setings in windows 2000?

comma was easy but you very well can not just hit the tab button because that just takes you to the next field in the window?

 

Kirk

 

You are using excel right?? Do not save as a CSV... in excell under save as there is Text (Tab Delimited)... mine is about 4 up from CSV... this saves it as a tab delimited file in *.txt format.

[Guest]

Ok I new that part but I thought that I read in the forum somewhere or in the notepad with the download that it should be changed, Kinda confused me.

 

Anyone know about the picture issue? oh yea also I decided to delete all of my products, I have one root categorie and then about 100 subs under it, I just deleted the root categorie and it gave me the php 30 sec time out,, Now I have no categories, but all of my products are still on the site and they all showup in the database when I run a query for them, They just dont show up in admin!

 

I do not suppose it would hurt to clear out my products table in mysql?

Or will that just make things worse?

[Guest]

Kirk,

 

I am not getting any errormessages at all....

 

can I try to email the file for you ?

 

Kim

[Guest]

Sorry to post agian, but I installed 2.0 to test it and when I try to upload I get the following:

 

Warning: Unable to create '/var/www/html/catalog/temp/EP1038337199.txt': Permission denied in /var/www/html/catalog/admin/includes/functions/general.php on line 783

 

Warning: Unable to move '/tmp/phpw61ASE' to '/var/www/html/catalog/temp/EP1038337199.txt' in /var/www/html/catalog/admin/includes/functions/general.php on line 783

 

 

File uploaded.

Temporary filename: /tmp/phpw61ASE

User filename: EP1038337199.txt

Size: 386

 

Warning: file("/var/www/html/catalog/temp/EP1038337199.txt") - No such file or directory in /var/www/html/catalog/admin/easypopulate.php on line 737

 

Warning: Invalid argument supplied for foreach() in /var/www/html/catalog/admin/easypopulate.php on line 742

| | | | | | | | | | | | | | | | |

 

No products_model field in record or incorrect root for category. I cannot import this record!

 

I checked line 783 to see what it looked like and I just do not quite know enough to know if that is the problem or if its something else.

 

I am thinking that I need to specify the temp directory there or something.

 

here is what the line consists of

 

mvoe_uploaded_file($filename['temp_name'], $target);

[Guest]

Kim, there is probably nothing wrong with your file,, I had the exact same problem and thats what I thought was wrong..

My problem was that I had 15000 products and they would act as if they had uploaded but they did not and I got no message whatso ever.

 

Take your file and reduce it to like 10 products and then try it. you should then at least get some sort of message.

 

There is something that causes no message,

I try 15000 and I get nothing just like you.

I tried 500 and I get the message that says I have exceeded the maximum php.ini processing time limit, I droped it to 300 and it worked fine,,,

remember that it is also going to depend on your procesor speed memory and so on with the server. so if your server is slower than mine,,, you might have to only upload 250 files at a time.

[Guest]

i am using easypopulate version 2 on an 19/11 snapshot, everything seems fine apart from the fact that when i upload my file there is no confirmation (i am not sure if there should be one though), the page reloads and the database is not updated.

 

It has done this all the way through all the versions.

 

Does anyone have any ideas or suggestions.

 

cheers

barry