08-11-2020 05:41 AM
08-11-2020 05:41 AM
Please, I have thousands of items in Excel - products for eshop, in separate rows, to which I need to assign more categories in the category column - from the total number of about 120 categories with subcategories. Each item will be marked with multiple categories, in woocommerce format. Categories are separated by a comma and the path to the subcategory is indicated by the ">" character, example:
|1245||Surface Studio 2019||desktops, microsoft, computers > all in one|
The question is, how to do this as effectively as possible?
08-11-2020 08:46 AM
Without understanding how your category is input currently (by hand?), my initial thought is for you to set up individual columns for each category, desktop, microsoft, etc. Create as many columns as the maximum entry will use, then place a list using data validation in each cell of each column. Data validation will drag and drop. Go to each item and select their categories across the row, include a subcategory column and select the subcategory as well, and then concatenate them in the final "category" row in the format you described. For the data validation, you can create one list for the categories and one list for the subcategories. So using your example, if you created four columns, the first 3 with the categories listed, and the fourth with the sub-category listed, if the item was in cell b2, in cell "c2" you would select "desktops", in "d2" you would select "microsoft" etc. In the category column ("g2" in this ex.) you would have =concatenate(c2&", "&d2&", "&e2&" > "&f2). (You can also put the comma-space in a cell and reference it instead of writing it out). Also, putting the comma-space in its own cell gives you the option of an if statement to filter it out so you don't have something that looks like "desktops,,, >"
08-11-2020 09:09 AM
Some things which are obvious for you are not clear for us. Better to submit small sample file (few records) to illustrate how the data is organized.
08-11-2020 09:57 AM
At the moment I do not have a tree of categories in its final form and I would prepare it according to what you advise me. But I'm sending a sample file to give you an idea of what I mean. There is a sheet of products and then categories.
08-11-2020 10:11 AM
I'd suggest to denormalise source data as
First table is SKU with names, another one SKU with categories. With that they could be merged in resulting table, preferably with Power Query. In second table SKU and categories could be selected from drop-down lists.
08-12-2020 02:11 AM
How to create drop-down lists so that the selected value is plain text suitable for csv and for import into woocommerce?
08-12-2020 09:44 AM
Drop-down list works only in Excel (xlsx) file. CSV is actually text file, you may add drop-down list while it is opened in Excel, but it will be lost as soon as you save the file in csv format.