I,
i need to reformat a spreadsheet and convert to .csv so i can automatically prepare monthly prices list for an ECommerce site using HikaShop shopping cart plugin for Joomla.


I need recommendation as to the most elegant way to archive this so there is the minimum monthly manual input required in the conversion.

A sample of the price list from Polycom has been attached and placed in a Dropbox folder for DL.
Here are the instructions from HikaShop support;

"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."

Attached Files

Polycom Price List May 2014.xlsx (434.6 KB, 17 views)

Hi,

Not sure if I can help. But, with your information I can not see the correlation between Excel and the .cvs file you want to create. Can you provide an example of the "CVS" file?

the text from HikaShop in my post describes in detail the required .csv format. you can view and DL a copy of the file that needs to be parsed here;

https://dl.dropboxusercontent.com/u/...ay%202014.xlsx
.

I agree with Charles, Tim; there's a lot of information missing. Let's start here:

1) You say you need "the most elegant way to archive this". That sounds like you're already able to create the .csv, and all you want to know is how to save it for long-term storage.

But if that were true, you wouldn't be showing us the instructions from HikaShop support. 2) You also say you want to do the conversion with "the minimum monthly manual input required". So now I'm guessing you know how to create the .csv, but it's labor-intensive; you want an easier way to do it.

If so, that's a worthwhile question, but we need to know how you create it in the first place, the hard way I mean. You tell Charles that the HikaShop documentation "describes in detail the required .csv format"; but if so, the details are clear only to someone who already knows more than I do. For instance, the instructions speak of a "category"; but what is a category, and why would I want to create one? I don't see anything named "category" in the .xlsx you posted.

Then, too, the instructions start by saying I can "add one category per row", but finish by saying "the categories can be in row1, 3, 9 and 14". All in all, it's not just that I don't understand what the instructions are saying, it isn't clear to me what I would be trying to create. It's easy to create a .csv, but clearly this .csv has to have a particular layout.

How do you do it manually, then?

Hi,
I think you need more knowledge on how ECommerce works within Joomla.
OK here goes,
Firstly you need Categories and if necessary Sub-Categories then within these Categories you place the individual Products.
e.g.

Category = Shoes
sub Category = Mens Shoes
2nd sub category = Mens Sports Shoes.
After categories are created, you then add the individual Products.

I think the above example is easier to follow than the Polycom Price list.

In order to export these results to the E-commerce Joomla plugin called HikaShop, you finally need to take the very simple step of converting the resultant spreadsheet to a .csv format.

However, if you have no experience in creating E-commerce sites from external spreadsheets, I can understand your initial confusion.

My requirement is to create (VBScript or Macro?) that will automate the conversion to a column and row format as outlined by the HikaShop support team,


Best regards,

Tim

Hi,

I think in order to get the data so that it works with the system. You need to know what the end format of the Excel sheet should look like.
The example of the file you posted has rows of data.
How would one determine as you put it "Parent/Child" relation.
If we can understand the format then we may be able to create a code for it.

I'm thinking you only want data in 2 columns.
Column A has the "Parent category" and column B has the "Sub category".

Hi,
I take your point. I will send a manually altered page of the source spreadsheet to Support at Hikashop to ensure it will be in the correct final format.

Hi,

Thanks for the feed back. I thought this was a "Dead" issue.
If we can still help let us know.

This project is taking some time, BUT i now have more definitive info from support at HikaShop.

Here is their suggestions;

"I know you can't add the products one at a time. That's the goal of the import system to add a whole bunch of products together in one go. So it doesn't change what I said so far.

And you can't pre create the categories via the interface ? Will the categories change in the future ?
If yes, then I do understand that you want to have the CSV embed the hierarchy of the categories in it.
In that case, you can have empty lines at the begining of your CSV with just the categories hierarchy and then just mention the categories in the products."

Here is an example of the Categories and 2 level of sub-Categories using an easier to understand product example set;

"parent_category,categories,product_name
FOOTWEAR,FOOTWEAR_MENS,
FOOTWEAR,FOOTWEAR_WOMENS,
FOOTWEAR_MENS,FOOTWEAR_MENS_DRESS,
FOOTWEAR_MENS,FOOTWEAR_MENS_SPORTS,
,FOOTWEAR_MENS_DRESS,product 1 in the dress category
,FOOTWEAR_WOMENS,product 2 in the womens category"

In practice, the actual Categories have more obscure titles but follow the same concept of Master Category and up to two levels of sub-Category.

In the original Polycom Conference room Equipment Spreadsheet all/only the Categories are in column A and they can only (I think) be differentiated by the cell formatting thus;
Main Categories are all caps.
Child Categories have a leading cap followed by lower case, and have a shaded cell background and Grandchild categories have leading cap followed by lower case and no cell background.
I cannot see any other way to differentiate one category from another.

All together the original spreadsheet has several hundred+ categories and 2,000+ Products.

The original Excel sheet is updated every month, and therefore the updating of the .csv must occur automatically, and can be run through CRON to further automate the upload to HikaShop.

Hi,

When I look at the file you posted I think I can see what you are referring to.
Row 1 has the "Parent Cat", row 3 has what I think is the Sub Cat. but in rows
4 thru 7 has "Description" will that be part of the Cvs file?

Hi,
Yes, you have it. so the requirement is firstly to parse out (at the beginning of the .csv version) all Categories, Child Categories and Grandchild categories (around 200 in total) and then add in the Products (around 2,000), placing them in their correct Category or sub-Category.
Link to the original Polycom Price list (May 2014) here;

https://dl.dropboxusercontent.com/u/...ay%202014.xlsx

Hi,

You do not want the data in columns "C hru F"?
And if you have a sub cat ie "Polycom Branded Services" that does not have a "Description". You do not want that in the cvs file. But if there is a Description you want that.

Hi,
I am just in the process of changing PC's so it will take a little time to answer your questions with clarity and examples, thanks a bunch for staying with this -much appreciated.

Not a problem. Just got the wife a new "IMac". Took awhile to transfer files.
But all is well and the new Mac is working great. However I can only look at it now and not touch. And, too still have to use my old "PC".

Hi,

Here is the final desired file in .csv format after the Categories and Product data columns have been added

Attached Files

final CSV file ready for upload.zip (570 Bytes, 19 views)