Sub ConvertMagentoToShopify()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim iRowSource As Long, iRowDest As Long, iCol As Long
Dim colBaseImage As Long, colDescription As Long, colCategories As Long
Dim magentoRow As Range
' Imposta le intestazioni del foglio shopify
shopifyHeaders = Array("Handle", "Title", "Body (HTML)", "Vendor", "Product Category", "Type", "Tags", "Published", "Option1 Name", "Option1 Value", "Option2 Name", "Option2 Value", "Option3 Name", "Option3 Value", "Variant SKU", "Variant Grams", "Variant Inventory Tracker", "Variant Inventory Qty", "Variant Inventory Policy", "Variant Fulfillment Service", "Variant Price", "Variant Compare At Price", "Variant Requires Shipping", "Variant Taxable", "Variant Barcode", "Image Src", "Image Position", "Image Alt Text", "Gift Card", "SEO Title", "SEO Description", "Google Shopping / Google Product Category", "Google Shopping / Gender", "Google Shopping / Age Group", "Google Shopping / MPN", "Google Shopping / Condition", "Google Shopping / Custom Product", "Google Shopping / Custom Label 0", "Google Shopping / Custom Label 1", "Google Shopping / Custom Label 2", "Google Shopping / Custom Label 3", "Google Shopping / Custom Label 4", "Variant Image", "Variant Weight Unit", "Variant Tax Code", "Cost per item")
' Assicurati che il foglio di lavoro di Magento sia quello corretto
Set wsSource = ThisWorkbook.Sheets("Magento")
' Configura il foglio di lavoro di destinazione per Shopify
On Error Resume Next
Set wsDest = ThisWorkbook.Sheets("Shopify_CSV")
On Error GoTo 0
If wsDest Is Nothing Then
Set wsDest = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsDest.Name = "Shopify_CSV"
Else
wsDest.Cells.Clear
End If
' Imposta le intestazioni di Shopify nel nuovo foglio di lavoro
For iCol = LBound(shopifyHeaders) To UBound(shopifyHeaders)
wsDest.Cells(1, iCol + 1).Value = shopifyHeaders(iCol)
Next iCol
' Ottieni gli indici delle colonne
colBaseImage = Application.Match("base_image", wsSource.Rows(1), 0)
colDescription = Application.Match("description", wsSource.Rows(1), 0)
colCategories = Application.Match("categories", wsSource.Rows(1), 0)
' Inizia il loop attraverso le righe del foglio di Magento
iRowSource = 2 ' La prima riga di dati dopo le intestazioni
iRowDest = 2 ' Prima riga disponibile in Shopify_CSV
While Not IsEmpty(wsSource.Cells(iRowSource, 1).Value)
' Controlla se le colonne 'base_image' e 'description' sono popolate
If IsEmpty(wsSource.Cells(iRowSource, colBaseImage)) And IsEmpty(wsSource.Cells(iRowSource, colDescription)) Then
If IsEmpty(wsSource.Cells(iRowSource, colCategories)) Then
' Se anche 'categories' è vuoto, salta al prossimo prodotto
GoTo SkipToNextRow
End If
End If
' Mappa i dati da Magento a Shopify se la riga è valida
' Handle
wsDest.Cells(iRowDest, 1).Value = wsSource.Cells(iRowSource, Application.Match("url_key", wsSource.Rows(1), 0)).Value
' Title
wsDest.Cells(iRowDest, 2).Value = wsSource.Cells(iRowSource, Application.Match("name", wsSource.Rows(1), 0)).Value
' Body (HTML)
wsDest.Cells(iRowDest, 3).Value = wsSource.Cells(iRowSource, Application.Match("description", wsSource.Rows(1), 0)).Value
' Vendor (per ora impostato su "dafare", ma pronto per essere collegato a una futura colonna "vendor")
wsDest.Cells(iRowDest, 4).Value = "dafare"
' Se la colonna "vendor" sarà aggiunta in futuro, utilizza il seguente codice (attualmente commentato):
' wsDest.Cells(iRowDest, 4).Value = wsSource.Cells(iRowSource, Application.Match("vendor", wsSource.Rows(1), 0)).Value
' Product Category (lasciato vuoto)
wsDest.Cells(iRowDest, 5).Value = ""
' Type (basato sulla colonna "categories", prendendo il secondo valore)
Dim categories As String
Dim categoryArray() As String
categories = wsSource.Cells(iRowSource, Application.Match("categories", wsSource.Rows(1), 0)).Value
categoryArray = Split(categories, "/")
If UBound(categoryArray) >= 1 Then ' Controlla se esiste almeno un secondo elemento
wsDest.Cells(iRowDest, 6).Value = Trim(categoryArray(1)) ' Prende il secondo valore
Else
wsDest.Cells(iRowDest, 6).Value = "" ' Se non esiste, lascia vuoto
End If
' Tags (usando il valore dalla colonna "categories", sostituendo '/' con ',')
Dim tags As String
tags = wsSource.Cells(iRowSource, Application.Match("categories", wsSource.Rows(1), 0)).Value
tags = Replace(tags, "/", ",")
wsDest.Cells(iRowDest, 7).Value = tags
' Published (impostato su "TRUE")
wsDest.Cells(iRowDest, 8).Value = "TRUE"
' Option1 Name e Option1 Value
Dim skuTot As String
skuTot = wsSource.Cells(iRowSource +1, Application.Match("sku-tot", wsSource.Rows(1), 0)).Value
If Right(skuTot, 6) = "UNIC" Then
' Se "UNIC" è presente negli ultimi 6 caratteri
wsDest.Cells(iRowDest, 9).Value = "Default Title" ' Option1 Name
wsDest.Cells(iRowDest, 10).Value = "Default Title" ' Option1 Value
Else
' Se "UNIC" non è presente
wsDest.Cells(iRowDest, 9).Value = "Taglia" ' Option1 Name
Dim skuTotArray() As String
skuTotArray = Split(skuTot, "-")
wsDest.Cells(iRowDest, 10).Value = skuTotArray(UBound(skuTotArray)) ' L'ultimo elemento di sku-tot per Option1 Value
End If
iRowSource = iRowSource + 1
' Passa alla riga successiva
SkipToNextRow:
iRowSource = iRowSource + 1
wend
' Salva il foglio Shopify_CSV come CSV
'Dim csvFilePath As String
'csvFilePath = ThisWorkbook.Path & "/ShopifyImport.csv"
'SaveAsCsvWithQuotes wsDest, csvFilePath
' Conferma che l'operazione è stata completata
'MsgBox "Conversione completata! Il file è stato salvato in: " & csvFilePath, vbInformation
End Sub
Sub SaveAsCsvWithQuotes(ws As Worksheet, filePath As String)
Dim r As Long, c As Integer, rowString As String
Dim fileNo As Integer
fileNo = FreeFile
' Apri il file per la scrittura
Open filePath For Output As #fileNo
' Itera attraverso le righe e le colonne del foglio di lavoro
For r = 1 To ws.UsedRange.Rows.Count
rowString = ""
For c = 1 To ws.UsedRange.Columns.Count
rowString = rowString & """" & ws.Cells(r, c).Value & """"
If c < ws.UsedRange.Columns.Count Then
rowString = rowString & ","
End If
Next c
' Scrivi la riga nel file CSV
Print #fileNo, rowString
Next r
' Chiudi il file
Close #fileNo
End Sub
Write, Run & Share VB.net code online using OneCompiler's VB.net online compiler for free. It's one of the robust, feature-rich online compilers for VB.net language, running on the latest version 16. Getting started with the OneCompiler's VB.net compiler is simple and pretty fast. The editor shows sample boilerplate code when you choose language as VB.net. OneCompiler also has reference programs, where you can look for the sample code to get started with.
OneCompiler's VB.net online editor supports stdin and users can give inputs to programs using the STDIN textbox under the I/O tab. Following is a sample VB.net program which takes name as input and prints hello message with your name.
Public Module Program
Public Sub Main(args() As string)
Dim name as String = Console.ReadLine() ' Reading input from STDIN
Console.WriteLine("Hello " & name) ' Writing output to STDOUT
End Sub
End Module
Visual Basic is a event driven programming language by Microsoft, first released in the year 1991.
Variable is a name given to the storage area in order to identify them in our programs.
Simple syntax of Variable declaration is as follows
Dim variableName [ As [ New ] dataType ] [ = initializer ]
variableName = value
If condition-expression Then
'code
End If
If(conditional-expression)Then
'code if the conditional-expression is true
Else
'code if the conditional-expression is false
End If
If(conditional-expression)Then
'code if the above conditional-expression is true
Else If(conditional-expression) Then
'code if the above conditional-expression is true
Else
'code if the above conditional-expression is false
End If
If(conditional-expression)Then
'code if the above conditional-expression is true
If(conditional-expression)Then
'code if the above conditional-expression is true
End If
End If
Select [ Case ] expression
[ Case expressionlist
'code ]
[ Case Else
'code ]
End Select
For counter [ As datatype ] = begin To end [ Step step ]
'code
[ Continue For ]
'code
[ Exit For ]
'code
Next [ counter ]
For Each element [ As datatype ] In group
'code
[ Continue For ]
'code
[ Exit For ]
'code
Next [ element ]
While conditional-expression
'Code
[ Continue While ]
'Code
[ Exit While ]
'Code
End While
Do { While | Until } conditional-expression
'Code
[ Continue Do ]
'Code
[ Exit Do ]
'Code
Loop
Do
'Code
[ Continue Do ]
'Code
[ Exit Do ]
'Code
Loop { While | Until } conditional-expression
Procedure is a sub-routine which contains set of statements. Usually Procedures are written when multiple calls are required to same set of statements which increases re-usuability and modularity.
Procedures are of two types.
Functions return a value when they are called.
[accessModifiers] Function functionName [(parameterList)] As returnType
'code
End Function
Sub-procedures are similar to functions but they don't return any value.
Sub ProcedureName (parameterList)
'Code
End Sub