Thread: Importing New Products - Can It Be Done?

After spending many hours trying to import product I'm unsure if it can be done at all. For newcomers (like myself) I'll list some of the issues I've had below and try not to repeat any information I've found in the manual or in the forum.

Excel
Can't make this work even though message states "Import Successful". First off you must use 2003 version of Excel, or save file as 2003 version. Otherwise you receive "OLE Error" message.

The Excel test file in the manual has multiple issues. It states you cannot delete the first 3 rows in the sample file but if you leave them in you'll receive an error during import. So you have no choice but to delete two of the three rows.

This leaves you with column headers like "ProductName", "ProductTypeID", etc. More problems.

ProductTypeID - This column header is specified in the sample Excel file. The example listed under this header notes "Generic Product". But it is unclear if you should really use these at all.

If you do a product export of some live products on your site you'll see the column header actually reads "producttype" and the cell value "generic". Hard to tell which format to use, the one from the sample file or the product export.

Category1 - This column header is specified in the sample Excel file. The example listed examples as "/Cat1/Cat2". Here again, when you do a product export it looks completely different. "category" is used as the header and cell values look like "\Cat1\Cat2;". Looks to me like there are issues with upper and lower case, forward slash vs back slash, semi-colon vs no semi-colon.

Don't know where a guy is to start sorting out the differences, but they go on and on. After spending a few hours trying to outguess the instructions vs. live examples, I decided to ditch the whole Excel approach and try Commander.


Commander
This product seems to do a relatively good job exporting and updating products. But I can't make it import to save my life.

They offer some videos that get you in the ballpark but still leave many questions unanswered. For example, when adding products you need to use the "insert" function. It goes under a header titled "Action". You can't use "insert" in the cell or even "Insert" with a capital "I". It only works if you use "Inserts", plural with a capital "I". Took me about 2 hours to figure that one out.

Then you have a wide array of issues just using a CSV file. Comma's and quotation marks are deadly. Just try an import a product with this in its description: Sizes Available: 1-1/2'', 2-1/4''

Massive pain.

So I've gone back to adding products one at a time by hand. I've penciled in my calendar that I should be finished by Dec 21, 2014 barring any unforeseen delays.

Hi

The spreadsheet, though it looks hard, is fairly easy if you get the format right.

It appears that you are on version 8 so I have added an example of a format that should work. Just look at the data entered and see how it maps to a site. Obviously its best to change the details to suit your own site (category etc).

The sheet must be called "Sheet1" in the bottom tab otherwise it will not import.

Have a go at importing two lines and let me know if I can be more help

Hi Dean,
Thanks for taking the time to help me out. The two products didn't import correctly, but at least it feels like I'm getting closer.

I didn't touch the 1st three header rows, just edited the data for my products. Does the attached file give you any clue?

Thanks for your help.

try this...

Also when you say its not importing are you getting any error message? Also, just checking, your xmlpackage. Is that one you have done yourself?

Not getting any error message, but the log file looks pretty strange. It's at the bottom of this note.

It did add one of the two products, the first one. Only some of the data was imported for that item. Price, for example, didn't make the trip.

The XML pkg "product.variantsinrightbar.xml.config" comes right from the sample Excel file in the manual. I just did a copy/paste exactly as in the sample.

Code:

Updating Product(B-2116 Robe Hook)
Executing SQL: update product set Name=N'B-2116 Robe Hook',SEName=N'b-2116-robe-hook',ImageFilenameOverride=NULL,ProductTypeID=1,Summary=N'Bobrick<br>Washroom<br>Robe Hook<br>',Description=N'This is some example text of a product description. You can also add html here if you wish',SEKeywords=N'Skateboard, tony hakws, board, inline',SEDescription=N'Skateboard, tony hakws, board, inline',SETitle=N'B-2116 Robe Hook | Bobrick',ManufacturerPartNumber=N'B-2116',ColWidth=4,XmlPackage=N'product.variantsinrightbar.xml.config',IsAKit=0,IsAPack=0,PackSize=0,SalesPromptID=1,Published=1,TrackInventoryBySizeAndColor=0,TrackInventoryBySize=0,TrackInventoryByColor=0,RequiresRegistration=0,MiscText=NULL,ExtensionData=NULL,SEAltText=N'B-2116 Robe Hook' where ProductID=330
>> Updated OK
Executing SQL: insert productCategory(productid,Categoryid) values(330,55)
Executing SQL: update productManufacturer set Manufacturerid = 16 where productid =330
Error on Node: Product:B-2116 Robe Hook: Node: ProductVariant, Required Field (Price) Is Missing!

Ok try changing the xml package to simple.product.xml.config and try again

More Notes:

Categories - This is quite a puzzle. When to use a forward slash and when to leave it out? Your EXAMPLE2 disregarded the parent category and only listed the subcategory with no slash at all.

I have some sub-cats that use the same name:
Schwinn -> 5 Speeds -> 10 Speeds
Rocket -> 5 Speeds -> 10 Speeds

Since the system keys in on CAT ID's and not their text names this didn't appear to be a problem before, but it does now.

I also notice another problem. The one product that did make it through the import process actually landed in the correct sub-category. However, when doing a product search in Admin the sub-cat shows as "Hooks," (with a comma). The product I hand entered only shows as "Hooks" (no comma following). That comma really made me nervous so I started snooping around.

Turns out the system created a new parent category called "Hooks" and assigned the product to it as well as the sub-cat called "Hooks". I had to go back in and fix the product mapping, then delete the newly created parent category.

I think the excel uupload will create a category or manufacturer if it does not already exist.

I did not know the structure so I would simply put the parent category then the sub categories in the relevant columns. In the 18 months we used the excel upload we never used and dividers (> or /)

Try creating the categories first then resetting the cache and reimport. This should work as there is not much wrong with your data

EXAMPLE2 Import
The product was added, no price and missing a few other details.

The XML package it was assigned was not simple.product.xml.config but rather the default, product.kitproduct.xml.config

Ok it could be that the wrong template is being used. I think the one I supplied earlier may have been from ASPDNSF V7.0.1.3

The newer versions have two extra columns SEAltText in columns AI and BL

Are these on the spreadsheet you are using? If not add these and try once more.-

No the template does not have "SEAltText" columns, so I will add them and keep trying.

Yes, that worked much better. Was just missing a few fields in the template like you noted.

The XML Package info didn't make it but most everything else did.

I sure appreciate the help. This is by far the best luck I've had so far and it gives me a fighting chance. You've saved me many hours of work.

Just look within the admin to see what xml package is assigned to the category and then use that on the spreadsheet. It simply writes the product to that category.

Any more help you need just let me know.

Still not getting the XML package information, whether I use that of the category or specific SKU. And that's not really a big deal considering I have to go back and hand edit each product to a certain extent anyway.

This whole process has been fascinating. Frustrating, but fascinating.

Just think, I'm fighting the same data import problems I fought in the 90's, hasn't changed in 15 years.

Hi

When you say "not getting the xml package information" what exactly do you mean?

Are the category package xml types the same that are on the spreadsheet?

Just seems wrong that you have to manually adjust these. Last year we imported around 13,000 products with the excel method and all went in fine.

Hi Dean,
I've been setting up all my categories and products as follows for XML:

#1: CAT = entity.gridwithprices.xml.config
#2: PROD = product.variantsinrightbar.xml.config

When I first tried importing I was using #2 as the package for all my products. They I saw your note that stated I should be using the category's xml package (#1) instead. Logically, it made no sense, but I tried it anyway. Same result.

All products default to "product.kitproduct.xml.config".

Does the xmlpackage you're trying to assign actually exist?
Check for spelling & capitalisation - maybe spaces in the field in excel?

If all else fails - you could just rename your desired xmlpackage so it becomes alphabetically first (or replace product.kitproduct.xml.config with your desired package) - then it would be assigned as the default.


TTFN

BFG

I know the XML Pkgs already exist because I'm using them with many other products and categories. I'll try the renaming idea, it sounds like a good test.

It would be a nice feature to bulk edit things like XML packages and other commonly used features.

I'm Submitting a bug report with this as well...

I've been exporting products with excel since version 5.9 but in 8.0.1.4 I am getting header rows and dimensions imported as products/categories. I think the import script is either missing or adding a column...

The exact same spreadsheet works perfectly fine in v 8.0.1.2

What does the converted xml file import file look like for your imports? A Excel file get converted to xml file and should be there if importing from excel. The xml files are saved to the images folder. An example file name would be ExcelImport_310201193039AM.xml. Looking at the file should at least give better insight as to what columns may be off.

I'm not using XML as the file type but using CSV instead. For all I know that may be the biggest problem right there because using CSV is a nightmare.

What import process/page are you using then? I don't recall a import that added products in a CSV format out of the box. Sure Commander uses CSV does but based off the conversation it looked trying to troubleshoot issues with importing products from the importProductsFromExcel.aspx page. Sorry for the confusion.

I've tried both Excel and CSV imports.

When using the Excel import template a good portion of the data works well. But there are problems with assigning categories, the description field and a few others.

On the other hand, CSV works OK for other items. So I've used a combination of both to get the job done.

Here's the thing . . .

The entire process of importing and exporting should be easy for everyone and come standard with a new installation. Two clicks of the mouse and you're done. Instead we're required to jump through major hoops by editing XML packages, making SQL queries and spending extra money on new software.

Want to setup a feed for Google Base? Good luck.

It seems the thought process is, "Well, no one really every imports, exports or uses Google Base. No need to support those functions."

But I have a solution - I'm taking up a collection to send a calendar to ASPDNSF. Seems they could benefit greatly by gaining the knowledge that it's 2011, not 1995.

Are you able to give an example spread sheet, import and converted xml file? Also can you name a few fields past the description isn't updating correctly? I think I should be able to help identify the fix for some of those issues.

Importing/exporting is shown big deal otherwise WSI wouldn't be pushed as much as it has/is if that wasn't the case. The issue comes down more to WSI isn't easy for a basic user to use in the current out of the box interface. This partially due the fact that WSI is a service that can be integrated into many other programs/interfaces.

All my example spreadsheets contain none of the problem fields such as:
Description
Summary
Meta Tags

I deal with these individually, by hand, since they can contain HTML, commas, quote marks, etc. To build a feed for Google Base, for example, I'll start with multiple export files, both CSV and Excel. Then I bring it all into Excel where I can manipulate the fields.

Right now, I only have about 400 products so it can be done within a couple of hours. The fact that it takes more than 30 seconds as with some of my other sites is quite a mystery to me.

I've spent a great deal of time checking into WSI by reading the manual, forum, etc. Can't find a single example, screenshot or even a basic overview on how to begin. So I gave up on that one.

Ok just to boil this down to the most basic level that I can. So we are not confusing each other. What exact issues are you having importing the attached example excel file (example3.zip)? If you are having an issue could include you please include the converted xml file and import log? Being able to see these two files will help me understand where the issue is.

That particular file gave me the least problems of all. And the problems boil down to a couple of key issues:

1] Cannot include any HTML
2] Cannot map to a Category

HTML - My summary field is used to add notes under the picture of an item and is centered. The HTML looks like this:
<div align="center">Bobrick<br>
Nickel Plated<br>
Robe Hook<br>
</div>
My hunch is the quotation marks in "center" is causing the issue. So I just deleted the <div> portions and at least some of the data made the trip. The <br> HTML code didn't seem to cause a problem. I don't even try to import the Description field as it always contains a bunch of HTML.

CATEGORIES - Never have resolved this issue. In this example a parent category exists called "Bobrick", with "Hooks" as a sub-cat.

If I populate the Category1 filed with Bobrick/Hooks I recive an error on import. So I got rid of the parent portion and just used Hooks. That got rid of the error and the products were imported.

But that created it's own new problem. The system created a new Hooks category and put the products there. Now I had duplicate categories so I re-mapped each product then deleted the new category created by the system.

Having to delete new categories after import makes me real nervous. On one of my earlier attempts to import it created a new category that I cannot delete. It appears to be soft deleted, but can't make it go away completely. And my system has never been the same since. These days when I create a new category I have to plan for it a few hours ahead. I create the new category, then go work on something else for several hours. When I come back the newly created category has finally taken effect and I can add products as normal. Until then, it doesn't appear in the tree, even if I rest cache.

The Example3 file was uploaded on 5-18 and I've attached both the xml and log files.

The XML file (ExcelImport_05182011.xml) is a bit of a mystery.

Let's say I try 3 different import attempts on the same day. Instead of creating 3 different XML files, one for each attempt, it only creates one for the day. Each successive attempt appears to overwrite the previous one for the day. Makes it impossible to go back and dig into the data at a later date. That is why the attached ZIP file does not include the data from Example3.

It's all one big puzzle wrapped in an enigma.

Hmm when I said import log I meant the import.htm file in the images folder not the IIS log. Sorry for the confusion. Since you did an import after making the example xml file not match the html file. Again sorry for the confusion.

I went and checked based off the provided xml file. I noticed that the category mapping appears to be split between two cells. Making it map two the catagory of Bobrick and Hooks. Is this the desired effect?

The html in the summary issue not working issue I didn't see to be an issue on my local testing so I'm not certain but it may be a code difference. If you can provide a copy of the import.htm file in the images folder after doing the import I should be able to better understand what is going on.

It is very kind of you to take this extra time to help me. I really appreciate the help.

A] Category Mapping - my intention was to map to a single category only, though it was a sub-category called "Hooks" under the parent category "Bobrick".

B] Import.htm - I've attached the file and took a good look at it too. What a puzzle.
import.zip

For one thing, I don't see any category mapping at all in the file. Now it could be I tried to import without any mapping at all, but I don't recall ever using that approach. I've tried a little of everything since I've had so many problems, but I'm pretty sure I always assigned products to a category.

I've noticed in the "Images" folder there are a number of Excel files listed there. Now I'm wondering if the whole process workes something like this:

1] Excel file gets loaded to "Images" folder by the system as a ".xls" file (Excel 2003)
2] The system then parses that file
3] That information is used to create the "import.htm" file
4] The "import.htm" file is then converted to an XML file, called "ExcelImport_Today'sDate"
5] The system then uses that XML file for the actual importing

The import.htm contains a whole load of SQL insert statements to create your products, variants, and mappings. The categories must have already been previously created during another import. Your mappings are created by statements like this:

Code:

insert productCategory(productid,Categoryid) values(334,54)

where it maps product # 334 to category # 54. The process will have looked these up based on the content of your category columns in your import spreadsheet. If the category didn't exist (by name) it would have created a new one.

The import process is more like.

1. The excel file is uploaded to the images folder
2. The file is then converted to a xml file
3. The xml is then ran through the import logic
4. Detailed results are saved to the import.htm file.
5. If there wasn't major errors with the import then the import.htm file is then linked on the importProductsFromExcel.aspx page for review. The name of the hyper link should look something like "View Import Log".

The import.htm is mostly the exact sql statements used to update the database. There are a few cases where it won't just sql. If the import.htm does not have **COMPLETED** in that is a tale tale sign there are other issues.

This import.htm file you gave isn't exactly the same as the xml file. The reason I can tell is because the xml file you gave only had one product in it. While the import.htm file has more than one product logged.

Also just looking the import log it looks like you set the xmlpackage to entity.gridwithprices.xml.config which I'm not too certain would show the product correctly. It looks that you are using the product.variantsinrightbar.xml.config on your site. So I would recommend setting the xmlpackage to product.variantsinrightbar.xml.config in the excel spreadsheet.

I know your webite URL since the you gave out the IIS logs. For that reason you may want to remove that attachment. Since that gives a bit more information than you may like.

Great information, a few more things are starting to make sense.

The import.htm file still doesn't make sense, but that's only because it is updated for each new import. So the trick seems to be:

1] Try an import file
2] Download the import.htm file and inspect it

Then repeat the process for each import. Otherwise, you might end up doing what I did, try a bunch of imports on a single day and only look at that file at the end of the day. It only gives info on the most recent attempt.

You shouldn't need to download the import.htm file. Just view the page it by clicking it the link that shows up after doing an import. Looking at the import.htm page for at least the completed statement after every import is a good idea.

But to recap on issues happening. You said you are getting an error when trying to import when the mapping is set to Bobrick/Hooks. What does this error say?

Well, the error actually says that no price was assigned, even though my price field always contains a value. My template for imports must be missing a field or have some other problem causing all data in subsequent fields to be invalid.

- - - - - - - - - - - - - - - - - - - - -

As for assigning categories, I've given up on that completely. After trying various methods while importing a single product it never comes out right. This causes more problems than it is worth. As an example:

1] Created new category called "Mop Holders"
2] In the template field for category entered "Mop Holders"
3] Tried to import a single product and the system created a duplicate category with the same name
4] Tried another import, this time with the value "/Mop Holders", following the example in the manual
5] Import log said it worked fine, but the product data was no where to be found

Since there aren't any other options, I'm back to building products within Admin, one at a time, by hand. As horrible as that sounds it is a better use of my limited time.

I finally had a clean import process, which was achieved by solving two problems. First, the SEAltText field (the first one) seemed to be overlapping the empty, shaded field used as a spacer. That fixed all my variant data being in the wrong field.

Next, made a work around for the category issue:

1] Plan ahead. Since the system is going to duplicate an existing category, don't even make the category in the first place.

For example, if you have a parent category called "ACME" and want to add a sub-cat called "Watches", don't create the sub-cat. Let the system do it for you during import.

Setup an import for all the products that will be under "Watches". During the import, "Watches" will be created as a parent category. Then simply go into Admin and edit the category so it's parent is "ACME". Saves a few steps and potential headaches.

Now that I've accomplished Step One, I'm going to be brave and try to add HTML in the Description field.