I have migrate an oscommerce website (manufacturer & category & product ) -> Nop 1.80.
i used Talend Open Studio for this migrate, it's a great soft.
but, how to import images in nop database ?
I programmed in VB a little code (from scratch, in shit mode, Fast, no optimized, no secure, etc.. ;) )
my program open a CSV file with lot of line ( Id ; Product Description ; picture path ).
he open picture, scale it and stock in nop db.
I hope help you with my source.
sorry for my english, it's so bad, but i'm work :(
happy new year !
Jean-Yves.
Imports System.IO
Imports System.Data.SqlClient
''' <summary>
''' Bulk import of picture in NOP DB.
''' PARSER CSV > BLob SQL
''' Tested with NOP1.80 . Probably ok with old version.
''' </summary>
''' <remarks>use VB.NET Framework 3.5 and Linq (VB2008 and VB2010 ok) </remarks>
'''
Public Class Form1
Public Const MAX_IMAGE_SIZE As Long = 100000
Private Sub GO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GO.Click
Dim separator As Char = CChar(";")
' exemple of CSV File
' Id ; Name ; PATH Image ;
' 7139 ; 4015 clover - BIAIS 25 mm ; c:\export\020111\images\4015.jpg ;
'
Dim FileCSV = File.ReadAllLines(nomfichier.Text)
Dim NameColon = FileCSV.First().Split(separator) ' nameColon = Id ; Name ; Path (not use in this script)
Dim data = From line In FileCSV.Skip(1) _
Select line.Split(separator) ' read file but skip First Line
Dim ids = From champ In data _
Select champ(0) ' ID
Dim name = From champ In data
Select champ(1) ' NAME
Dim pictures = From champ In data _
Select champ(2) ' Picture PATH
Dim nbpictures = ids.Count
Dim height As Double
Dim width As Double
Dim ratio As Double
Dim picture1 As Bitmap
Dim picture2 As Bitmap
For i = 0 To nbpictures
If File.Exists(pictures(i)) Then
'--------------------------------------------------------------------
' rescale this picture if necessary.
'--------------------------------------------------------------------
picture1 = New Bitmap(pictures(i))
height = picture1.Height
width = picture1.Width
If height > 600 Then ' if height> 600px then height 600px & calc Width.
ratio = height / 600
height = 600
width = width / ratio
End If
If width > 600 Then ' idem for Width.
ratio = width / 600
width = 600
height = height / ratio
End If
' create a new picture with height/width ok.
picture2 = New Bitmap(CInt(width), CInt(height))
picture2 = CType(picture1.GetThumbnailImage(CInt(width), CInt(height), Nothing, Nothing), Bitmap)
' ----------------------------------------------------------------------------------------
' Stock picture (blob) in SQL nop_image
' ----------------------------------------------------------------------------------------
Dim myCmd As SqlCommand
Dim myCmd2 As SqlCommand
Dim ms As New System.IO.MemoryStream
Dim arrPicture() As Byte
' Modify this line with your connection string.
Dim myConnection = New SqlConnection("Data Source=MyIP\SQLEXPRESS;Initial Catalog=MyNopBase;Integrated Security=False;Persist Security Info=False;User ID=MyUser;Password=MyPassword;Connect Timeout=120")
myConnection.Open()
myCmd = New SqlCommand("SET IDENTITY_INSERT Nop_Picture ON ; insert into Nop_Picture (PictureId,PictureBinary,Extension,IsNew)values (@PictureId, @PictureBinary, @Extension, @IsNew)", myConnection)
myCmd2 = New SqlCommand("SET IDENTITY_INSERT Nop_ProductPicture ON ; insert into Nop_ProductPicture (ProductPictureId,ProductID,PictureID,displayOrder) values (@ProductPictureId, @ProductID, @PictureID, @DisplayOrder)", myConnection)
If Not IsNothing(picture2) Then
picture2.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
If ms.Length > MAX_IMAGE_SIZE Then
' error....
' TODO.....
End If
arrPicture = ms.GetBuffer()
myCmd.Parameters.Add("@PictureBinary", SqlDbType.Image).Value = arrPicture
Else
myCmd.Parameters.Add("@PictureBinary", SqlDbType.Image).Value = DBNull.Value
End If
myCmd.Parameters.Add("@PictureId", SqlDbType.Int).Value = ids(i)
myCmd.Parameters.Add("@Extension", SqlDbType.Char).Value = "image/jpeg"
myCmd.Parameters.Add("@IsNew", SqlDbType.Bit).Value = False
myCmd2.Parameters.Add("@ProductPictureId", SqlDbType.Int).Value = ids(i)
myCmd2.Parameters.Add("@ProductId", SqlDbType.Int).Value = ids(i)
myCmd2.Parameters.Add("@PictureId", SqlDbType.Int).Value = ids(i)
myCmd2.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = 1
myCmd.ExecuteNonQuery() ' Write Picture
myCmd2.ExecuteNonQuery() ' Write Product - Picture relation.
Else
'---------------------------------------------------------------------------------------------------
' If CSV file it's ok, but image.jpg not found > write line in textbox in same format of this csv file.
' exemple of output if 4015.jpg not found :
'
' 7139 ; 4015 clover - BIAIS 25 mm ; c:\export\020111\images\4015.jpg ;
' you copy/paste in your text editor ; you save in csv & you reload! (after have found a good images).
result.Text = result.Text & ids(i) & ";" & name(i) & ";" & pictures(i) & vbCrLf
End If
Next
MsgBox("End...")
End Sub
End Class