Trouble importing categories with csv file

[dooleymort]

I am using a demo versio nof the store with DataPort 7.0.2

I am having trouble formatting a csv file to import categories. I added some categories using the admin, just to export, and find out the format. I thought I had the file with my data correct, but when I import it fails with message:
No Product available for Import(Upload)
Is this a problem with my formatting?

The first line in the file has all of the fields:
CategoryId|"StoreId"|"ParentId"|"Name"|"Summary"|"Description"|"ThumbnailUrl"|"ThumbnailAltText"|"DisplayPage"|"Theme"|"HtmlHead"|"VisibilityId"|"MSExcelWorkAround"
When I go to the mapping options, it finds all of my fields in the Your Import Sheet column.
I attached my csv file as a text.

Anyone know what I'm doing wrong?

[ZLA]

Where did the vertical pipe characters come from? When I download I have commas (hence CSV). Try doing a replace all on your file from "\" to "," and see if that makes it work.

[mazhar]

Where did the vertical pipe characters come from? When I download I have commas (hence CSV). Try doing a replace all on your file from "\" to "," and see if that makes it work.

Most probably when downloading CSV dooleymort specified settings other then default. Default settings are " and ,. You can change this information on CSV export form in DataPort.

[dooleymort]

I did replace the comma with vertical pipe because that's how I get my data from another source. When I exported the categories, I sepcified vertical pipe instead of comma and it worked fine, it just seems that I can't import that way.

I am using the manual mapping and changing the comma to pipe, and when I go the the Your Import Sheet column all of my fields are there. I can match them up with the AbleCommerce fileds just fine. Then I get the error: No Category Available for Import(Upload).

I did replace the pipes with commas, and I still get the same error. Again, all of my fields are showing up in the mapping section.

What step am I missing?

[Naveed]

I examined your attached file. There is one formatting issue.. The data for first csv column/field is not properly quoted:

Code: Select all

CategoryId|"StoreId"|"ParentId"|"Name"|"Summary"|"Description"|"ThumbnailUrl"|"ThumbnailAltText"|"DisplayPage"|"Theme"|"HtmlHead"|"VisibilityId"|"MSExcelWorkAround"
700|"1"|"0"|"Bags/Carry Cases"|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"
...
...

Should be Like:

Code: Select all

"CategoryId"|"StoreId"|"ParentId"|"Name"|"Summary"|"Description"|"ThumbnailUrl"|"ThumbnailAltText"|"DisplayPage"|"Theme"|"HtmlHead"|"VisibilityId"|"MSExcelWorkAround"
"700"|"1"|"0"|"Bags/Carry Cases"|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"
...
...

I have tried with this change and was able to import the data...

[dooleymort]

Thank you. That helped. Now, CategoryId seems to be working, but none of the other fields. When I specify the file, it finds CategoryId only, and I get an error for every other field:

• While validating provided CSV data against the current template following error has been registered.:
  -----------------------
  
  Following columns in your provided CSV file does not match while using the current template:
  ----------------------
  |"StoreId"|"ParentId"|"Name"|"Summary"|"Description"|"ThumbnailUrl"|"ThumbnailAltText"|"DisplayPage"|"Theme"|"HtmlHead"|"VisibilityId"|"MSExcelWorkAround""700"|"1"|"0"|"Bags/Carry Cases"|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"
  
  To fix this problem you can either use manual mapping or can choose an appropriate custom template file.
  NOTE: DATA FOR UNMAPPED CSV COLUMNS WILL NOT BE IMPORTED
  
  
  You will not be able to import your csv data until you provide all the required csv columns. Following required columns are missing in your provided CSV file:
  ----------------------
  Name, ParentId

Then when I update the dext delimiter I get:

• ************** Exception Text **************
  System.ArgumentException: An item with the same key has already been added.
  at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
  at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
  at CommerceBuilder.DataClient.Csv.CsvReader.ReadNextRecord(Boolean onlyReadHeaders, Boolean skipToNextLine)
  at CommerceBuilder.DataClient.Csv.CsvReader.GetFieldHeaders()
  at v.d()
  at v.a(Object A_0, EventArgs A_1)
  at System.Windows.Forms.Control.OnClick(EventArgs e)
  at System.Windows.Forms.Button.OnClick(EventArgs e)
  at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
  at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
  at System.Windows.Forms.Control.WndProc(Message& m)
  at System.Windows.Forms.ButtonBase.WndProc(Message& m)
  at System.Windows.Forms.Button.WndProc(Message& m)
  at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
  at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
  at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  

I'm hoping this is a simple fix, like the missing quotes were.
Thanks again.

[Naveed]

While validating provided CSV data against the current template following error has been registered.:
-----------------------

Following columns in your provided CSV file does not match while using the current template:
....

The first error is basically a warning message that either your current template selection is not correct or your file does not contains valid data. This warning normally appears when you are using a text delimiter other then the standard "comma", or your current selection of delimiter is and text qualifier is different then used in csv data. Due to which the CSV engine was not able to parse the provided data correctly.

Regarding the 2nd error, although it is quite annoying but the simplest work around is to ignore the error and try again by selecting appropriate delimiter and clicking the "Update" button... If your fields are not mapped automatically then map them manually and continue with the import process.

[dooleymort]

Right. I did select the appropriate delimiter, click the "Update" button, mapped the fields manually and continued with the import process. I then get the No Category Available for Import(Update) error again.

[dooleymort]

I am specifying a CategoryId that does not exist. I am using the Id from my data feed. Is this the problem?
Is there a way to add a Category and specify it's Id?
Or, is there a better way to do this?

Thanks for all of your help,
Lila

[Naveed]

I think there may be some formatting problems with your finalized csv data as well... Can you please share it...

[dooleymort]

OK, thanks.
Here's the file I've got now (as a text file).

[Naveed]

I have examined the the provided csv file and I found that the following CSV records have problems:

"6010"|"1"|"6000"|"LCD Panels- 8 to 16""|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"
"6020"|"1"|"6000"|"LCD Panels- 17""|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"
"6025"|"1"|"6000"|"LCD Panels- 20" to 28""|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"
"6027"|"1"|"6000"|"LCD Panels- 19""|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"


"6050"|"1"|"6000"|"LCD Panels- 30" & Over"|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"

Problem here is that you are using double quote character with in your csv data without properly escaping it. For example, following category name contains double quote character:

LCD Panels- 20" to 28"

And it should be escaped like:

LCD Panels- 20"" to 28""

So, the above respective csv record will become like:

"6025"|"1"|"6000"|"LCD Panels- 20"" to 28"""|""|""|""|""|"CategoryList.aspx"|""|""|"0"|"Do not delete"

Key point is if you are using double quote as text qualifier in CSV data then you should properly escape all other double quote characters with in the csv data contents.

Fixing the above 4 csv records will fix your issue.

[dooleymort]

That was definitely the problem! They import now, but leave a lot of orphaned categories (but all parent categories are fine).
Some categories import just fine, and some are left out. I can't see a pattern as to which work, and which don't. It's not the same set each time, and if I try to import again, some are duplicated, again not all.

To start with, I didn't have any existing categories in the store. I am just trying to import my categories and parent categories.

Since I can't specify the CategoryId, how do I specify what the parentId is when I import?

Thank you again for all of your help.

[Naveed]

Since I can't specify the CategoryId, how do I specify what the parentId is when I import?

Off course you can specify the CategoryId, and also the parent Id based upon the category Id's. However it is not guaranteed that the specified categoryId's will be assigned to respective categories, because when importing new categories Id's are automatically assigned. But the key point is that the import routine is intelligent enough to correctly map the child categories to correct parents as specified in the CSV data.

So, you can import something like (omitting extra csv columns fields for simplicity) :

Code: Select all

"CategoryId", "Name", "ParentId"
"100","Parent Category","0"
"101","Child Category A","100"
"102","Child Category B","100"

And even if while importing new categoryId's are assigned to each category, both the "Child Category A" and "Child Category B" will be mapped under correct parent "Parent Category".

I think you were doing this correctly in the above attached csv files....

[dooleymort]

Yes. That is what I thought, the problem is that it doesn't work for all of the child categories.
It leaves a bunch orphaned.
What am I missing?
-Lila

[dooleymort]

Here is the text file of the errors when I import.
I imported to an empty store. There were no categories.
It left a lot of orphaned categories. Only one parent category imported without any child cateogries.
The rest imported some, but not all.

[Naveed]

Here is the text file of the errors when I import.
I imported to an empty store. There were no categories.
It left a lot of orphaned categories. Only one parent category imported without any child cateogries.
The rest imported some, but not all.

I have re-imported the file at my local system and I think it is a bug in import routine. It is not mapping the parents correctly between different chunks. However luckily there is a quick workaround for you:

As the problem is there between different chunks, so we can change the chunk size to import all categories in one chunk. Now suppose you have a total of 662 categories to import. Now if we change the chunk size to 662 we can import all categories in one chunk(in one go). Follow these steps to change the chunk size:
1. Connect the DataPort to the server.
2. Open the settings screen from "Options -> Settings" menu.
3. In the chunk size change the chunk size to "662" Or equal to number of categories in your csv file.
4. Click save button and now re-import your categories.

This will solve the above issue. However please keep in mind the following things:

1. Use a clean store, with no existing categories.
2. Properly backup your database before using the DataPort, so that undesired changes can be reverted back by re-storing the database.
3. Modifying the chunk size setting does not preserve over different sessions, if you want to use a chunk size other then default you have to change it each time you connect to your server.

[Naveed]

I have reported the above parent Id mapping issue as a new bug. Which can be tracked at:
http://bugs.ablecommerce.com/show_bug.cgi?id=8107

[dooleymort]

That did it!
All parent and child categories imported. None were skipped, none were orphaned.
Thanks for your help again and again.

Regards,
Lila