Hi,
How to import data from XML file or Excel as you write into features?
I don't found any in backend.
Thank you
How To Import Data Via XML/Excel?
- 62
- 464
- 7/13/2010
- United States
This is what i did for importing records. It is very basic but something to start with.
I did a product export of the default install of nop which gives you an excel worksheet named "Products" with a dozen or so products.
I created a new workseet named "Defaults"
I then copied the first and second rows from "Products" worksheet to the "Defaults" worksheet which gave me the title row and one product row.
Deleted all rows except the first title row in worksheet "Products".
Since i already have a SQL 2000 database with 1400 products in it, i used SQL DTS or Export wizard to export those products to the same excel file that contains the above two worksheets. This created a new worksheet called "Items" or whatever your table name is.
From the developer toolbar in excel, open VB editor.
Then it is simply writing each field to the "Products" worksheet from "Items" worksheet and in case there is no field that is similar, write that field from the "Defaults" worksheet...
Public Sub CopyData()
Dim shtItems As Worksheet
Set shtItems = Sheets("Items") 'ref Items WS
Dim shtProducts As Worksheet
Set shtProducts = Sheets("Products") 'ref Products WS
Dim shtDefaults As Worksheet
Set shtDefaults = Sheets("Defaults") 'ref Defaults WS
Dim TotalRows, x, txtDesc
TotalRows = shtItems.UsedRange.SpecialCells(xlLastCell).Row 'get the number of rows in the exported items WS
For x = 2 To TotalRows Step 1 ' starting in second row after title row
shtProducts.Range("A" & x).Value = shtItems.Range("C" & x).Value ' Copy Name from ItemID in Items WS
shtProducts.Range("B" & x).Value = shtItems.Range("D" & x).Value ' Copy ShortDescription from ItemName
shtProducts.Range("C" & x).Value = shtItems.Range("E" & x).Value ' Copy FullDescription from ItemDescription
shtProducts.Range("D" & x).Value = shtDefaults.Range("D2").Value ' Copy ProdTypeID from defaults since their is nothing in items that matches
shtProducts.Range("E" & x).Value = shtDefaults.Range("E2").Value ' Copy TemplateId from defaults etc
' the rest is similar to above depending on what fields you have that transfer to nop fields, otherwise use a nop default
shtProducts.Range("BB" & x).Value = shtDefaults.Range("BB2").Value ' Copy CreatedOn from defaults
Next x
End Sub
Import the excel file into nop...
The above is very basic and most will want to do it another way depending on the source of your unique products, but this at least gets descriptions, prices, etc imported. Also this is only the second time i have played with excel so if there is a better way in excel please speak up.
I did a product export of the default install of nop which gives you an excel worksheet named "Products" with a dozen or so products.
I created a new workseet named "Defaults"
I then copied the first and second rows from "Products" worksheet to the "Defaults" worksheet which gave me the title row and one product row.
Deleted all rows except the first title row in worksheet "Products".
Since i already have a SQL 2000 database with 1400 products in it, i used SQL DTS or Export wizard to export those products to the same excel file that contains the above two worksheets. This created a new worksheet called "Items" or whatever your table name is.
From the developer toolbar in excel, open VB editor.
Then it is simply writing each field to the "Products" worksheet from "Items" worksheet and in case there is no field that is similar, write that field from the "Defaults" worksheet...
Public Sub CopyData()
Dim shtItems As Worksheet
Set shtItems = Sheets("Items") 'ref Items WS
Dim shtProducts As Worksheet
Set shtProducts = Sheets("Products") 'ref Products WS
Dim shtDefaults As Worksheet
Set shtDefaults = Sheets("Defaults") 'ref Defaults WS
Dim TotalRows, x, txtDesc
TotalRows = shtItems.UsedRange.SpecialCells(xlLastCell).Row 'get the number of rows in the exported items WS
For x = 2 To TotalRows Step 1 ' starting in second row after title row
shtProducts.Range("A" & x).Value = shtItems.Range("C" & x).Value ' Copy Name from ItemID in Items WS
shtProducts.Range("B" & x).Value = shtItems.Range("D" & x).Value ' Copy ShortDescription from ItemName
shtProducts.Range("C" & x).Value = shtItems.Range("E" & x).Value ' Copy FullDescription from ItemDescription
shtProducts.Range("D" & x).Value = shtDefaults.Range("D2").Value ' Copy ProdTypeID from defaults since their is nothing in items that matches
shtProducts.Range("E" & x).Value = shtDefaults.Range("E2").Value ' Copy TemplateId from defaults etc
' the rest is similar to above depending on what fields you have that transfer to nop fields, otherwise use a nop default
shtProducts.Range("BB" & x).Value = shtDefaults.Range("BB2").Value ' Copy CreatedOn from defaults
Next x
End Sub
Import the excel file into nop...
The above is very basic and most will want to do it another way depending on the source of your unique products, but this at least gets descriptions, prices, etc imported. Also this is only the second time i have played with excel so if there is a better way in excel please speak up.
