Product import, attributes formatting in Excel

[jriven]

I could not figure out why my export of my product database in business catalyst could not then be imported after making a few minor changes. After removing any data from cells that were optional and I thought potentially causing the problem, I finally deduced that the product attributes column was to blame. So I suspect that it has something to do with the format of the data in that cell. Here is the data:

Size*|5|Y:XL|,L|,XXL||US/23,S|,M|,XXXL||US/24;Color*|5|Y:Black|/images/products/clothing/t-shirts/shabby-road-back-black.jpg|US/21,Gray|/images/products/clothing/t-shirts/shabby_road_gray_back.jpg|US/21

What if anything is wrong with the syntax above? This is exactly how the product export output the data. I added the attributes manually, that's it.

Shouldn't the system export data that can then be easily imported?

This really creates multiple problems for managing products and their inventory in bulk. Will I have to remove the product attributes column every time I do an upload from now on?

[serenityjane]

My response may be a bit late for you, but for future reference...

We had a similar issue yesterday that I am still yet to find any documentation on. However, we resolved the issue by removing any unnecessary colons or semi colons from the attributes field. For example, we had "Please select:" which we replaced with "Please select".

What surprised us about this was that the file we were importing was simply a backup of the original export. In other words, BC cannot interpret a file that it has exported. Very frustrating and I would love to hear from a BC expert about why this is so!

[Liam Dilley]

We been on BC about this for a while now.

There are several issues with Data and when you export at the moment.

Attributes should not allow a set of characters etc for one example BUT the admin is currently allowing you to make those in the admin. This then is incorrect in the system and as soon as you export the data will be incorrect.

From looking at the above example it has commas which is one of them, you can not use a comma in an attribute name, this will create one of the problems.

Also when text fields are marked as such on web apps (text not numbers) BC is not parsing this information properly to the CSV export and spreadsheet software can treat them as numbers.

BC have been made aware of the issues, I will direct them to this forum so they can see others have this as well and we at Pretty will try push them for a resolution but I know they are busy with the stuff they announced on the blog.

[jriven]

Sometimes I feel like the person prioritizing the releases is Rob Ford, as in they are smoking something funny.

As cool as it is to have an Open Platform, some of the basic functionality of BC does not work and is very embarrassing to present the work-arounds to clients. It makes a developer look unprofessional and the project ill-conceived because the promised ability to edit product information in a spreadsheet is not even working correctly.

CSV files should surround text strings with quotes to eliminate any trouble with special characters. Why not have a comma in an attribute name?

Will Adobe even take a comprehensive look at their product and fix the core functionality? Or will they simply continue to release "cool stuff" our clients don't care about?

Here's a tip though: ABANDON EXCEL and use Apache OpenOffice Calc. You get much cleaner data and get to specify your delimiters, encase your strings in quotes, etc. Oh and its free.

[jriven]

4 months ago I complained about this. Has this been resolved? NO

The Product Import is still completely broken if you have attributes in you attributes column.

[Liam Dilley]

That is not true, What your saying there is it wont work with attributes in there, this is not the case.

I have worked on 4 jobs with attributes without illegal characters and the imports have gone through.

Yep there is two core cases..

1. IF you do it wrong

2. If you have illegal characters in there that BC wont accept and it will cause problems

In terms of attributes, but aside from that it is working.

[jriven]

Ok Liam,

Why not be helpful?

Please post the correct code for uploading attributes that have added PRICE and IMAGES with ENABLED VARIATIONS. I'd be happy to concede defeat if you can show us all how this works.

I have been using this (which is what BC exports) which throws an error when uploaded:

Size*|5|Y:Small|,Medium|,Large|,X-Large|,2X||US/2,3X||US/3;Color*|5|Y:Grass|/images/products/clothing/t-shirts/50thghostgreen.JPG,Cumin|/images/products/clothing/t-shirts/50thshostred.JPG

So how do you write this so it can be uploaded?

Here is a screenshot of the attributes. We have not inserted any special characters into attribute names. Just a "/" in between the US and 2 to add 2 bucks for larger sizes.

Thanks, JR

[Liam Dilley]

02.00 I would have done.

[jriven]

What difference would that make? That is not what the exmple says to do. Please clarify.

Are you stating that instead of "US/2" you would just put "02.00"? Will BC know it's still US currency? Are you stating that the slash is the illegal character that is causing all the trouble?

[Liam Dilley · Accepted solution]

No, I mean US/02.00 but how you did it is also valid if it renders when you do it in the admin.

If your struggling with imports then make up an example in the admin - Export it and then you can see the structure, you then just copy that and apply it to other products.

Just be mindful NOT to use illegal character sets and stick to Numbers for prices and letters for other attribute data to avoid any issues with the import/export (Which there are in this regard)

[jriven]

Liam,

Export from Admin???? Are you seriously suggesting I have not already done this?? Have you not read this discussion??? I did as you suggested before we even began this discussion months ago. Please stop beating around the bush. Do you have a solution or not??? If not, please stop wasting my time.

AGAIN:

I have been using this (which is what BC exports) which throws an error when uploaded:

Size*|5|Y:Small|,Medium|,Large|,X-Large|,2X||US/2,3X||US/3;Color*|5|Y:Grass|/images/products/clothing/t-shirts/50thghostgreen.JPG,Cumin|/images/products/clothing/t-shirts/50thshostred.JPG

So how do you write this so it can be uploaded?

[klubjunky]

it's been exactly a YEAR now..

any updates or fixes to the absolutely painful updating of ecommerce

please let me know