Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

EasyPopulate 1.0 - Populate your store from Excel / csv


wasson65

Recommended Posts

thanks wasson...

 

it is working now, though on line 440, I had to change "$max_manufacturers_id" to the new "$max_mfg_id" string.

 

other than that it works great....

 

but those darn excell double qoutes... the code doesn't allow them to pass through (SQL syntax error), but if you change the $items[$i] = substr($items[$i],1,strlen($items[$i])-1); to something like the length-20 instead of one, it allows it pass through to the database...

 

Now here is the part I don't get (maybe cause I am not that great with PHP)... when you goto look at that product in the database, the double quotes are still around the description (even though we told it to strip off some characters... 20 in my test case!), and the quadruple quotes are still there (even though we told it to change all cases of them to normal double quotes)...

 

example:

 

the description in the Excell cell is: This is my "great" description.

in the exported file it shows as: "This is my ""great"" description."

 

the new code doesn't allow this to pass through to the database, but just for a test I changed the end to -20 instead of -1... It now passes to the database.

 

Pull up the product desctription and I expect to see:

This is my "great" description.

 

But instead see:

"This is my ""great"" description."

 

over at PHP.net in the manual there is a note on str_replace that states it may not work with strings longer than 329 characters.  that would explain why the quadrouple quote wasn't changed... but I don't get why the start and end quotes weren't stripped off when they were passed to the database.

 

Thanks for the fix on the manufacturer id stuff, that'll definitely go into the next rev.

 

I'll check out the 329 char limit on the str_replace, and adjust as needed there.

 

The double quotes at the start and end of the desc, I'll go over these again, and combine with the other suggestions that people are putting in.

 

This double quotes thing seems to be the last really serious problem we've got to figure out.

 

Thanks for the help

Link to comment
Share on other sites

  • Replies 362
  • Created
  • Last Reply

Top Posters In This Topic

Ah, this is better. I was afraid I was loosing my brains. I'd manually added double sets of quotes around things, and at the beginning and ends of strings, and tested, and they had looked right.

 

But it turns out I've got php running with magic quotes off!

 

Is Magic_quotes required for OSC? Are there hosters that run with it off?

 

I'd like for the code to be smart enough to handle both cases, if possible...

 

I'll mull this over for a bit. Thanks for the pointers.

 

Tim

Link to comment
Share on other sites

Can any of you guys help me to change this version ?

I need to import my product structure as :

 

1. Manufactur

2. Subcategory 1

3. Subcategory 2

4. Subcategory 3

5. Product

 

I have 3700 products to update each week... So I need a "special version" of the excellent importer.

 

Kim

 

Kim, you've been very patient with your requests, I'm really going to try to fix this tonight. It's a natural direction to go, it makes sense, I guess it's time for me to shut up and code it! ;-)

 

Tim

Link to comment
Share on other sites

yeah... I've got mine adjusted for magic quotes and it works wonderful now (see the fix code I wrote)... I don't know if OSC requires magic quotes or not, but when I installed it gave me the error "magic quotes are turned off"... that might just be for the install...

 

yeah an if statement to check if they are turned on or off and then use the appropirate "clean-up" code would make it more universal...

 

in the mean time, I'll sit here and mull over why if I delete the default categories & products that come with OSC, my database gums up.

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

hey I got kinda bored, so I cam up with this to check for magic_quotes... its kinda messy (with the 3 if statements), but I tested it against all possible situations with the magic_quotes in PHP.ini (there are 2 types we are worried about) and every situation (only 4 possible situations: both on, both off, one on&one off [2 ways]) and it came threw in each situation. Again I'm new to PHP, so you might find a better solution than this:

	// 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_gpc') == 1){ // The magic_quotes_gpc 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],1,strlen($items[$i])-2);

   }

   // 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 {

	 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 1st two and last two characters of the string.

   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 { // magic_quotes are turned off.

   // 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]);

	 }

	 }

 }

 }

}

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

no I was wrong... the above code doesn't work if both magic_codes are turned on... I was looking to do a OR statement... you know "if a is 1 or b is 1 then do this"... but PHP doesn't seem to have any kinda statements like that, not that I could find anyways.

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

ok I got it... just need to scitch the GPC and RUNTIME around so that it looks at runtime 1st. the code should read like this:

   // 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 { 

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

           // check if the last character is a quote; 

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

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

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

           } 

           // 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.

	 echo $items[$i]; 

           if (substr($items[$i],0,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]);

  } 

        }

  }

     } 

  }

 

Now it works with all 4 magic_quote scenarios:

1) GPC off & RUNTIME off

2) GPC on & RUNTIME on

3) GPC on & RUNTIME off

4) GPC off & RUNTIME on

 

I have thouroughly tested with all 4 scenarios... placing the RUNTIME if statement 1st allows for all 4 to be properly accounted for.

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

:D to start with im impresed with your mod.

 

i have a quick question?

 

i use linda's quantity controller witch adds 13 extra images to the table and min order as well as rebate prices and others

 

the table now looks like this

 

products table structure:

 

products_id int(11) No auto_increment

products_quantity int(4) No 0

products_model varchar(12) Yes NULL

products_image varchar(64) Yes NULL

products_bimage varchar(64) Yes NULL

products_subimage1 varchar(64) Yes NULL

products_bsubimage1 varchar(64) Yes NULL

products_subimage2 varchar(64) Yes NULL

products_bsubimage2 varchar(64) Yes NULL

products_subimage3 varchar(64) Yes NULL

products_bsubimage3 varchar(64) Yes NULL

products_subimage4 varchar(64) Yes NULL

products_bsubimage4 varchar(64) Yes NULL

products_subimage5 varchar(64) Yes NULL

products_bsubimage5 varchar(64) Yes NULL

products_subimage6 varchar(64) Yes NULL

products_bsubimage6 varchar(64) Yes NULL

products_price decimal(15,4) No 0.0000

products_date_added datetime No 0000-00-00 00:00:00

products_last_modified datetime Yes NULL

products_date_available datetime Yes NULL

products_weight decimal(5,2) No 0.00

products_status tinyint(1) No 0

products_tax_class_id int(11) No 0

manufacturers_id int(11) Yes NULL

products_ordered int(11) No 0

products_quantity_order_min int(8) No 1

products_quantity_order_units int(8) No 1

products_price_list decimal(15,4) No 0.0000

products_price_rebate decimal(15,4) No 0.0000

products_discount1 int(6) No 0

products_discount2 int(6) No 0

products_discount3 int(6) No 0

products_discount4 int(6) No 0

products_discount1_qty int(6) No 0

products_discount2_qty int(6) No 0

products_discount3_qty int(6) No 0

products_discount4_qty int(6) No 0

products_discounts_id int(11) No 0

 

i would like to know how easy it would be to intergate this int your mod as it would save me a lot of time

Always open to sugestions on my site to make it better

Link to comment
Share on other sites

Wasson, I think this is it now... a closer look and magic_quotes_gpc doesn't affect what's input by your mod, so I took that out...

 

   // 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 

   echo $items[$i] . " ";

           // 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.

	 echo $items[$i] . " "; 

           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]);

        }

  }

     } 

  }

 

sorry for all these posts Wasson... I wasn't aware that magic_quotes_gpc didn't affect your mod... only the runtime magic_quotes... this will be my last post for awhile... hehe

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

whoops... those echos need to be taken out... I just put them in there when I was testing to see exactly what each magic_quote did.

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

:D to start with im impresed with your mod.

 

i have a quick question?

 

i use linda's quantity controller witch adds 13 extra images to the table and min order as well as rebate prices and others

 

the table now looks like this

 

products table structure:

 

products_id  int(11)   No    auto_increment

products_quantity  int(4)   No  0  

products_model  varchar(12)   Yes  NULL  

products_image  varchar(64)   Yes  NULL  

products_bimage  varchar(64)   Yes  NULL  

products_subimage1  varchar(64)   Yes  NULL  

products_bsubimage1  varchar(64)   Yes  NULL  

products_subimage2  varchar(64)   Yes  NULL  

--big snip --

products_price_rebate  decimal(15,4)   No  0.0000  

products_discount1  int(6)   No  0  

products_discount2  int(6)   No  0  

products_discount3  int(6)   No  0  

products_discount4  int(6)   No  0  

products_discount1_qty  int(6)   No  0  

products_discount2_qty  int(6)   No  0  

products_discount3_qty  int(6)   No  0  

products_discount4_qty  int(6)   No  0  

products_discounts_id  int(11)   No  0

 

i would like to know how easy it would be to intergate this int your mod as it would save me a lot of time

 

At the top of the EP file, you'll find a list of fields. Add the additional fields to that list, and assign numbers to show where you want them to appear in the file.

 

Then you'll need to modify the queries that select and insert into the database to handle all the additional fields.

 

Theoretically, that's it!

Link to comment
Share on other sites

holly cow! I am gone for 30 hours and there are 5 new pages.

 

Ok, I am going to use the newest version more than likely all day today.

 

but somewhat off subject,

 

I need some feedback on the system requirements for my server.

after adding 15000 products the thing is extreamly slow.

 

running redhat 7.3, with apache and mysql, osc and 15000 products.thats all that it is used for, and it is only running one website.

 

What do you think minimum should be.

 

I am running on a celeron 667 with 192 mb ram and a 20gig hd.

I uploaded the 15000 products over the weekend from home and when I got to the office this morning the thing took 2 min to load the home page.

 

Aghh, I hate slow computers

Link to comment
Share on other sites

holly cow! I am gone for 30 hours and there are 5 new pages.

 

Ok, I am going to use the newest version more than likely all day today.

 

but somewhat off subject,  

 

I need some feedback on the system requirements for my server.

after adding 15000 products the thing is extreamly slow.

 

running redhat 7.3, with apache and mysql, osc and 15000 products.thats all that it is used for, and it is only running one website.

 

What do you think minimum should be.

 

I am running on a celeron 667 with 192 mb ram and a 20gig hd.

I uploaded the 15000 products over the weekend from home and when I got to the office this morning the thing took 2 min to load the home page.

 

Aghh, I hate slow computers

 

If I had to lay odds, it's the db that's your problem. I'm not aware of any indexing as part of the standard osc install.

 

Basically, any field that's used to tie two tables together should be indexed, it makes the joins go much faster. Without the indexing, the db must scan every row in the table.

 

Hmm, sounds like another contrib: Indexing 1.0?

 

Tim

Link to comment
Share on other sites

Interesting, I assume this would be simillar to the indexing service that comes in windows 2000, I am curently trying to decide if I should install linux on another system that I have and set it up there, It is a 1.8ghzA with 512k cache and 512mb ram and so on. I am just wondering how hard it would be to move things considering I have so much set up allready and I do not know if the performance diff would be woth it. Especially if it is the data base

 

when it rains it poors

Link to comment
Share on other sites

Interesting, I assume this would be simillar to the indexing service that comes in windows 2000, I am curently trying to decide if I should install linux on another system that I have and set it up there, It is a 1.8ghzA with 512k cache and 512mb ram and so on. I am just wondering how hard it would be to move things considering I have so much set up allready and I do not know if the performance diff would be woth it. Especially if it is the data base

 

when it rains it poors

 

OK, you lost me with the "indexing service that comes with win2k". What I'm talking about are database indexes, along the lines of:

"CREATE INDEX products_products_id_index ON products (products_id);"

 

in mysql.

 

Are we talking about the same thing? The machine you have should be able to do this much better than it is...

 

Tim

Link to comment
Share on other sites

I just got an email from Alan Yost who says he's having better luck with Excel saving the file in a nice way if he choses to save as "Text (Tab delimited) instead of csv, which adds chars.

 

Somebody who's been having problems want to let me know if this helps?

 

Thanks,

Tim

 

PS: 1.61 is out, it's 1.6 with the manufacturer id fix from this thread, but nothing else. I wasn't sure if I was going to get to the magic quotes stuff today, and wanted to get this fix out.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

I did see "the thing" to be really slow as well when I added 3600 products.

Then i deleted the numbers after the category (the - category(xx) ) in the leftside list. and after that my DB ran fast again :)

 

btw check my temp site i am doing together with a friend :

www.storageshop.dk/catalog/default.php and let me know your thoughts.

 

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 :)

 

Kim

Link to comment
Share on other sites

I just got an email from Alan Yost who says he's having better luck with Excel saving the file in a nice way if he choses to save as "Text (Tab delimited) instead of csv, which adds chars.

 

This is how I save for the new version that uses tabs... I'm not even sure how you would save it as a CSV file with tabs between the fields, or even why you would want to since excell does export tab delimited files... my old CSV and new tab delimited files look the same when opened in a text editor (excell still double quotes, etc...)...

 

a note, the new CSV files exported from Easypopulate need to be renamed to *.txt to be opened as tab delimited files in excell, since they are not really csv (comma seperated values) files.

The only thing necessary for evil to flourish is for good men to do nothing

- Edmund Burke

Link to comment
Share on other sites

anyway, I was wondering, isn't it possible to also have the possibility to REMOVE records from the database? By for instance changing the "product available" column to "-1" orso? that would also speed up the process of deleting products that are no longer needed, keeping the db small at the same time and fast...

 

This is a great idea! I've been mulling it myself, it seemed odd that there was no way to delete the product.

 

We'd need to decide on the rules for this deletion to occur: If you delete a product in a subcategory/category, does it continue to exist in other categories? If no other categories use it should we "turn it off"?

 

Let's talk this one thru, it makes sense to have some functionality like this.

Let the talking commence... ;) Here are some of the first concepts that occurred to me: (please understand that I'm not really sure what it takes to implement this kind of stuff, so I'm sorry if it's outrageous at any point! I'm working from a design perspective here. :) )

 

1) I like the idea of just using the v_prod_avail field as a simple deletion indicator, since it would be easy to see in your working .csv file. Removing the date and changing to '-1' or such would be an obvious option here, as DicE said. Alternately, we could make it more verbose like the EOREOR you've already implemented and require "REMOVE" or "DELETE" in that field to make it more accessible. Only question here is if you can have EP accept a char string in a date field or if a number would be easier to process.

 

2) Possibly add another field like "product_state" or something so we can derive functionality beyond only a product deletion? Using this, we may have some of the following options (just examples):

  • 'ADD' = add or update as an active product using the v_prod_avail field as the available date (current functionality)

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

'OFF' = set product to Inactive in the database (opposite might be 'ADD' state, rather than adding an 'ON'?)

'DELETE' = delete product from the database

  • I know that's a bit much, but it would be cool to take a whole set of products offline at once without remming them from the database. :)

 

3b) Now that I'm thinking about this stuff, I want a feature in osC (and subsequently in EP) to specify a date to deactivate a product's availability!! Oooooh. Sure would be nice to set a limited availability period for a product, where you set a date range to have the product active... "mmmmm... forbidden donuuuuut..." Oops... sorry, but I got a bit off track there. :D

 

4) Regarding categories, I see this as being intuitive: When you have a product set to be deleted (by whatever method) and the category fields have valid, existing category data, EP checks for these various conditions and deletes the product accordingly:

  • -If 'v_category_root_name' and 'v_categories_name' exist, remove product from that category only.

-If 'v_category_root_name' exists (other is empty), remove product from all child categories of this root category.

-If both category fields are empty, remove product from all categories.

-I think there might be another option here that would set the product to be deleted from the database entirely, but that may be tied to the deletion flag, rather than category fields?

  • I'm thinking of various other things, but for now this basically expresses my initial thoughts on the subject. Thanks for your hard work, Tim (and now others who are adding code to the fire! ;) )
Link to comment
Share on other sites

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

Link to comment
Share on other sites

Hoza you got something there. But I prefer not to do all this work.. :)

I got 3600 partnumbes each week to update :(

 

I prefer a piece of software to do all the work for me, as i am pretty lazy.

Tim told he will come up with another version in a weeks time which solves my troubles..... he is the man !

 

But the way you mention it could be a way to do it.... thanks I did'nt even think on that !

 

 

Kim :idea:

Link to comment
Share on other sites

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=

I can't type a trick like i can type.

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
×
×
  • Create New...