Adding Excel File Upload option to product_csv module

[BenDover]

I get this message at top now when turning on debug after uploading:

Notice: Undefined variable: errlevel in /usr/home/web/snl149285/administrator/components/com_virtuemart/classes/excel/reader.php on line 210


Extra info:
With PHPMyAdmin I can export the products as a CVS file, add a product in a text file and then import it again with PHPMyAdmin.

[Joseph Kwan]

The notice is not a big problem. If debug site turned on, you should see a series of sql statements at the bottom of the page. I just want to see the query that do the insert which starts with

INSERT INTO jos_vm_product (cdate, mdate, vendorid ...

[hmmurdock]

I was able to install the hack fine, but when I go to upload it simply goes a blank page and hangs. The url is administrator/index2.php

The preview I did in IE worked fine, and when I continued to upload is when it hang. When I tried to preview in Firefox, it went right to the blank page.

Any advice is GREATLY appreciated, thanks!

[Joseph Kwan]

Please post your excel file.

[BenDover]

Hi Joseph,
For test I have 6 products in VM, in excel file now 7 products.
7th product not added: Line 7: Incorrect Product SKU: 7 could not be added

This is the sql string for 7th product when I upload it:
INSERT INTO #__{vm}_product (cdate,mdate,vendor_id,product_sku, product_s_desc, product_desc, product_thumb_image, product_full_image, product_weight, product_weight_uom, product_length, product_width, product_height, product_lwh_uom, product_in_stock, product_available_date, product_discount_id, product_discount_id, product_name, product_sales, attribute, custom_attribute, product_tax_id, product_publish) VALUES ('','','1','7', '', '<strong>Printformaat:</strong><br />80x200 cm tweezijdig<br /><br /><strong>Kleur:</strong><br />zilver<br /><br />De roll-up is gemaakt uit aluminium. inclusief dubbele full-color print op PVC.', '34c0a4587d60de83d3610938e0d63919.jpg', 'bfdaa47960e00d395a9da44268ca517f.jpg', '0', 'pounds', '0', '0', '0', 'inches', '0', '1189288800', '0', '0', 'Roll-up luxe dubbel', '0', '', '', '2', 'Y')

For reference, this is the update sql for 6th product, this is working (Line 6: Updated Product SKU: 6):
UPDATE #__{vm}_product SET product_sku = '6', product_s_desc = '', product_desc = '<strong>Printformaat:</strong><br />80x200 cm tweezijdig<br /><br /><strong>Kleur:</strong><br />zilver<br /><br />De roll-up is gemaakt uit aluminium. inclusief dubbele full-color print op PVC.', product_thumb_image = '34c0a4587d60de83d3610938e0d63919.jpg', product_full_image = 'bfdaa47960e00d395a9da44268ca517f.jpg', product_weight = '0', product_weight_uom = 'pounds', product_length = '0', product_width = '0', product_height = '0', product_lwh_uom = 'inches', product_in_stock = '0', product_available_date = '1189288800', product_discount_id = '0', product_discount_id = '0', product_name = 'Roll-up luxe dubbel', product_sales = '0', attribute = '', custom_attribute = '', product_tax_id = '2', mdate='' , product_publish ='Y' WHERE product_sku='6'

[Joseph Kwan]

Thanks for the posting. This helps to clarify the problem, finally. The problem is an extra "product_discount" column in addition to the "product_discount_id" column. The code is intelligent enough to convert product_discount to product_discount_id but somehow does not detect the duplicate and so the SQL complained.
Please remove the product_discount column and try again.
This advice also applies to lazyNode who is suffering from the same problem.

[BenDover]

Joseph, you are the hero of the day. It uploads now with new products.

Is this a bug?
I used the 26 standard installed fields on the configuration tab from upload.

[Joseph Kwan]

Yes, I would consider this as a bug. Actually, I don't feel it necessary to have both product_discount and product_discount_id in the allowed field. Probably the product_discount should be removed from the allowed field list on the top of the ps_csv.php file.

[Joseph Kwan]

Here is an updated version (0.6) of the hack which include
1. several bug fixes including problems with ps_csv and Excel Reader.
2. Added export to Excel file function (the file is actually Excel XML format and is not actual XLS file. But it can be read by Excel recent versions)
3. Added multi-language support which detects your encoding automatically and uses it to set the import encoding.

Let me know if there are problems.

[attachment cleanup by admin]

[Kevin]

Thanks for making this available.

Do I have to import a parent product first or can I import both parent and child at the same time.

Thanks

[Skate]

Here is an updated version (0.6) of the hack which include
1. several bug fixes including problems with ps_csv and Excel Reader.
2. Added export to Excel file function (the file is actually Excel XML format and is not actual XLS file. But it can be read by Excel recent versions)
3. Added multi-language support which detects your encoding automatically and uses it to set the import encoding.

Let me know if there are problems.

Hello I get this:
Error: Function Not Registered. product_xls is not a valid MOS_com_phpShop function.

[Joseph Kwan]

Kevin, I didn't see any code checking the parent product. So probably you need to ensure the parent product is there before adding it to the field. As a rule, I would advise putting the parent product before the child product in your upload.file.

Shalriyar, you have to add the function to the module list. Please see the readme in the zip file.

[Kevin]

Thanks again Joseph.

One more question,  Does the database discard duplicates?  If I upload the same products will it get duplicated?

[Joseph Kwan]

For csv upload, the system use product_sku to identify the the product. I guess what you mean duplicate products are products with identical product_sku. In that case, the last one in the file will overwrite all previous data.

[Kevin]

That's what I mean.  Great.

Thank once again.