How to prepare an Excel file for import

-- url of the page with the problem -- :http://tbcp.siamwide.com/administrator/index.php?option=com_hikashop
-- HikaShop version -- : 2.3.1
-- Joomla version -- : 3.3.0
-- PHP version -- : 5.4.28
-- Browser(s) name and version -- : Chrome
-- Error-message(debug-mod must be tuned on) -- : Error_message

I am not sure the best way to proceed with this (see attached Excel Price List from Polycom)
But it would seem it would be prudent to firstly parse the existing sheet into something more palatable for HikaShop.

Any guidance here would be gratefully received.
I am no programmer but reasonably competent in Excel...

Hi,

Have you read that documentation ?
hikashop.com/support/support/documentati...hikashop-import.html

It explains how the csv import work in HikaShop, and how to format the csv file.
You can to create example products in your shop, then export them to have a real example of a valid csv file.

Hi Xavier,
I see by creating an export file AFTER I make some valid Category and Product entries into HikaShop, i can make a 'template' of how to set up my source .csv for import - clever solution, thanks I'll try your suggestion now.

Although I expect the way the Categories and Sub-Categories have been formatted in the source spreadsheet may be problematic, I'll see.

Hi,

Indeed, you will have to manage the csv file before importing it in order to have the correct format.

Since the Category, sub-Category and 2nd level Sub-Category are currently spread over columns A-F throughout the spreadsheet, perhaps it would be best to firstly add 3 columns at the LH side and move them to say Category = column A, Sub-Category = column B and Sub-Category 2 = column C - so that before converting to .CSV every item to be parsed would have its own column?

Sorry, but I don't really understand what you mean. Could you write a more detailed example ?

Hi,
I have attached the Polycom Price list.
A main Category,
REALPRESENCE ROOM
Can be seen on Row 1

2 Sub-categories;
HDX Executive Collection Stand Systems
Service - HDX Executive Collection
Can be seen on Row 3 and Row 9;

A further nested Sub-Category of Service - HDX Executive Collection;
Installation Services
is on Row 14

As these are the Categories and Sub-Categories for all the Polycom products, we will need them to be created and ready for monthly updates from Polycom

Hi,

You can add one category per row in the csv. They must be filled in the column "category" and "parent_category" depending on the three.
Example:
parent_category;category
;realpresence room
realpresence room;HDX Executive Collection Stand Systems
realpresence room;Service - HDX Executive Collection
HDX Executive Collection Stand Systems;Installation Services

You just have to keep this format, but the categories can be in row1, 3, 9 and 14. You just need to have the parent category before the child one.

Hope this will help you.

Hi Xavier,

I have manually edited the original Polycom price list to prepare it for (monthly) export to .csv.
I am no expert at Excel, and i have assumed what has taken much manual cut and pasting, can be achieved either by VB or scripting (not sure what approach is preferable).

The original May Polycom price list and the manually modified spreadsheet are both attached here. If i have it correct, i will employ an Excel expert to created the necessary conversion code.

There are no attachments on your message.

This is the original Polycom price list (unmodified)


This is the same price list with a page of the same list I have modified (prior to changing to .csv format)

having trouble attaching files..Cant see why, Trying

just the modified version

Hmm,
Trying again



to attach Source (unmodified) and Target (manually modified ) versions of the Polycom price list.

My apologies, I forgot to zip these spreadsheets in .xls;

You're almost there with your manually edited CSV.
For the column names, you need to use the ones from HikaShop.
So Category should actually be parent_category, child category should be categories, long description should be product_description RRP should be price_value and product id should be product_code
Also, for the price, you need to remove the $.
And finally for the last product, the grandchild category should actually be in the child category column and the child category should be in the category column.
To simplify the categories, the best is to pre created your categories via the interface of HikaShop and then just specify the direct category of the product in the "categories" column. That should be simplify your script for generating the CSV file in the correct format.

Hi,

I have made the recommended changes, but feel there is still an issue with what I labelled as "grandchild" category.
To explain, throughout the entire Polycom price list there are 3 levels of category.

In that price list (see attached) all categories are placed in column A
The highest level can be identified by the fact it is all in upper case. (e.g. REALPRESENCE ROOM)
The second level is identified by have a grey background (e.g. HDX Executive Collection Stand Systems)
The third level can be identified by the fact it has no formatting (e.g. Polycom Branded Services)




Therefore in the Manually Modified v02 (see attached) I have labelled them;
parent_category (example REALPRESENCE ROOM)
categories (example HDX Executive Collection Stand Systems)
child_category (example Polycom Branded Services)




Is this correct and ready for conversion to .csv for HikaShop?

here again is the 2nd Manually Modified V02

Hmm something wrong here after attaching the properties show a 0 file size for Manually Modified v02 zipped - since the zipped file is only 22 bytes - might be too small?
I have placed it here in my Dropbox account in both .xls and zip formats.

db.tt/pTJNmyB2

db.tt/mGYrAejj

Hi,

Here is the file I would recommend:



As you can see, I left only the categories column for the categories. This assumes that you already have the categories created via the menu Products>Categories.
I've also fixed the product_description column name.

Hi Nicolas,

Thnaks for that, it is very much appreciated.
However i am still not sure all is OK.

In the case of the category;
Polycom Branded Services
This is a subset or child category of;

Service - HDX Executive Collection

Which in turn is a subset or child category of
HDX Executive Collection Stand Systems

This pattern is repeated throughout the entire Polycom Price List

As the price list is over 2,000 line items it is important that we can by programming, automate the monthly updates as they arrive from Polycom.