Import a lot of Product

I need to import a lot of products(25k) from external xml file.
What is the best way? If i use nopCommerce API it it very slow.
Is it possible to just fill DB tables, relating to products?

yes you can do that. as long as you know how to map the fields in your xml file to the field in the db everything should work.

Yes you can,  I ended up doing SQL inserts since its was taking 8 hours in 1.7 when it would take a hour before the entity framework was introduced.  From some digging that I did the entity framework isn't optimized for things like importing a lot of records.   Once there in everything seems fine.

-Keith

Hello,

With SQL can do the insert on product and productvariant tables.
The problem for me is the pictures, because when we insert a new article by nopcommerce and import an image it automatically creates the image with the correct name and dimensions.

Doing this procedure with the SQL not know if it is possible

I found that the speed really slows down with the larger objects.   So if you do your SQL insert of the product, variants etc..  then write a small function that returns the ID of the inserted product / variant,  then you can call insert picture to insert the picture then call insertproductpicture to connect it to the product.

That should still save a ton of time right there.

Also with large imports and entity framework I upgraded my system to 8 gigs and that sped up the nop imports a lot.

-Keith

Hello,

I apologize but do not quite understand your answer (maybe because my English was not very good)
Still not got my online store, because I am up to some changes locally and then move on.

I have all my products in a SQL database and want to import into nopcommerce. The images are in a folder in which each one has different sizes (eg 800x480, 500x900, ...). The name is equal to the id of the product (sku field in nopcommerce)

I realized that the SQL query that performs an insert on the products table and table produc variant.
Then you said to create a function that returns the product ID inserted.

But I do not understand the rest.


*** Knowledge of VS2010 and C # are minimal

Best Regards
Luis

When your using nops library's to create objects,  such as insert product,  it returns the product that you just inserted so you can then use it to do other things.  Like insert the picture with the product.  this takes the id of the picture and the id of the product.

If your using direct SQL to insert you'll need to then get the id of that last inserted record to then call insertproductpicture.

The quickest gain would be having the code and the database on the local system with 8gigs of ram.  that will give you a huge speed increase to start with

if you look at import in the library you'll see how nop is doing it.

-Keith

Finally i made import of 22k products with images and 1.2k categories.
I had to write SP for inserting and updating as nop API is really slow for bulk insert.
unfortunately, the speed od nop store and admin is tragic.

So my question is: for how many products was nop designed?

feryt wrote:

Could you pub SP?

It is easy but long one. It just insert data product and product variant data into related tables. For updating i have another one(see end). I have SP for importing pictures, prices, stock availability too(send me email - [email protected])

CREATE PROCEDURE [dbo].[Gen_HpTronicModule_CreateProduct](
  @HpProductID      as int,
  @CategoryID        As int,
  @ManufacturerID      As int,
  
  /* Product - mandatory*/
  @Product_Name              As nvarchar(400),
  @Product_ShortDescription        As nvarchar(max),
  @Product_FullDescription        As nvarchar(max),
  @Product_AdminComment          As nvarchar(max),
  @Product_TemplateID            As int,
  /* ProductVariant - mandatory*/
  @ProductVariant_SKU            As nvarchar(100),
  @ProductVariant_ManufacturerPartNumber  As nvarchar(100),
  @ProductVariant_TaxCategoryID      As int,
  @ProductVariant_Weight          As decimal(18,4),
  @ProductVariant_Length          As decimal(18,4),
  @ProductVariant_Width          As decimal(18,4),
  @ProductVariant_Height          As decimal(18,4)
) AS BEGIN SET NOCOUNT ON

--#region Defaults
  Declare
    /* Product - rest*/
    @Product_ShowOnHomePage        As bit = 0,
    @Product_MetaKeywords        As nvarchar(400) = '',
    @Product_MetaDescription      As nvarchar(4000) = '',
    @Product_MetaTitle          As nvarchar(400) = '',
    @Product_SEName            As nvarchar(100) = '',
    @Product_AllowCustomerReviews    As bit = 1,
    @Product_AllowCustomerRatings    As bit = 1,
    @Product_RatingSum          As int = 0,
    @Product_TotalRatingVotes      As int = 0,
    @Product_Published          As bit = 1,
    @Product_Deleted          As bit = 0,
    /* ProductVariant - rest*/
    @ProductVariant_Name        As nvarchar(400) = '',
    @ProductVariant_Description      As nvarchar(4000) = '',
    @ProductVariant_AdminComment    As nvarchar(4000) = 'Created by GENiTOR HP Import Module. Please, do not delete this Product Variant.',
    @ProductVariant_IsGiftCard        As bit = 0,
    @ProductVariant_IsDownload        As bit = 0,
    @ProductVariant_DownloadID        As int = 0,
    @ProductVariant_UnlimitedDownloads    As bit = 0,
    @ProductVariant_MaxNumberOfDownloads  As int = 0,
    @ProductVariant_DownloadExpirationDays  As int = 0,
    @ProductVariant_DownloadActivationType  As int = 0,
    @ProductVariant_HasSampleDownload    As bit = 0,
    @ProductVariant_SampleDownloadID    As int = 0,
    @ProductVariant_HasUserAgreement    As bit = 0,
    @ProductVariant_UserAgreementText    As nvarchar(max) = '',
    @ProductVariant_IsRecurring        As bit = 0,
    @ProductVariant_CycleLength        As int = 1,
    @ProductVariant_CyclePeriod        As int = 0,
    @ProductVariant_TotalCycles        As int = 1,
    @ProductVariant_IsShipEnabled        As bit = 1,
    @ProductVariant_IsFreeShipping        As bit = 0,
    @ProductVariant_AdditionalShippingCharge  As money = 0,
    @ProductVariant_IsTaxExempt          As bit = 0,
    @ProductVariant_ManageInventory        As int = 1,
    @ProductVariant_StockQuantity        As int = 0,
    @ProductVariant_DisplayStockAvailability  As bit = 1,
    @ProductVariant_MinStockQuantity      As int = 0,
    @ProductVariant_LowStockActivityID      As int = 0,
    @ProductVariant_NotifyAdminForQuantityBelow As int = 1,
    @ProductVariant_OrderMinimumQuantity    As int = 1,
    @ProductVariant_OrderMaximumQuantity    As int = 99,
    @ProductVariant_WarehouseID          As int = 0,
    @ProductVariant_DisableBuyButton      As bit = 0,
    @ProductVariant_Price            As money = 0,
    @ProductVariant_OldPrice          As money = 0,
    @ProductVariant_ProductCost          As money = 0,
    @ProductVariant_CustomerEntersPrice      As bit = 0,
    @ProductVariant_MinimumCustomerEnteredPrice  As money = 0,
    @ProductVariant_MaximumCustomerEnteredPrice  As money = 0,
    @ProductVariant_PictureID        As int = 0,
    @ProductVariant_AvailableStartDateTime  As datetime = null,
    @ProductVariant_AvailableEndDateTime  As datetime = null,
    @ProductVariant_Published        As bit = 0,
    @ProductVariant_Deleted          As bit = 0,
    @ProductVariant_DisplayOrder      As int = 1,
    @ProductVariant_DisplayStockQuantity  As bit = 0,
    @ProductVariant_Backorders        As int = 0,
    @ProductVariant_GiftCardType      As int = 0,
    @ProductVariant_CallForPrice      As bit = 0,
    
    @now As datetime = GETDATE();
--#endregion  

  
  INSERT INTO [dbo].[Nop_Product](
      [Name]
           ,[ShortDescription]
           ,[FullDescription]
           ,[AdminComment]
           ,[TemplateID]
           ,[ShowOnHomePage]
           ,[MetaKeywords]
           ,[MetaDescription]
           ,[MetaTitle]
           ,[SEName]
           ,[AllowCustomerReviews]
           ,[AllowCustomerRatings]
           ,[RatingSum]
           ,[TotalRatingVotes]
           ,[Published]
           ,[Deleted]
           ,[CreatedOn]
           ,[UpdatedOn]
    ) VALUES (
      @Product_Name,
      @Product_ShortDescription,
      @Product_FullDescription,
      @Product_AdminComment,
      @Product_TemplateID,
      @Product_ShowOnHomePage,
      @Product_MetaKeywords,
      @Product_MetaDescription,
      @Product_MetaTitle,
      @Product_SEName,
      @Product_AllowCustomerReviews,
      @Product_AllowCustomerRatings,
      @Product_RatingSum,
      @Product_TotalRatingVotes,
      @Product_Published,
      @Product_Deleted,
      @now,
      @now);
    
  Declare @ProductID As int;
  Select @ProductID = SCOPE_IDENTITY();
  
  INSERT INTO [dbo].[Nop_ProductVariant](
      [ProductID]
           ,[Name]
           ,[SKU]
           ,[Description]
           ,[AdminComment]
           ,[ManufacturerPartNumber]
           ,[IsGiftCard]
           ,[IsDownload]
           ,[DownloadID]
           ,[UnlimitedDownloads]
           ,[MaxNumberOfDownloads]
           ,[DownloadExpirationDays]
           ,[DownloadActivationType]
           ,[HasSampleDownload]
           ,[SampleDownloadID]
           ,[HasUserAgreement]
           ,[UserAgreementText]
           ,[IsRecurring]
           ,[CycleLength]
           ,[CyclePeriod]
           ,[TotalCycles]
           ,[IsShipEnabled]
           ,[IsFreeShipping]
           ,[AdditionalShippingCharge]
           ,[IsTaxExempt]
           ,[TaxCategoryID]
           ,[ManageInventory]
           ,[StockQuantity]
           ,[DisplayStockAvailability]
           ,[MinStockQuantity]
           ,[LowStockActivityID]
           ,[NotifyAdminForQuantityBelow]
           ,[OrderMinimumQuantity]
           ,[OrderMaximumQuantity]
           ,[WarehouseID]
           ,[DisableBuyButton]
           ,[Price]
           ,[OldPrice]
           ,[ProductCost]
           ,[CustomerEntersPrice]
           ,[MinimumCustomerEnteredPrice]
           ,[MaximumCustomerEnteredPrice]
           ,[Weight]
           ,[Length]
           ,[Width]
           ,[Height]
           ,[PictureID]
           ,[AvailableStartDateTime]
           ,[AvailableEndDateTime]
           ,[Published]
           ,[Deleted]
           ,[DisplayOrder]
           ,[CreatedOn]
           ,[UpdatedOn]
           ,[DisplayStockQuantity]
           ,[Backorders]
           ,[GiftCardType]
           ,[CallForPrice]
    ) VALUES (
           @ProductID,
           @ProductVariant_Name,
           @ProductVariant_SKU,
           @ProductVariant_Description,
           @ProductVariant_AdminComment,
           @ProductVariant_ManufacturerPartNumber,
           @ProductVariant_IsGiftCard,
           @ProductVariant_IsDownload,
           @ProductVariant_DownloadID,
           @ProductVariant_UnlimitedDownloads,
           @ProductVariant_MaxNumberOfDownloads,
           @ProductVariant_DownloadExpirationDays,
           @ProductVariant_DownloadActivationType,
           @ProductVariant_HasSampleDownload,
           @ProductVariant_SampleDownloadID,
           @ProductVariant_HasUserAgreement,
           @ProductVariant_UserAgreementText,
           @ProductVariant_IsRecurring,
           @ProductVariant_CycleLength,
           @ProductVariant_CyclePeriod,
           @ProductVariant_TotalCycles,
           @ProductVariant_IsShipEnabled,
           @ProductVariant_IsFreeShipping,
           @ProductVariant_AdditionalShippingCharge,
           @ProductVariant_IsTaxExempt,
           @ProductVariant_TaxCategoryID,
           @ProductVariant_ManageInventory,
           @ProductVariant_StockQuantity,
           @ProductVariant_DisplayStockAvailability,
           @ProductVariant_MinStockQuantity,
           @ProductVariant_LowStockActivityID,
           @ProductVariant_NotifyAdminForQuantityBelow,
           @ProductVariant_OrderMinimumQuantity,
           @ProductVariant_OrderMaximumQuantity,
           @ProductVariant_WarehouseID,
           @ProductVariant_DisableBuyButton,
           @ProductVariant_Price,
           @ProductVariant_OldPrice,
           @ProductVariant_ProductCost,
           @ProductVariant_CustomerEntersPrice,
           @ProductVariant_MinimumCustomerEnteredPrice,
           @ProductVariant_MaximumCustomerEnteredPrice,
           @ProductVariant_Weight,
           @ProductVariant_Length,
           @ProductVariant_Width,
           @ProductVariant_Height,
           @ProductVariant_PictureID,
           @ProductVariant_AvailableStartDateTime,
           @ProductVariant_AvailableEndDateTime,
           @ProductVariant_Published,
           @ProductVariant_Deleted,
           @ProductVariant_DisplayOrder,
           @now,
           @now,
           @ProductVariant_DisplayStockQuantity,
           @ProductVariant_Backorders,
           @ProductVariant_GiftCardType,
           @ProductVariant_CallForPrice);
          
  Declare @ProductVariantID As int;
  Select @ProductVariantID = SCOPE_IDENTITY();

  INSERT INTO [dbo].[Gen_HpTronicModule_ProductMapping] (  HpProductID, NopProductID, NopProductVariantId, IsLocked, NopCategoryID, NopManufacturerID ) VALUES ( @HpProductID, @ProductID, @ProductVariantID, 0, @CategoryID, @ManufacturerID);

  INSERT INTO [dbo].[Nop_Product_Category_Mapping] (
    ProductID,
    CategoryID,
    IsFeaturedProduct,
    DisplayOrder
  ) VALUES ( @ProductID, @CategoryID, 0, 1 ) ;
    
    INSERT INTO [dbo].[Nop_Product_Manufacturer_Mapping] (
    ProductID,
    ManufacturerID,
    IsFeaturedProduct,
    DisplayOrder
  ) VALUES ( @ProductID, @ManufacturerID, 0, 1 ) ;
          
  RETURN @ProductID;
END

------------------------- PRODUCT UPDATE


CREATE PROCEDURE [dbo].[Gen_HpTronicModule_UpdateProduct]
(
  @CategoryID        As int,
  @ManufacturerID      As int,

  @ProductID        As int,
  @Name          As nvarchar(400),
  @FullDescription    As nvarchar(max),
  @AdminComment      As nvarchar(max),

  @ProductVariantID    As int,
  @Width          As decimal(18,4),
  @Height          As decimal(18,4),
  @Length          As decimal(18,4),
  @Weight          As decimal(18,4),
  @ManufacturerPartNumber as nvarchar(100)
)AS
BEGIN SET NOCOUNT ON;
  Declare @oldCategoryID as int, @oldManufacturerID as int;
  Select @oldCategoryID = NopCategoryID, @oldManufacturerID = NopManufacturerID From dbo.Gen_HpTronicModule_ProductMapping Where NopProductID = @ProductID;
  
  If @oldCategoryID <> @CategoryID -- nova ani puvodni nemuze byt null, tak staci update
    Update [dbo].[Nop_Product_Category_Mapping] Set CategoryID = @CategoryID Where ProductID = @ProductID And CategoryID = @oldCategoryID;

  If @oldManufacturerID <> @ManufacturerID -- novy anipuvodni nemuze byt null, tak staci update
    Update [dbo].[Nop_Product_Manufacturer_Mapping] Set ManufacturerID = @ManufacturerID Where ProductID = @ProductID And ManufacturerID = @oldManufacturerID;
  
  UPDATE   dbo.Gen_HpTronicModule_ProductMapping Set NopCategoryID = @CategoryID, NopManufacturerID = @ManufacturerID Where NopProductID = @ProductID;
  
  UPDATE dbo.Nop_Product Set [Name] = @Name, FullDescription = @FullDescription, AdminComment = @AdminComment Where ProductID = @ProductID;
  UPDATE dbo.Nop_ProductVariant Set [Width] = @Width, Height = @Height, [Length] = @Length, [Weight] = @Weight, ManufacturerPartNumber = ManufacturerPartNumber Where ProductVariantId = @ProductVariantID;
END