Hi Jack,

I found today that a combination of two double-quotes and one comma splitted a description in two columns while exporting to XLS or XLSX or CSV.in an XTREE
.
This:
ETIQ. TECIDO "LEF" BY PIEDRO 30X92 BRANCA/LETRAS ROSA CLARO, AD (DOBRA AO MEIO)

Converted to:
column A
"ETIQ. TECIDO "LEF" BY PIEDRO 30X92 BRANCA/LETRAS ROSA CLARO
column B
AD (DOBRA AO MEIO)"

Is there any tweak I can do in the CSV2XLS.ini to workaround this?

This is not urgent because I've changed the few descriptions with this problem, but it can come up again at any time so, I would like to prevent it in advance, if possible.
Thanks

Hi Jorge,

Nice to see you're back at work! (Or not.)

This appears to be a bug in CSV2XL, i.e. it is getting confused by the embedded double-quotes and not realizing that the complete string is one field.

Note however, that this assumes your example above is missing the trailing " after (DOBRA AO MEIO) in the original data. I suspect that it's really there, because it shows up in the 2nd column after it gets divided.

Let me see if I can figure out how to make CSV2XL a bit smarter, in which case this would just require an update to that one SBX.

Ok, although this might possibly be considered an issue for STRTOK (which is used by CSV2XL to parse the lines), I added a bit of workaround logic to CSV2XL 2.2(293) to put the broken field back together again. It currently only works for a single comma though, because it depends on finding a consecutive pair of fields, one with only a leading " and the other with only a trailing ", but hopefully that will reduce the problem by an order of magnitude with very little effort. All you have to do is run UPDCUR from the BAS: directory.

A more robust fix will require tinkering with XTREE itself, which I'm going to put on the to-do list.

Hi Jack,
Thank you for the welcome, but the cause of my silence here is a flood of work not a return to work :-)

No, there are no trailing double-quote in the original text, as well as the initial double-quote before ETIQ., it was added in column A and column B in the export process.
It seems that two double quotes were added around the full text due the comma in the midle but, after, got split in two columns at the comma position, probably confused by the pair of double-quotes around "LEF".

Thank you.

Speaking of flood, we got about a year's supply of rain here in the last couple of days. I'm glad you're dealing with a better kind of flood.

As for the quotes, on further review, XTREE always adds them to the output, mainly to minimize downstream problems with potential delimiters (commas) embedded within fields. The problem with embedded quotes though is that it confuses the STRTOK logic, which isn't specifically targeting CSV format so it just tries to match quotes in pairs, ignoring delimiters within them. But I think that the CSV2XL update will resolve it for you.

Yeah! I've heard about the floods in California, I don't know how much we will last on Earth with all the mad climate changes!

I didn't understand if I can update already the CSV2XL but tried and nothing new came, no hurry.
Thanks

Maybe that's why we need so many software changes - to give us practice dealing with the much bigger ones coming!

But I did post the CSV2XL 2.2(293) to the 7.0 UPDCUR respository. Here's what the operation should have looked like:

If you didn't get an update, then it's probably because you're not on 7.0?
In that case, you can just download the one file directly from: csv2xl.sbx

I'm still debating whether XTREE should convert the embedded double-quotes to single-quotes (which is one way of avoiding the problem more generally, since otherwise Excel, for example, will also split your example field into two if you load the CSV directly). Or just try to fix what may be considered a bug in STRTOK opcode 4+2 (used by CSV2XL) in which the paid of embedded double-quotes seems to confuse the opcode 2 logic.

Interesting...
Checking my BAS: folder, the CSV2XL.SBX has a timestamp of (very) early today.
I can remember that UPDCUR was the first thing I've done today.
It seems that I've missed it listed in the middle of the other updated files because, I've renamed it and tried UPDCUR now and got it.

But the most important is that it solves the problem.

Regarding the possibility to convert double-quotes to single-quotes, apologize for not agree because we can be converting the "inches" sign to "minutes" among other cases where the convertion can adulterate the content.

Many thanks for the fix

Glad to hear it. Sounds like you agree with Frank that UPDCUR could be more communicative!

And you’re right: we have to hold the line on adulterated content!