CSV import problems

I have a goal that I hope is realistic: I would like to keep my entire (small) shop, with 10 categories, 16 sub-categories, and 47 variants, all in one spreadsheet, just as exported by the HS > Products "Export" function. This is for efficiency's sake and to keep all the information organized and updated in one place.

Here is the workflow I am trying to create: I would like to simply open this file, modify anything necessary such as price or inventory changes, and upload the entire file back again. I would keep this downloaded and modified CSV file as my "working" file for the shop and make almost all changes in the shop directly to this file, and then upload the entire file again. Modifications could include adding language translation columns for JoomFish for use in the site's active languages, and also adding new variants, etc.

Q1) Is this a workable workflow within HS, or should I be going about it differently?

I haven't seen this in the documentation, but here are the observations I've made about the exported CSV file of my shop.

Original file as exported was:

• Encoding: UTF-8
• Separator: “;” (semicolon)

Organization of original file (your mileage may differ):

• Row 1 has the column headers
• Row 2 is the “mother row”; it is empty except for the text “product category” in col. AO categories
• Rows 3-12 are the product categories
• Rows 13-27 are the subcategories. Some are parents of variants and some are products themselves*.
• Rows 28-204 are the variants**.

Product categories:

• all columns are empty except col. AM parent_category, AN categories_image and AO categories
• AM parent_category text is “product category” (without the quotes)

Subcategories (same as base products in my shop):

• listed as “main” in col. N product_type
• AM parent_category is blank
• col. AO categories lists the category it’s associated with
• see col. F product_code; it contains the code that variants use to link to it

Variants:

• Associated with their product by means of col. B product_parent_id

Where to enter key info:

• Page title: col. AL product_page_title
• Variant weight:*** col. AX peso (last column: text entered = label of variant choices of prod. page)
• Header on product page AND link text on Subcategory listing page: col. C product_name
• Body text on product page: col. D product_description
• Inventory: product_quantity

* I have now changed this in my "working" CSV file so that subcategories with only one variant are set up like that; no subcategories are now purchasable products themselves. To do this, I zeroed out the dimensions, product_quantity and price_value and set the rest of the columns to be like other subcategories, copying and pasting the appropriate values. I.e., they are set up just like a subcategory/product that has more than one variant.

** This included a lot of "ghost" (automatically generated) variants I didn't want or need. I simply deleted those lines in my working CSV file so that I have only the variants I want. I'm not sure if this is the right procedure.

*** Weight is the only variable for the products in my shop.

Q2. Are these observations correct? Am I missing anything I need in order that when I upload the file again, the variants will link correctly with their products/subcategories, and product/subcategories with their categories?


My immediate problem is the CSV file I'm working with now on a site that urgently needs to launch (yesterday).

(I have a thread open in the Bug Report section about an error notice I get now when I export all my shop's products. However, I'm not working with that exported file at the moment, I'm only using it for reference. My problems are with a previous CSV export that had no error notices, which I've modified and am now trying to import again.)

I've turned Site > Global configuration > Server > Server Settings > Error Reporting to Maximum, which was useful at one point for eliminating some errors, but now is no longer showing any relevant errors.

On importing the whole CSV file, I get a cryptic message, usually stating something like, "X products found: X new products imported, X invalid product code, X already existing products." If there are problems anywhere, it doesn't tell me specifically what they are. In any case, the results have been very spotty: Some products are added correctly and everything is perfect. Others are completely ignored. For some products, masses of new "ghost" variants are generated (one for every possible product weight I entered into the system). Etc. So everything needs to be gone through carefully again and cleaned up, defeating the whole purpose of the exercise, which is to efficiently upload all the data in the shop at once.

Q3. Shouldn't this procedure work? Is it a bug or is there something else I should be doing to prepare for the upload? (I am half-tempted to first back up and then delete all the products, categories, etc. before uploading to see if that would work, but I don't relish the idea of restoring from backup and hoping for the best.)

This being the case, I have taken to making mini-CSV files from my main working file and trying to import only the variants associated with a single product/subcategory at a time to see if I can isolate the problem and develop a workable way to upload my information.

I am now trying only to get four variants uploaded with their full, correct information, and have them associate themselves correctly with their "mother" product/subcategory.

Before I upload, I delete all the existing variants of the product and ensure that it has the correct name, description and product code and that price, dimension and quantity information are all zeroed out. I either enter "manage variants" and delete them all; or I do that and also delete the variable "weight" to prevent HS from re-generating "ghost" variants; or I go back out to the products list (HS Products > Products, listing all sub-elements of "Product category," i.e. everything) and delete all the variants by hand there.

Then I upload. The message when I upload "4 products found: 0 new products imported, 0 invalid product code, 4 already existing products" doesn't really tell me much, so I investigate further.

On investigation, I find:

• 4 correct, new variants created, but associated with a new, blank product/subcategory that has no information (no name, no description and the critical information all zeroed out).
• Name of each new variant has oddly been taken not from the product_name column but from product_name|en-GB, one of my translation columns for JoomFish
• If I eliminated the variable "weight" before, the product/subcategory remains with no variants. If I didn't eliminate the variable, I get about 40 automatically generated variables, one for each weight of every possible item I have ever put into the system.

Q4. Any ideas on how I can get the items I upload to associate themselves with their product/subcategory correctly?
Q4a. Am I right in deleting the existing variants before uploading? How about the variable?

For reference, I'm attaching the mini-CSV file I've been experimenting with trying to upload, just trying to get these four variants right. I figure once I get this right, uploading the correct info for the rest of the shop will be a breeze.

Thanks Nicolas or anyone for any help on this issue.

Update: I bit the bullet and did the following, with fair-to-middling success:

Back up several different ways
Leave all my categories as they were (their info was fine)
Delete ALL the subcategories/products and their variants
Upload my master CSV file containing all information

The result was perfect, almost... The only problem was that the subcategory/product names all came out in English rather than in Spanish, the site's Default language as established in Extensions > Language Manager. (The English translation also came out in English, so I have English twice).

Any ideas on this?

Should my product_name column be titled product_name|es-ES instead, in a multilingual environment?

Hi there,

Glad to see you make it works, csv import can be a powerful too when it's well used
About your name problem, you have to set the name for your default language in the product_name column (so spanish names here) and use product_name|en-GB for your english names.
Hope it will make everything work like you want!

Hi Eliot,

Actually what's strange is that that is how it was: product_name contained the Spanish and product_name|en-GB contained the English, but after I uploaded it, in the Spanish language part of the site, the product names mysteriously came out in English and the en-GB tab area of the HS Buiness interface was empty. Maybe this is a legitimate bug. ??

Also, I must say that repeatedly deleting all products and variants and uploading the same full-shop spreadsheet to fill it with updated information did not give the same results each time. It was the same spreadsheet except for a few price changes. (I had to do this 3x yesterday). One the second occasion, some variants became unlinked from their products, and on the third occasion, all of them did. In the front end, the variants showed up on the correct page as expected, but selecting one of them would crash the site! So in the end, I restored from backup and entered the price variations by hand.

Perhaps another bug? Or what do you think?

Hi dforest,

i've been struggling with csv's a lot lately, you should be very carefull with the product id's and product_parent_id's, one day i have been a whole day trying to figure out why my variants where in the wrong places after import, i have sometimes to remove manually the products directly in phpMyAdmin but then i realized some little very important things:

1-is very good to make imports in not too big batches, if you got problems during import is easier to find the error on your csv.

2-Is good if your "variant"s are near the "main" product they belongs too in the spreadsheet.

3-Remember that if the the main product have variants you must specify which are the default ones in the "main" product row, if you don't specify them the product page crashes.

4-Be very careful with repeated values on the "product_id" , "product_parent_id" and "product_code", repeated product codes makes HS dizzy during import.

5- use colors in your spreadsheets to make them easier to read while debugging, i usually have a main .ods file and that is the file i work on, when i'm ready to import i just export as csv from there.

this are he most commons mistakes i have encountered during my imports and sometimes they are really difficult to find out in big csv's thats why i prefer to have one csv per product category (it can be tricky when you have products with multiple categories).

protorob wrote:
3-Remember that if the the main product have variants you must specify which are the default ones in the "main" product row, if you don't specify them the product page crashes.

Hi Protorob,

Thanks so much for this post. I've done a small experiment following all your advice on a product whose variants were missing for some reason. (The product was showing up as "free" in the shop, and one person managed to buy it! Ugh!)

The key piece of advice for me was your #3 above. In the variable column (mine is "peso" for the product weight in Spanish) the main product had an inexistent variant weight. I changed this to what should be the default choice, one of the real, existing variants, and it imported perfectly. Your checklist allowed me to spot this very quickly.

Another thing I discovered is important:

When exporting your file as a CSV (I use OpenOffice .ods files as you do), don't set the Text Delimiter option to ' (a single quote character); keep the default option of " (double quote).

It seems that since most of the numerical cells in our product spreadsheets start with the single quote ' character, presumably to be interpreted correctly in HS with the correct number of decimal places, etc., that if you export the file with this same character as the text delimiter, HS gets confused and does not separate the records properly. At least it gave me an error message until I changed it back to the default and re-imported the file.

Ciao e grazie molto

Hi Dforrest,

I understand what you experienced with the free products on non defined variants, i have been struggling with that last week, fortunately Xavier pointed me in the right direction and with a couple of lines of code in the product/listing_price view he solved that annoying problem, you can see the solution here:
Not definde variants returns free product

About the configuration of the csv i usually use this configuration, i use openOffice, i think it is better for handling csv's:

-On the OpenOffice "Save As" Dialog, when you choose csv there are some checkboxes that activates, the first one should say something like "Automatic File Extension" then there is a greyed one that says "Protect with password" or something like that and there is a third one that in italian says "Modifica Impostazioni Filtro" (that should be something like "modify export options") if you check that box when you click "Salva" it asks you if you want maintain the current format or save as ODS, you click Maintain and the another popup comes up with csv's options.

I usually choose:

UTF-8 for the char type
Field separator ";" (punto y coma)
Text Separator " (Comillas)

then i select the "save cell contents as shown - (Salva el contenido de las celdas como mostrado)
And let the next checkbox unselected

This way have been working for me very good and i allways work in the ODS file that i have color coded (sort of)

One thing that is still tricky to me is the decimal handling on the product_price column because if you use the comma HS understand it as multiple values (and this is very useful when assigning multiple categories to a product) so you need to use the period (punto) as decimal separator.
The problem i have found is that when i receive the excel files from my suppliers they always use the comma in their price column and may be they have also defined the cell as € value so a simple copy/paste of this values onto the HS import file can be dangerous.

So i sorted this by:

1. Set the format of the whole product_price column as number and the language as "Italiano (Svizzera)" because fortunately swiss buddies uses the period as decimal separator.

2. When Copy/Pasting your values don't use the simple Ctrl+C - Ctrl+V , instead use Special Paste or Ctrl+Shift+V and in the popup select only numbers and uncheck everything else.

This way, when you paste the values in your preformated swiss number cell they will show with period separator, no mater if the original cell was formated with commas and euro symbol.

Try and see.

Have a good day.

En el futuro si quieres charlar un rato sobre HS podeis escrivirme en español via PM
Hasta luego