Resolved Export product_desc end ups in multiple fields

Hello,

I have 2 problems:

No you don't :silly: We have challenges

One thing you should know is that Excel is very stubborn when it comes to CSV files. It starts already with the delimiters you have chosen for export. Excel does not allow any other delimiter than a single or double quote. Since you have something else it will cause problems.

The first issue should be solved by setting the field delimiter to a semi-colon (if you are not in Europe, this is going to be a comma) and the text enclosure to a double quote. I can hear the question come but my text has double quotes. This will be taken care of by CSVI, as the CSV standard has a solution for that. With the standard delimiters that Excel loves so much, you can open the file fine in Excel. You can even go as far as specifying an export filename in your template and add the extension .xls so Excel will open it directly. So your export filename could be myproducts.xls.

Your second issue is more problematic because CSVI does indeed use the |-symbol for separating multiple categories. The only solution I can think of for this is to replace the |-symbol with it's HTML equivalent

|

on import and export. So you have a replacement rule for the import where the |-symbol is replaced by the code above. For export you have the opposite rule. This is only needed then for the category_path field.

1e problem
I change delimiter back to ; (semi-colon) but this make no difference, excel doesn't recognize product_desc as one field.
If you look at text file, so even without importing it into excel, you can already see that the html code within the description field is already on multiple lines.
In the text file you see another strange thing, 2 x double quotes: style=""font-size: 16px;"

2e problem with category separator.
This replace rule you suggest, replaced the | within the category name, but also the separator for multiple categories.

Any other ideas?

If you look at text file, so even without importing it into excel, you can already see that the html code within the description field is already on multiple lines.

I told you Excel is stubborn OpenOffice/LibreOffice has no problems reading this file. This would need a replacement rule as well, where you replace the line-ending with a space. So in the replacement rule you would search for

/\n/

and replace it with a space. Set the type of replacement to a regular expression. This will remove the invisible line breaks.

In the text file you see another strange thing, 2 x double quotes: style=""font-size: 16px;"

That is the CSV standard. If you use a double quote as text enclosure then any data inside should be escaped with another double quote. So that get's you 2 double quotes, so you know the double quote belongs to the text.

This replace rule you suggest, replaced the | within the category name, but also the separator for multiple categories.

Ah yes, I see. That is because you have both in the same field of course. Then your original solution remains and that is import the product twice with the Append category option. Only other way I see is that the category names are changed to use the HTML code or another separator symbol if you want to do it in a single run.

Thx Roland,

All works. It was CR LF so, /\r\n/ did the job.
Category separator replace works now as well, the first | had spaces around it and the regular category | separator not. So just had to include spaces in replace function.

Thx again for the quick help

Hi Roland,

A follow up question on this replacement function. As I mentioned before export with replacement functions works great.
Now I trying to do the reverse method during import.
Somehow it doesn't work.
So I am trying to change "& #124;" back |, using the regular text for this.
Should I use regular expression with escape characters?

I looked in log file, but this doesn't show the execution of the replacement.
Any tips?

Hello,

I think you can just use a regular text replacement, is it a problem to leave it as it is? The browser should still show it as a |-symbol or not?

The log has no data on the replacements indeed.

I can't leave it, the browser title is showing & #124;in the category name!
I tried replace text during import, just with replace abcd within the category_name, and replace it for |, but this doesn't anything either.

Ah yes, the browser title, the part that doesn't render HTML.

Do I understand it correctly you are saying a regular replacement is not working? If so, we need to check how it is setup. Is the rule set to Import and assigned to the categoy_name field?

Yes correct. normal replace with import doesn't work.
Screenshot shows my rules.
Screenshot import is a copy of the export, with a change of the different rule to use. Strange thing is that I don't have a column publish in the overview. That's why I included the export screenshot.

Strange thing is that I don't have a column publish in the overview.

That column doesn't exist in the import templates, so it is normal not to see it there.

In your import template, have you set, Use file for configuration to No? This is required for CSVI to use the fields assigned to the template.

rrrrr, you are right again. I normally did it both, use first line with all fields, and have all fields in template as well. But I see now, that if you use first line, it doesn't trigger the replacement.

Replacement now works fine, although not the expected results. It replaces & #124; with |-sign first, and after that creates the "new" unwanted category. It makes sense, before updating or creating a category, fist execute the replacement.

What I did was use separator [ and the replacement: "Audi[A4 Avant (B8) | 2012-|- All products
Hoped this would be the result:
Main: Audi
Sub: A4 Avant (B8) | 2012-
2nd main: - All products

But it creates a "2012-" main category as well!
Any other smart work arounds!

I don't have any smart workarounds for this because the multiple categories separator is fixed in the code unless you want to change that.

You can create an override for the product import and change line 215 of the administrator/components/com_csvi/addon/virtuemart/com_virtuemart/model/import/product.php where it has the |-symbol.