Ok I finally got past all the errors while bulk importing from excel and all products and descriptions are uploaded etc.. Now I am stuck as to how to get the images for each perspective product to link up in bulk?
I already uploaded images..I think to the correct directory etc..
How is this done, can anyone please instruct me as to the steps to take to do this?
In short I have all products uploaded via import from excel via the noscommerce admin panel and I ftpd the images folder, images, but how do I get them all to connect so the amges show with the products without having to do each and every single product image one at a time?
I know that normal process one at a time is to upload each image from my hard drive to each perspective product manually, but how to do this in bulk?
Ok I finally got past all the errors while bulk importing from excel and all products and descriptions are uploaded etc.. Now I am stuck as to how to get the images for each perspective product to link up in bulk?
I already uploaded images..I think to the correct directory etc..
How is this done, can anyone please instruct me as to the steps to take to do this?
In short I have all products uploaded via import from excel via the noscommerce admin panel and I ftpd the images folder, images, but how do I get them all to connect so the amges show with the products without having to do each and every single product image one at a time?
I know that normal process one at a time is to upload each image from my hard drive to each perspective product manually, but how to do this in bulk?
Thanks again :o)
This feature is not available in nopCommerce project, you have to do some changes in the code in order to accomplish this. You can modify Export.cs / Import.cs files for importing images along with the products. You can do something like this : You will define path in the excel file and your code will capture image from that file defined in the excel file and when you will import that excel file, all the images along with the products will get saved in your database.
I had the same problem and have over 3500 products with multiple images for each. I am by no means a DBA and cobbled together some very basic SQL using a cursor to load the images from a database to the matching product. I have pasted the script and stored porc below. They are very basic, but seem to do the job. You will need to play around with them to load any secondary images for products as you need to get the ID's correct in the database, but it will work for loading single images into the db.
The outline of the process is;
- Copy all images you wish to load into a directory called c:\images - Create and run the stored proc - Run the script to load up the images to the correct product. This is done by matching the names of the images.
You can delete the table used to store the images by the proc once you have loaded your images. Just take care with matching the names on the join. I had an issue with an bad image that caused the process to miss out one image and then each product had the wrong image against it! My use of ID's is also not very good, but I am new to this!
Stored Proc-
USE [Store1] GO /****** Object: StoredProcedure [dbo].[insert2img] Script Date: 05/13/2010 10:28:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROC [dbo].[insert2img] as Begin Declare @img1 as varbinary(max) Declare @dircmd as varchar(max) DECLARE @filename varchar(100) DECLARE @filepath varchar(100) DECLARE @maxRowID int DECLARE @count int DECLARE @tempXMLFileName table (RowId int identity(1,1), name varchar(100)) Declare @sql as varchar(max)
Set @count = 0 Set @dircmd = 'MASTER..XP_CMDSHELL "dir/b '+ 'C:\Images\*.*"'
INSERT @tempXMLFileName exec (@dircmd) SET @maxRowID = (SELECT max(RowId) FROM @tempXMLFileName) WHILE @count <= @maxRowID BEGIN SET @filename = (SELECT name FROM @tempXMLFileName WHERE [RowId] = @count) Print @filepath Set @filepath = 'C:\Images\'+@filename
Set @sql = 'Insert into dbo.aaaimg (imgname,img) SELECT ''' + @filename + ''', BulkColumn from Openrowset(Bulk ''' + @filepath + ''', Single_Blob) as image ' Print @filename print @sql Print @count EXEC (@SQL)
Set @filepath=' ' print @filepath Set @count = @count + 1
end end
You may well have to enable XP_CMDSHELL on your instance of SQL.
SQL Script -
DELETE FROM dbo.Nop_Picture DELETE FROM dbo.Nop_ProductPicture DBCC CHECKIDENT('dbo.Nop_Picture', RESEED, 0) DBCC CHECKIDENT('dbo.Nop_ProductPicture', RESEED, 0) GO
SELECT img, imgname FROM dbo.aaaimg right JOIN dbo.allproducts ON imgname=IMAGE order by id
FOR READ ONLY OPEN curPicture FETCH curPicture INTO @img,@imgname
WHILE @@FETCH_STATUS = 0 BEGIN SET @ProdCatID += 1 SET @PicID += 1 SET IDENTITY_INSERT [dbo].[Nop_Picture] ON
INSERT [dbo].[Nop_Picture] ([PictureID],[PictureBinary], [extension], [IsNew]) VALUES ( @PicID, @Img, N'image/pjpeg', N'0') SET IDENTITY_INSERT [dbo].[Nop_Picture] OFF SET IDENTITY_INSERT [dbo].[Nop_ProductPicture] ON INSERT [dbo].[Nop_ProductPicture]( [ProductPictureID],[ProductID], [PictureID],[DisplayOrder]) VALUES (@ProdCatID,@ProdCatID,@PicID,@ProdCatID ) SET IDENTITY_INSERT [dbo].[Nop_ProductPicture] OFF PRINT @imgname PRINT @ProdCatID PRINT @ProdNo FETCH curPicture INTO @img, @imgname
END
CLOSE curPicture DEALLOCATE curPicture GO
You will see above that I use a reference table called allproducts. This is the reference table that I used to actually load all the products from in the first instance and contains the product information including the image name linked to the product.
As I said very rough and ready, but it works.
Cheers,
James.
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.