Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Easy Populate & Products Attributes


VJ

Recommended Posts

I am using Easy Populate 2.76h-MS2 and when updating existing products if there is a price drop for an product attribute (from 0 to -1 cost for example) it doesn't get update.

 

step 1: I upload a product with some attributes

step 2: I download the complete csv

step 3: I edit one of the options to a -1 value (not caring about tax or whatever)

step 4: I upload the file

 

After step 4 the negative value hasn't been applied, but any other changes have.

there was an issue with this quite some time ago, but has since been fixed. I can't reproduce the problem now. can you post a link to a sample of the CSV file what you are trying to insert (with the negative number? you can PM me the link if it is sensitive data. Please don't put the data in the post since the board will change it.

Link to comment
Share on other sites

there was an issue with this quite some time ago, but has since been fixed. I can't reproduce the problem now. can you post a link to a sample of the CSV file what you are trying to insert (with the negative number? you can PM me the link if it is sensitive data. Please don't put the data in the post since the board will change it.

 

I just found the problem, if the export date + time isn't the same the attribute update will fail, but others still succeed (I generate them every time I rerun the export).

Should I rewrite my program to use a fixed date+time or is this a 'bug' in the easy populate?

 

P.s: Are you sure you release the file after the export? My browser keeps the file locked until I close/manually unlock it.

P.s2: sorry for the inconvenience.

Edited by GieltjE
Link to comment
Share on other sites

All, could you please look at this thread here:

http://www.oscommerce.com/forums/index.php?showtopic=341992

 

I am trying to insert unique values into my product_model column so that EP will work properly. Is there a SQL query that I could use to do this quickly?

 

Also, why does EP NOT extract the product ID number? You would think, since that is the unique value, it would be the one used to check products on import instead of the product_model. Seems like an oversight to me?

 

Any ideas about this?

Link to comment
Share on other sites

Okay, I got the model number issue sorted, but now when I try to upload, it produces this error:

 

File uploaded.

Temporary filename: /tmp/phpOnaAkw

User filename: BABY revised 09-aug-01.csv

Size: 6961

| 188 | Active | LAVENDER L | | | | 10.99 | Gerber Updated

1054 - Unknown column 'product_size' in 'field list'

 

UPDATE products_description SET products_name='LAVENDER LOTION', products_description='', product_size = '',product_uom = '', products_url='' WHERE products_id = '188' AND language_id = '1' LIMIT 1

 

[TEP STOP]

 

 

It would appear that it doesn't like product size? This is an extra field that we added same with product uom.

 

Any ideas?

Link to comment
Share on other sites

Okay, I got the model number issue sorted, but now when I try to upload, it produces this error:

 

File uploaded.

Temporary filename: /tmp/phpOnaAkw

User filename: BABY revised 09-aug-01.csv

Size: 6961

| 188 | Active | LAVENDER L | | | | 10.99 | Gerber Updated

1054 - Unknown column 'product_size' in 'field list'

 

UPDATE products_description SET products_name='LAVENDER LOTION', products_description='', product_size = '',product_uom = '', products_url='' WHERE products_id = '188' AND language_id = '1' LIMIT 1

 

[TEP STOP]

 

It would appear that it doesn't like product size? This is an extra field that we added same with product uom.

 

Any ideas?

what is the version and upload date of the EP you are using?

Edited by surfalot
Link to comment
Share on other sites

I just found the problem, if the export date + time isn't the same the attribute update will fail, but others still succeed (I generate them every time I rerun the export).

Should I rewrite my program to use a fixed date+time or is this a 'bug' in the easy populate?

 

P.s: Are you sure you release the file after the export? My browser keeps the file locked until I close/manually unlock it.

P.s2: sorry for the inconvenience.

 

export date + time? do you mean the date on the CSV file name or the v_date_added or v_date_avail columns?

Link to comment
Share on other sites

Hello,

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

Link to comment
Share on other sites

Hello,

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

please look at the support section of the docs and include as much of the info as you can that it asks for.

Link to comment
Share on other sites

Settings & Info

 

EP vers: 2.76h-MS2

osCommerce Online Merchant v2.2 RC2a

OS: Linux 2.6.18-92.1.10.el5PAE

HTTP: Apache

DB: MySQL 4.1.22-max-log

PHP: 5.2.8 (Zend: 2.2.0)

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

Link to comment
Share on other sites

Settings & Info

 

EP vers: 2.76h-MS2

osCommerce Online Merchant v2.2 RC2a

OS: Linux 2.6.18-92.1.10.el5PAE

HTTP: Apache

DB: MySQL 4.1.22-max-log

PHP: 5.2.8 (Zend: 2.2.0)

 

I succesfully installed easypopulate and uploaded the file into temp folder. But when I click insert into db I get the following error. Please let me know how to fix it.

 

Filename: data.csv

1054 - Unknown column 'p.products_subimage1' in 'field list'

 

SELECT p.products_id as v_products_id, p.products_model as v_products_model, p.products_image as v_products_image, p.products_subimage1 as v_products_subimage1,p.products_subimage2 as v_products_subimage2,p.products_subimage3 as v_products_subimage3,p.products_subimage4 as v_products_subimage4,p.products_subimage5 as v_products_subimage5,p.products_subimage6 as v_products_subimage6, p.products_price as v_products_price, p.products_weight as v_products_weight, p.products_date_available as v_date_avail, p.products_date_added as v_date_added, p.products_tax_class_id as v_tax_class_id, p.products_quantity as v_products_quantity, p.manufacturers_id as v_manufacturers_id, subc.categories_id as v_categories_id, p.products_status as v_status_current FROM products as p, categories as subc, products_to_categories as ptoc WHERE p.products_model = 'DHRQ2102' AND p.products_id = ptoc.products_id AND ptoc.categories_id = subc.categories_id LIMIT 1

 

[TEP STOP]

The install directions do ask you to go through all the settings at the top of the easypopulate.php before you start. :)

 

I think I know the problem. The latest version of 2.76h: "easypopulate.php 2.76h con PDF File Upload and Display - josequituc - 28 May 2009" has some other contribution settings enabled by default.

 

Check the settings section at the top of the easypopulate.php for this section: // *** Other Contributions Support ***

Make sure all those settings are false unless you have those contributions installed.

 

Note to josequituc, increase the version number when you create a new version please.

Link to comment
Share on other sites

v_date_added, I don't even add a v_date_avail since that is not in the question.

 

I still can't replicate the issue.

 

I change the attrib price to 10 or 0,

export the data,

change the v_date_added and the attrib price to -1

everything updates fine.

 

There should never be a connection between the date and the attrib price. the data is manipulated distinctly separate from each other and I don't see any like variables being used or overwritten.

 

Can you give me a couple links to two sample csv files with the one product example? One before product data, and one that represents the change giving you problems?

Link to comment
Share on other sites

I still can't replicate the issue.

 

I change the attrib price to 10 or 0,

export the data,

change the v_date_added and the attrib price to -1

everything updates fine.

 

There should never be a connection between the date and the attrib price. the data is manipulated distinctly separate from each other and I don't see any like variables being used or overwritten.

 

Can you give me a couple links to two sample csv files with the one product example? One before product data, and one that represents the change giving you problems?

 

Ok, just did some more testing, and as my export program allways gave "-0.8403" (tax value = 19% as stated in the export file) and never worked, my manual attempts with a fast "-1.0000" allways work.

 

I tested some more low value changes and all failed, by everything lower then -1.000 works.

Another strange thing I noticed (tax still 19%):

0.8403 * tax = doesn't work

1.0000 * tax = 1.19

1.5000 * tax = 1.19

 

see: http://www.speelmans.com/Example.rar

Link to comment
Share on other sites

Okay, I got the model number issue sorted, but now when I try to upload, it produces this error:

 

File uploaded.

Temporary filename: /tmp/phpOnaAkw

User filename: BABY revised 09-aug-01.csv

Size: 6961

| 188 | Active | LAVENDER L | | | | 10.99 | Gerber Updated

1054 - Unknown column 'product_size' in 'field list'

 

UPDATE products_description SET products_name='LAVENDER LOTION', products_description='', product_size = '',product_uom = '', products_url='' WHERE products_id = '188' AND language_id = '1' LIMIT 1

 

[TEP STOP]

 

 

It would appear that it doesn't like product size? This is an extra field that we added same with product uom.

 

Any ideas?

 

Here are the full specs of my EP load:

 

Settings & Info

EP vers: 2.76h-MS2
osCommerce Online Merchant v2.2 RC2a
OS: Linux 2.6.28.9
HTTP: Apache/2.2.11 (Unix) mod_ssl/2.2.11 OpenSSL/0.9.8i DAV/2 mod_auth_passthrough/2.1 mod_bwlimited/1.4 FrontPage/5.0.2.2635
DB: MySQL 5.1.30
PHP: 5.2.9 (Zend: 2.2.0)

Temp Directory:
/home/spogbz/public_html/temp/
Temp Dir is Writable
Magic Quotes is: off
register_globals is: on
Split files on: 3000 records
Model Num Size: 12
Price with tax: false
Calc Precision: 2
Replace quotes: false
Field seperator: comma
Excel safe output: true
Preserve tab/cr/lf: false
Category depth: 4
Enable attributes: false
SEF Froogle URLS: false
More Pics: false
Unknown Pics: false
HTC: false
SPPC: false
Extra Fields: false

 

Now again, I get the posted error when trying to upload and the only thing I can think of is that it doesn't like the product_size custom field.

 

Any help?

Link to comment
Share on other sites

Ok, just did some more testing, and as my export program allways gave "-0.8403" (tax value = 19% as stated in the export file) and never worked, my manual attempts with a fast "-1.0000" allways work.

 

I tested some more low value changes and all failed, by everything lower then -1.000 works.

Another strange thing I noticed (tax still 19%):

0.8403 * tax = doesn't work

1.0000 * tax = 1.19

1.5000 * tax = 1.19

 

see: http://www.speelmans.com/Example.rar

it really would help if you tell be exactly what you are doing. The nonsense about dates, taxes, and changing 0 to -1 has nothing to do with what you are actually doing. Which of course, all worked for me.

 

The actual issue here was decimals. The fact that you are trying to import decimals into the attributes. Yes, there seems to be a problem with that.

 

look for this:

if ( $$v_attribute_values_price_var < 0 ) $$v_attribute_values_price_var = strval(-((int)$$v_attribute_values_price_var));

change to this:

if ( $$v_attribute_values_price_var < 0 ) $$v_attribute_values_price_var = strval(-((float)$$v_attribute_values_price_var));

 

please note there is a precision setting in EP. Make sure you are using a precision of 4 not the default of 2.

Link to comment
Share on other sites

Here are the full specs of my EP load:

 

Now again, I get the posted error when trying to upload and the only thing I can think of is that it doesn't like the product_size custom field.

 

Any help?

 

and it won't like the product_uom either. did you add custom fields to the products table custom fields settings in EP ?

 

Did you create those fields in the database also ?

Link to comment
Share on other sites

and it won't like the product_uom either. did you add custom fields to the products table custom fields settings in EP ?

 

Did you create those fields in the database also ?

 

 

Okay not sure about the first one - add custom fields to the products table custom fields settings in EP? (Is this an oversight by me on the installation instructions?)

 

The fields are in the database yes. Not in the products table, but another one for extra fields (This was the Extra Fields Contrib). Both of these show up in EP export check box in admin.

 

Thanks

 

Rob

Link to comment
Share on other sites

Okay not sure about the first one - add custom fields to the products table custom fields settings in EP? (Is this an oversight by me on the installation instructions?)

 

The fields are in the database yes. Not in the products table, but another one for extra fields (This was the Extra Fields Contrib). Both of these show up in EP export check box in admin.

 

Thanks

 

Rob

you say you are using 2.76h. But neither "product_size" nor "product_uom" are anywhere in that version. So you must have added it somewhere. I would suggest going back to the version I uploaded and starting over. Only change the setting you are sure you understand. If there is any settings I can clarify, ask first.

Link to comment
Share on other sites

I know I am not the first to ask, and I know that it isn't yet available, however it would be great if SPECIALS could be included in EasyPopulate :D

 

Failing that or additionally, the ability to export the product_id from the database would be handy. Understanding that model could still be easy populates primary key, however if product_id was exportable then it would be easier for people to do manual sql updates into other tables not supported by Easy Populate like SPECIALS.

 

thanks

Link to comment
Share on other sites

I know I am not the first to ask, and I know that it isn't yet available, however it would be great if SPECIALS could be included in EasyPopulate :D

 

Failing that or additionally, the ability to export the product_id from the database would be handy. Understanding that model could still be easy populates primary key, however if product_id was exportable then it would be easier for people to do manual sql updates into other tables not supported by Easy Populate like SPECIALS.

sounds like you know what you are doing! This would be a great project for you to contribute! :D

Link to comment
Share on other sites

Failing that or additionally, the ability to export the product_id from the database would be handy. Understanding that model could still be easy populates primary key, however if product_id was exportable then it would be easier for people to do manual sql updates into other tables not supported by Easy Populate like SPECIALS.

the product id was used in a version of EP for the master products contribution. You will find that functionality by looking around the downloads.

Link to comment
Share on other sites

you say you are using 2.76h. But neither "product_size" nor "product_uom" are anywhere in that version. So you must have added it somewhere. I would suggest going back to the version I uploaded and starting over. Only change the setting you are sure you understand. If there is any settings I can clarify, ask first.

 

Okay, so I looked in PHPmyAdmin to find where product_size and product_uom are located.

 

They are declared in the table: products_extra_fields, where product_extra_fields_id is the unique index for the various extra fields.

 

Then the values for this are stored in: products_to_products_extra_fields table where they ref the product_extra_fields_id.

 

So for example size and uom are declared in products_extra_fields with product_extra_fields_id of 6 and 7 respectively. Then in table products_to_products_extra_fields is has products_id, product_extra_field_id (6 and 7 mentioned earlier) and product_extra_field_value where the values are stored.

 

Now, All of that has my head spinning when I go through the instructions and get to this point:

 

// BEGIN Define Custom Fields for your products database

 

The instructions indicate that only works in tables products and products_description.

 

Now I have ammended this and I am going to guess that this does nothing but create these as options to click on the export.

 

 $custom_fields[TABLE_PRODUCTS_DESCRIPTION] = array('product_size' => 'Products Size', 'product_uom' => 'U.O.M.' );

 

So at that point I am lost. the instructions are leading me to believe that I cannot access that table and subsequently its date.

 

So... please help... :-)

Link to comment
Share on other sites

Okay, so I looked in PHPmyAdmin to find where product_size and product_uom are located.

 

They are declared in the table: products_extra_fields, where product_extra_fields_id is the unique index for the various extra fields.

 

The extra fields contribution has nothing to do with the Custom Fields settings for the products and products_description tables. If you have the extra fields contribution installed you need to look at the instructions in the docs for the extra fields contribution.

 

You would use the Define Custom Fields area of the settings if you have added fields to the products or products_description tables only.

Edited by surfalot
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...