How to import products from an Excel Spreadsheet

I tried to populate my catalog by importing an excel spreadsheet and I ran into issues.  I solved them by performing the following steps:

Create a bogus product in your catalog.
Then Export your product to an Excel Spreadsheet to give you a base template for importing (Catalog, Products, Manage Products, Export to Excel).
Copy and paste the appropriate columns from the spreadsheet you want to import to the spreadsheet you just exported.
Import the spreadsheet (Catelog, Products, Manage Products, Import From Excel).

OK, Here is where the fun part begins.  The import kept bombing out.  To debug it, I ran it locally.
nopCommerce logs the errors (System, Log) so I opened up the line of code that was bombing out.
Turns out that it didn't like the creation date that I copied as a string.  It was looking for a double number.
The code is in the Libraries/nopServices/ExportImport/ImportManager.cs file.

I commented out the CreateOnUtc date line and replace it with DateTime.now

  //DateTime createdOnUtc = DateTime.FromOADate(Convert.ToDouble(dr["CreatedOnUtc"]));
                  
                    DateTime createdOnUtc = System.DateTime.Now;

I run the import again and now it bombs out on the 4th row.  I check the error log file again.  Turns out that I have weights and measurements for some records and not for others.

In the same ImportManager.cs file, I change the code for the weight, length, width, height to:

// LJD 11/08/2011
                    decimal weight = 0.00M;
                    if (!String.IsNullOrEmpty(dr["Weight"].ToString()))
                    {
                        weight = Convert.ToDecimal(dr["Weight"]);
                    }

                    decimal length = 0.00M;
                    if (!String.IsNullOrEmpty(dr["Length"].ToString()))
                    {
                        length = Convert.ToDecimal(dr["Length"]);
                    }

                    decimal width = 0.00M;
                    if (!String.IsNullOrEmpty(dr["Width"].ToString()))
                    {
                        width = Convert.ToDecimal(dr["Width"]);
                    }

                    decimal height = 0.00M;
                    if (!String.IsNullOrEmpty(dr["Height"].ToString()))
                    {
                        height = Convert.ToDecimal(dr["Height"]);
                    }
                    //decimal length = Convert.ToDecimal(dr["Length"]);
                    //decimal width = Convert.ToDecimal(dr["Width"]);
                   // decimal height = Convert.ToDecimal(dr["Height"]);

Ta Dah!  2 weeks of catalog setup done in 2 hours.

Hi ,

I am also having problems importing excel files as the template has got too many entities which i am not even using ,

my product contains just the minor information which are :
Code, description , cost , Antacide, I know there are some columns in the template which are mandotory like seo , keywords date , etc but what about weight, size etc .. is it ok to remove these as my client doesnt want these or it will break up d code ... any suggestions

Look at what I wrote above.  I didn't run into any issues with SEO and Meta Tags being mandatory.  But, if there is a column that is mandatory, I populated the spreadsheet by going to the top cell for that column, clicking Shift, End, Down Arrow to select the spreadsheet column, and Clicking the Fill, Down to copy whatever is in the top cell of the spreadsheet all the way down.  So if you have a spreadsheet column that is mandatory where you want the value to be True, then set the top cell to True and copy it for the entire column.

It's a tedious process, but, I just imported a couple of thousand items that would take weeks to do manually.

Also, if you run into issues, you may have to modify the code like I did above.

Thnx for a quick reply , one more thing , how did u find the category and manufacture id's , coz i will have to link the products with the manufacture and categories

Truth be told.  I did the same thing.  I manually entered the Categories and Manufacturers and manually assigned them through the backend.  I'll have to look at the code and see if it can be determined on the fly.

BTW: When I said I manually assigned the Manufacturers and the Categories to the Products, I didn't go product by product. I went to the Manufacters and Categories and used the Product search on the Products Tab to assign the relationships.

Just look at the number in the url and it will mean the category #.

I have a problem exporting as I always get _x00D_ all over the place.  I just do a find and replace for now.

You shouldn't have to do that.  I am going to look at the code and see if it trys to associate the Category and Manufacturer by name when you import a spreadsheet.  I can see how lookup by Category Name could be an issue if you have sub-categories with the same name.

Well I have just made a spearate excel sheet which shows the id of categories , sub categories and manufacture , then link that ids in the spreadsheet category id , and manufacture id table , subcategories can be written like "parentcategory;subcategory".

Cheers

Oh, that's a good idea.