code to load inventory and categories

I'm writing some code to import products and categories into nopcommerce. I am running into problems with the foreign key constraints.

DELETE FROM Nop_Product
SET IDENTITY_INSERT Nop_Product On
DBCC CHECKIDENT ('Nop_Product', reseed, 0)

Products.ProductManager.InsertProduct(itemi.ItemLookupCode, itemi.Description, itemi.SubDescription1, "", 0, 0, false, "", "", "", "", false, 0, 0, true, false, DateTime.Now, DateTime.Now);

I get:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Nop_Product_Nop_ProductTemplate". The conflict occurred in database "store", table "dbo.Nop_ProductTemplate", column 'ProductTemplateId'.
The statement has been terminated.


Is there a better approach to doing this? I would like to initially truncate the tables or at least delete the data and reset the ident. So I can load a fresh set of categories and products in a batch from another database.

Thanks.

Any luck with this?  I have a DB of about 4000 items I'd like to be able to import as well.

Hi. Not a whole lot of progress. I did find an xml import stub class in the nop code but nothing in the current release yet. With the FK contraints a direct approach has been difficult without having to disable the contraints on the tables. I've just started looking at building a ssis integration project with visual studio. You?

I really haven't had time to sit down and mess with this.  I was looking at the productmanager insert product code and I guess I'm just going to open my excel spreadsheet and call that method for each row in the spreadsheet.  That will get the bulk of the info in the db and then I'll have to go back and add the pictures and SEO and link the categories and stuff like that manually.  Any thing that I can do programmatically at this point is better than nothing.

Ok so here is what I came up with so far.  My code is not pretty.  This is actually the first time I've programmed in C#.

This is my import method in ImportManager.cs.  I'm returning a DataTable basically so that if I have errors, I'll know exactly which records to look at.  I've also hard coded several values for taxcategory as well as published, deleted and several others.  They were all going to be the same in my situation.  I'm also just doing the default Product Variant.  I only have a handful of products that have different variants.

public static DataTable ImportProductsFromExcel(string FilePath){
            // Connect to the Excel Spreadsheet
             string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties=Excel 8.0;";
    
             // create your excel connection object using the connection string
            OleDbConnection objXConn = new OleDbConnection(xConnStr);
            objXConn.Open();
    
             // use a SQL Select command to retrieve the data from the Excel Spreadsheet
             // the "table name" is the name of the worksheet within the spreadsheet
             // in this case, the worksheet name is "Sheet1" and is coded as: [Sheet1$]
             OleDbCommand objCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", objXConn);
    
             // create a DataReader
            OleDbDataReader reader = default(OleDbDataReader);
            reader = objCommand.ExecuteReader();

            // used for testing your import in smaller increments
            int counter = 0;
            string Name; 
            string SKU;
            decimal Price = 0;
            decimal OldPrice = 0;
            decimal Weight = 0;
            decimal Width = 0;
            decimal Length = 0;
            decimal Height = 0;
            DataTable errDT = new DataTable();
            errDT.Columns.Add("row", typeof(int));
            errDT.Columns.Add("SKU", typeof(string));
            errDT.Columns.Add("Name", typeof(string));
            errDT.Columns.Add("Error", typeof(string));
    
            while (reader.Read()) {
                // counter to exit early for testing...
                counter = counter + 1;
                Name = "";
                SKU = "";
                Price = 0;
                OldPrice = 0;
                Weight = 0;
                Width = 0;
                Length = 0;
                Height = 0;
                if (counter > 0)
                {
                    try
                    {
                        Name = Convert.ToString(reader["name"]);
                        string ShortDescription = Convert.ToString(reader["ShortDescription"]);
                        string FullDescription = Convert.ToString(reader["FullDescription"]);
                        string AdminComment = Convert.ToString(reader["AdminComment"]);
                        int ProductType = 1;
                        int Template = 4;
                        bool ShowOnHomePage = false;
                        string MetaKeywords = Convert.ToString(reader["MetaKeywords"]);
                        string MetaDescription = Convert.ToString(reader["MetaDescription"]);
                        string MetaTitle = Convert.ToString(reader["MetaTitle"]);
                        string SEName = Convert.ToString(reader["SEName"]);
                        bool AllowCustomerReviews = true;
                        int RatingSum = 0;
                        int TotalRatingVotes = 0;
                        bool Published = true;
                        bool Deleted = false;
                        DateTime CreatedOn = DateTime.Now;
                        DateTime UpdatedOn = DateTime.Now;

                        // Insert Product
                        Product product = ProductManager.InsertProduct(Name, ShortDescription, FullDescription, AdminComment, ProductType, Template,
                            ShowOnHomePage, MetaKeywords, MetaDescription, MetaTitle, SEName, AllowCustomerReviews, RatingSum, TotalRatingVotes,
                            Published, Deleted, CreatedOn, UpdatedOn);

                        SKU = Convert.ToString(reader["SKU"]);
                        string ManufacturerPartNumber = Convert.ToString(reader["ManufacturerPartNumber"]);
                        bool IsDownload = false;
                        int productVariantDownloadID = 0;
                        bool IsShipEnabled = true;
                        bool IsFreeShipping = false;
                        bool IsTaxExempt = false;
                        int TaxCategory = 8;
                        int StockQuantity = Convert.ToInt16(reader["StockQuantity"]);
                        int MinStockQuantity = 0;
                        int Warehouse = 0;
                        bool DisableBuyButton = false;
                        bool RequiresTextOption = false;
                        if (reader["Price"] != DBNull.Value)
                        {
                            Price = Convert.ToDecimal(reader["Price"]);
                        }
                        if (reader["OldPrice"] != DBNull.Value)
                        {
                           OldPrice = Convert.ToDecimal(reader["OldPrice"]);
                        }
                        if (reader["Weight"] != DBNull.Value)
                        {
                            Weight = Convert.ToDecimal(reader["Weight"]);
                        }
                        if (reader["Length"] != DBNull.Value)
                        {
                            Length = Convert.ToDecimal(reader["Length"]);
                        }
                        if (reader["Width"] != DBNull.Value)
                        {
                            Width = Convert.ToDecimal(reader["Width"]);
                        }
                        if (reader["Height"] != DBNull.Value)
                        {
                            Height = Convert.ToDecimal(reader["Height"]);
                        }
                        int PictureID = 0;
                        int DisplayOrder = 1;

                        LowStockActivityEnum lowStockActivity = (LowStockActivityEnum)Enum.ToObject(typeof(LowStockActivityEnum), 0);

                        ProductVariant productvariant = ProductVariantManager.InsertProductVariant(product.ProductID, string.Empty, SKU, string.Empty,
                            string.Empty, ManufacturerPartNumber, IsDownload, productVariantDownloadID, IsShipEnabled, IsFreeShipping, IsTaxExempt,
                            TaxCategory, StockQuantity, MinStockQuantity, lowStockActivity, Warehouse, DisableBuyButton, RequiresTextOption,
                            string.Empty, Price, OldPrice, Weight, Length, Width, Height, PictureID, Published, Deleted, DisplayOrder, CreatedOn, UpdatedOn);

                    }
                    catch (Exception exc)
                    {
                        errDT.Rows.Add(new object[] { counter, SKU, Name, exc.Message });
                        
                    }
                }

             }
             //If counter > 2 Then ' exit early for testing, comment later...
             // Exit While
             //End If
            
             reader.Close();
             errDT.Rows.Add(new object[] { counter, "Rows Addedd", "","" });
             return errDT;
      }

This is an admin page I created called ProductImportExcel.aspx.  It's pretty  simple.  A FileUpload control and a gridview to display any errors.

ASPX page:

<%@ Page Language="C#" MasterPageFile="~/Administration/main.master" AutoEventWireup="true" CodeFile="ProductImportExcel.aspx.cs" 
Inherits="NopSolutions.NopCommerce.Web.Administration.Administration_ProductImportExcel" ValidateRequest="false" %>

<asp:Content ID="c1" ContentPlaceHolderID="cph1" Runat="Server">
<asp:Panel ID="PanelUpload" runat="server" Visible="True">
            <asp:FileUpload ID="FileUploadExcel" runat="server" />
            <br />
            Please select an Excel file to import:<br />
            <asp:Button ID="ButtonUploadFile" runat="server" 
                Text="Import File" onclick="ButtonUploadFile_Click" /><br />
            <asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="ErrorPanel" runat="server" Visible="False">
    <asp:GridView ID="ErrorGrid" runat="server">
    </asp:GridView>
            
</asp:Panel>
</asp:Content>

and Code Behind:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.MobileControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using NopSolutions.NopCommerce.Common.Categories;
using NopSolutions.NopCommerce.Common.Manufacturers;
using NopSolutions.NopCommerce.Common.Media;
using NopSolutions.NopCommerce.Common.Products;
using NopSolutions.NopCommerce.Common.Promo.Discounts;
using NopSolutions.NopCommerce.Common.Tax;
using NopSolutions.NopCommerce.Common.Templates;
using NopSolutions.NopCommerce.Common.Utils;
using NopSolutions.NopCommerce.Common.Warehouses;
using NopSolutions.NopCommerce.Common.ExportImport;
//using NopSolutions.NopCommerce.Web.Administration.Modules;


namespace NopSolutions.NopCommerce.Web.Administration
{


    public partial class Administration_ProductImportExcel : BaseNopAdministrationPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void ButtonUploadFile_Click(object sender, EventArgs e)
        {
              if (FileUploadExcel.HasFile) {
                 try {
                     // alter path for your project
                     FileUploadExcel.SaveAs(Server.MapPath("~/Resources/UploadedFiles/ExcelProductImport.xls"));
                     LabelUpload.Text = "Upload File Name: " + FileUploadExcel.PostedFile.FileName + "<br>" + "Type: " + FileUploadExcel.PostedFile.ContentType + " File Size: " + FileUploadExcel.PostedFile.ContentLength + " kb<br>";
                 }
                 catch (Exception ex) {
                     LabelUpload.Text = "Error: " + ex.Message.ToString();
                     ProcessException(ex);
                 }
                 DataTable dt = ImportManager.ImportProductsFromExcel(Server.MapPath("~/Resources/UploadedFiles/ExcelProductImport.xls"));
                 PanelUpload.Visible = false;
                 ErrorGrid.DataSource = dt;
                 ErrorGrid.DataBind();
                 ErrorPanel.Visible = true;
                 System.IO.File.Delete(Server.MapPath("~/Resources/UploadedFiles/ExcelProductImport.xls"));
             }
             else {
                 LabelUpload.Text = "Please select a file to upload.";
             }
        }
}
}

Like I said, I'm open to any suggestions or improvements that can be made on this.  This was just a simple way for me to get my products into my site easily.

Thanks,
WaltD

Oops, in ImportManager you need to test to make sure StockQuantity != DBNull as well.  I missed that one.

Thanks for the post, however I have gotten everything to work except 1 issue I am erroring out:

Have any ideas why 'ImportProductsFromExcel' would not be defined in ImportManager.cs even though it's there?

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0117: 'NopSolutions.NopCommerce.Common.ExportImport.ImportManager' does not contain a definition for 'ImportProductsFromExcel'

Source Error:



Line 45:                     ProcessException(ex);
Line 46:                 }
Line 47:                 DataTable dt = ImportManager.ImportProductsFromExcel(Server.MapPath("~/Resources/UploadedFiles/ExcelProductImport.xls"));
Line 48:                 PanelUpload.Visible = false;
Line 49:                 ErrorGrid.DataSource = dt;

Hi. I am getting the same error. Is there an answer to this problem please? Thanks.

Hey guys, sorry, I just saw this thread with your questions.  I'll look at it and see if I can figure out what's going on.  

Just for clarification, after adding the copied the code into the importmanager.cs file, you recompiled nop.common and made sure you had your updated dll in the bin folder of your website?

Thanks,
WaltD

Hi WaltDjr,

Can you email me a sample excel data file @ '[email protected]'. Thanks a lot!

Ben