I've Integrated an Excel Bulk File Upload w/ Pictures (images)

For those of you interested, I have successfully integrated a bulk file upload into the Excel Import function of NopCommerce v1.8.

* The code will import up to three pictures per Product and will insert the default picture into the ProductVariant.
* It will also (finally) import the item name into the ProductVariant.
* It will set the ProductVariant "Published" to false for an Inventory number 1 or 0 to reduce backorder hassles.
* The import function will update any record that you have already placed into the database (without changing the picture)... This is a quick and easy way to update pricing and stock quantity without having to build a custom Web Service.

There are two requirements for the excel spreadsheet (and I haven't updated the excel export yet):
1.  You must include the file location for the three images that you would like to upload.
      I have created a script to automatically create the image url based on the sku.
      You can automatically place the url for the three images as the script will check to see if the image exists...
2.  You should name your product photos with the same sku as your item. This is a naming convention... and makes administration so much easier.  Since the Excel sheet needs a sku anyway all you have to do is create a script to assign the url based on the product sku and add the directory and file extension.

I would like to share this with everyone so please let me know how I can do this.

I would like to see if I can start some collaboration in getting this as a permanant part of the program and expand on it.  

For instance, though this works perfectly for each item having its own sku and essentially being its own product... the logic of the shopping cart does not make it easy.  For instance, I have an item that comes in three colors.  Each color has its own sku and its own Stock Quantity.  This is all tracked within the ProductVarient.  The product is like the Parent for the three colors.  Because of this logic and the way that the warehouses name the items (ex. Pen / Blue, Pen / Black, Pen / Red, etc.)  it is impossible to generate a script to seperate 17,000 items into a parent group and assign the correct Item Template.  That means that I would have to go in and manually assign the first item has a Product and then change the rest to ProductVarients... Not Desired.

The good thing is that the bulk upload alone has estimated to save me over 5 YEARS of work (estimated by accounting for the time to upload one picture per item).

I'm very interested seeing how you did this and will send you a PM for more info. Hope I can help you with this.

I too would love to use your script. I have been asking how to do this but have not had much luck. Probably due to me not having a clue how to use VWD or code in C# though!

Hopefully you can send me a copy of your script and also that it won't be to complex for me to implement...!

Will send you PM.

Thanks

Thanks to ajhvdb's detailed request, I have placed all the required files in a hierarchy (just like in the program) of folders to be placed in the appropriate location.  I have also included a copy of the excel spreadsheet with the 3 extra spots for pictures and test data inserted.  Finally, I included the actual photos for each product.

Instead of emailing everyone directly... I have placed all the files into a zip folder which can be downloaded from:

http://brettspencer.name/files/nopCommerce_Products_Images_Import.zip

Make sure to save a copy of your original .cs files before overwriting them, just in case you want to return to the default build.

Also, if you DO NOT HAVE VISUAL STUDIO 2010 (you don't have access to the Library directory/folder), I have included the bin folder to replace yours within your website.  This should work with no problems as it has been compiled with new script.  Please let me know if it doesn't.

<img src="http://brettspencer.name/files/nopCommerceEXcelUploadFileExampleImg.JPG" alt="Excel Image Example" />

Sorry if this sounds dumb! but what am I supposed to change the images location to?

I have uploaded the bin folder and pictures folder to my web server root and imported your xls to my site. However the images are not populating. I have tried "pictures" in place of your "j:\"

Please help...!

Thanks

Thanks very much for the sample. I'm new to nop but it seems to be designed very modulair.

I think i can create a bulk import and export with the im/export of pictures files.

Could you help me (and others) with some statements..! (the numbers 3 and 7 are random)

1.
A product has at least 1 variant.

1a.
The variant code can be empty

2.
A product can have 3 or more variants

3.
A product can have 7 or more pictures

4.
One of those 7 picturesid's is filled into the 1st variant

5.
3 pictureid's  filled into the 3 variant records are from the 7 pictures from the product

6.
I hope there is only 1 or a few getpictures methods..which ones?

Glenn...

The pictures should be located at a location on your local drive.  NopCommerce automatically grabs those pictures and either sends them to the database or the web directory ("images") depending on what you chose for your media in the "All Settings" in your Administration portal.

So Say... you have pictures on your c drive, in a folder called pictures.  You would use the excel file - where you see that I have "j:\" and you would place c:\pictures in that spot.  The excel formula will automatically generate the correct location for the three pictures in the excel file.

ajhvdb

I didn't read this post in full but saw the "random number of pictures to import - ie 7".  I have an idea for this already but will need a little time to code and implement before sharing.  This could be as easy as getting a user input for the max number of pictures to import and then sending the url as a string formula with the directory that the pictures are located at and the max number of photos.  The way I currently have it, it will check to see if pictures exist, so theoretically, this could be an easy update.

I will study the rest of your response soon and post in detail.  Thanks.

Could you help me (and others) with some statements..! (the numbers 3 and 7 are random)

1.
A product has at least 1 variant.

nopCommerce already does this automatically.  Each product is its own variant - that is why I updated the import to include the product name in the varient name.

1a.
The variant code can be empty
Not sure what you mean here... this is impossible as the varient holds crucial information for the product such as sku, pice, and stock.

2.
A product can have 3 or more variants

This is already true.  A product can have an infinite amoutn of varients.  But when you import using Excel or XML there is no way to assign a varient to a product.  You would have to go in and manually assign each product varient to a product ID for them to become a varient.  I am currently working on a script to this with my 20,000 products so that I can reduce the amount of time I need to this manually.  Unfortunately I will not be able to share the script as it manipulates strings with specific words that are pertinent to my products.  Unless the whole structure of Products and Varients changes, I don't believe there will ever be an easy way to do this.

3.
A product can have 7 or more pictures

This is possible - see my previous post.

4.
One of those 7 picturesid's is filled into the 1st variant

My script already does this.  It inserts the default picture into the varient.

5.
3 pictureid's  filled into the 3 variant records are from the 7 pictures from the product

In order to do this, there will have to be some major changes to the nopCommerce program.

     1. a new picture viewer will have to be built.
     2. a new column will have to be inserted into the product_picture table to include a product varient id mapping.
     3. all of the datatable and datagrids will have to updated as well as all of the insert, and update scripts.

I do see where this could be handy though. For instance I have a Product that has three sizes (which would be the three varients).  Each varient or size has three colors.  So theoretically I could have the three pictures for each varient.  

This isn't impossible, but would take quite a bit of work.


6.
I hope there is only 1 or a few getpictures methods..which ones?

Yes.  and if we went to a variable number of pictures to import it would be a loop through each picture using only one insert method.

Good input, thanks.

My statements were written down, from looking at your code. I do not want to change anything in nop. Thanks for your time helping me understanding the structure of nop.

1.
Q.
The variant code can be empty
A.
Not sure what you mean here... this is impossible as the varient holds crucial information for the product such as sku, pice, and stock.
Q.
The value of the variant code can be empty? There is alway a 1 to 1 relation from product to variant. And if there are 10 products, there are exactly 10 variant records? or more of course, if a product has 2 variants

2.
Q.
A product can have 3 or more variants
A.
This is already true.  A product can have an infinite amoutn of varients.  But when you import using Excel or XML there is no way to assign a varient to a product.  You would have to go in and manually assign each product varient to a product ID for them to become a varient.
Q.
The variant has a productid and variantid
Product with id = 1, has variant id = 100, and product id = 1

Product with id = 2, has variant id = 101, and product id = 2
Product with id = 2, has variant id = 102, and product id = 2

isn't this how it's working now?

4.
Q.
One of those 7 picturesid's is filled into the 1st variant
A.
My script already does this.  It inserts the default picture into the varient.
Q.
How do you know what's the default?