Product Attribute condition for Bulk Upload

Hi,
Have a large number of product and want to upload in bulk from excel file.
Need help on how to define attribute condition from excel file.
eg. I have two attribute - Product Color and Size.
Suppose, one product have 3 color, but -
1. Red color has size of - Large, Medium, Small
2. Blue color has size of - Large, Small
3. Green color has size of - Medium, Large

So, As per requirement the Size attribute should have Attribute condition on Color attribute.
Is there anyway to define this condition from excel file for bulk upload.
Please guide me the approach.

Thank You.

I would recommend that you manually create at least one product exactly as it should be done using the admin system..so that everything is perfect.
Then in admin >> Catalog >> Products >> Manage Products screen, click the Export to Excel button and save the file to your PC.

You will see the columns and the method used. Use this as a template and add all of your products to it in the same format.

I use to go nuts with product attributes. Especially when you have thousands.

Here's a simple SQL script I used to update Product Attributes.

You may have to reset your id or delete them all and start fresh. In any event backup your SQL database before you even look at this script.



===========================================================================================

/****** The Update SET statement selects updated data from your edited excel
[dbo].[ProductAttributeValueUPDATED] table and Updates certain fields into Nopcommerce existing [dbo].[ProductAttributeValue] table v 3.8.
***Warning: Complete Backup FULL Metal and SQL backup Now! Works fastest up to 50 million Products.
==========================================================================================
This SQL Code Updates Field: Name
This code is great to correct errors in colors or sizing or even convert sizing to USA sizes etc.  
===========================================================================================
STEPS:
1)export out dbo.product using TASKS>EXPORT DATA>DATASOURCE SQL SERVER>DESTINATION MSEXCEL
(USE 32BIT VERSION IF MISSING EXCEL)SQL Server 2014 Import and Export Data (32-bit) ONLY!
2)Name your new exported ms excel file TAB dbo.ProductUpdated (this name will work with this script)
3)Watch out for ' in text fields you need it there to work.
4)UpdatedOnUtc MUST BE ADDED WITH PROPER VALUES - NO NULL ALLOWED so just add it.
5)Update field(s) one at a time then run this script each time. ***You may mess up that's why.
6)All 'CreatedOnUtc, 'UpdatedOnUtc time format is '2016-08-23 16:00:00.000 Look at dot at end.000
  use this and fill down for start and end times for fields (other time fields too)
  'SpecialPriceStartDateTimeUtc  'SpecialPriceEndDateTimeUtc
  '2016-08-10 16:00:00.000       '2016-09-11 09:30:00.000   (never forget!)
Tested with 2003-2007 excel works with SQL Server 2014 Import and Export Data (32-bit).
******/

UPDATE [dbo].[ProductAttributeValue]
SET [dbo].[ProductAttributeValue].Name = [dbo].[ProductAttributeValueUPDATED].Name
FROM [dbo].[ProductAttributeValue], [dbo].[ProductAttributeValueUPDATED]
WHERE [dbo].[ProductAttributeValue].Id = [dbo].[ProductAttributeValueUPDATED].Id

I tried the manual product load through the admin / cataloge then exported it as a template and then tried to reload and I get a "No workesheet found" error. I even tried to reload the exact same product that I exported and got the same error. Im sure its something that I am doing wrong.

I tried the manual product load through the admin / cataloge then exported it as a template and then tried to reload and I get a "No workesheet found" error. I even tried to reload the exact same product that I exported and got the same error. Im sure its something that I am doing wrong.


Your just trying to add a product with no attributes correct?

Just click export to excel(selected)select the check box your product table at the middle of page, then export/import it back in adding just one product. You probably renamed the bottom excel tab called "product" but the table name is products.xlsx

Just leave it alone do not rename the bottom tab or any top headers. Try again but search for "uploading products" this is Products attributes.... your next step.


I also have SQL code for adding/updating /deleting/resetting id.... so you won't have to use the admin panel again for uploading products. Just wait when you try to add lots of products you'll need the sql code.



Mike

Hey Mike

Thanks for the info and quick response. I will try that and let you know

Thanks
Josh

You just reminded me to do BULK Product Attribute Updates. Copy and paste this code in sql only if you know how to backup and restore your database.

This code will copy the NEW attribute data you just uploaded from your excel table(see above post how to import/export data). Always call your updated attribute value table >>>> ProductAttributeValueUpdated

and it will INSERT directly into the Nopcommerce ProductAttributeValue Table. We don't mess with this ProductAttributeValue table unless you want mass confusion.

All you need to do is to export out this ProductAttributeValue Table from your "CORRECT database name." Please don't destroy other databases. Remember you may have lots of databases with tables called ProductAttributeValue, then add in your changes/ filldown/ specifically map the correct numbers of ProductAttributeMappingId. You'll get it eventually! You will see a certain number represents a certain attribute like color. One number represents size, etc. Just make sure you add one example of each sample attribute data so you can see the mapping CLEARLY like mud. I.e. add one color, one size, one abc, one cbs, and one xyz, then export your table and note the mapping of numbers. Write the mapping on paper old school style so you don't get confused.

in SQL> right click on your CORRECT database name> New Query> Copy and paste this code (only if you have a table called ProductAttributeValueUpdated then you are ready to execute! Good luck This saved me hours and days of time if you can get it. Again make sure you are in the correct database if you have lots of them like me.


/****** Script Inserts Data in ProductAttributeValue even with ID KEY and Foreign Key  ******/


SET IDENTITY_INSERT [YOURDATABASE].[dbo].[ProductAttributeValue] off

INSERT INTO [YOURDATABASE].[dbo].[ProductAttributeValue]
      ([ProductAttributeMappingId]
      ,[AttributeValueTypeId]
      ,[AssociatedProductId]
      ,[Name]
      ,[ColorSquaresRgb]
      ,[ImageSquaresPictureId]
      ,[PriceAdjustment]
      ,[WeightAdjustment]
      ,[Cost]
      ,[Quantity]
      ,[IsPreSelected]
      ,[DisplayOrder]
      ,[PictureId])

SELECT [ProductAttributeMappingId]
      ,[AttributeValueTypeId]
      ,[AssociatedProductId]
      ,[Name]
      ,[ColorSquaresRgb]
      ,[ImageSquaresPictureId]
      ,[PriceAdjustment]
      ,[WeightAdjustment]
      ,[Cost]
      ,[Quantity]
      ,[IsPreSelected]
      ,[DisplayOrder]
      ,[PictureId]
    
FROM [YOURDATABASE].[dbo].[ProductAttributeValueUpdated]

SET IDENTITY_INSERT [YOURDATABASE].[dbo].[ProductAttributeValue] On


DBCC CHECKIDENT ([ProductAttributeValue], RESEED, 1)

Hey Mike

Awesome info I really appreciate the time for the tips. I obviously need to get more acquainted with Nopcommerce.

I did try what you said about the uploads and it is giving me a different error now.

"Could not find a part of the path 'C:\HostingSpaces\admin\boatersplanet.com\wwwroot\ExportImport\All-catalog\Error.log.14-12-2016.txt'."



Thanks
Josh

"Could not find a part of the path ....

Let me guess... you are uploading the excel file from your "LOCAL" laptop/Desktop using NOP ADMIN PANEL to a "REMOTE SERVER" location? Then that would make sense because the file path is LOCAL on your laptop which it can't find.

Try this. Save your excel file on your somewhere on your REMOTE SERVER, then login ON SERVER and try again but now point the file path of the REMOTE SERVER excel file. Just make sure you save the file to a location that HAS "PERMISSIONS for USERS." You probably can't do this due to permissions so make sure you have permissions on the sub directory.

The message is that the path does not exist means that you are local doing remote upload OR permissions issue. You can see exactly in log files on windows or in txt file. Either way simple fix unless permission and your Hosting co does not allow.

Hey Mike

That's exactly what I was doing. Kinda making sense now the way you have described it.

Thanks
Josh