We are using flexcel to open a csv file (pipe delimited) and save it as an excel file. The strings in the csv have quotes around them.
For example: 12345|"This is string"|
When there are quotes inside the string, there is an issue with interpreting the data.
For Example: 12345|""Hello" There"|
should be interpreted as
12345
"Hello" There
but is being interpreted as
Hello" There"
Is there a way to fix this?
Hi,
The main issue with CSV is that there is not a clear standard on how quotes and others should work. Actually, there is a standard, but nobody supports it, and everyone uses "whatever Excel does" as the de-facto standard. And so do we. You can read a little more about it at:
https://doc.tmssoftware.com/flexcel/vcl/tips/understanding-csv-files.html
So, for your example, I tried saving this string into a .csv file:
12345,""Hello" There",test
And opened it with Excel, and it shows:
Which is also what FlexCel is interpreting. If you want to get "Hello" There, then the simplest way to do it is to write it in Excel, and see what it saves when you save it as CSV.
I did it here, and the CSV generated is:
12345,"""Hello"" There",test
Which honestly makes more sense. When you have a quoted cell, you must quote all the cell inside quotes, and then quotes inside are escaped by writing them twice. The string you wrote: ""Hello" there" doesn't seem to follow a logical pattern. If "" is the escape for ", then ""Hello" means "Hello and then there is a hanging " at the middle of the cell. (and other at the end).
I think you are just writing simple " and not double to escape them, but then a parser can't know that the " means that the text ends there or it is just a middle-of-the-cell quote.
I mean, imagine we want to save the text
"Hello"|
in a cell.
If we single-quoted the cell, it would be:
""Hello"|"|Next-Cell
When we read this, the parser would read "Hello instead of "Hello"| for the cell. So without some extra rules, this isn't a workable standard. You wouldn't be able to read what you saved.
As said, there are millions of ways we could decide a CSV behaves, and we can't implement them all, but we tried to follow "what Excel does" as closely as possible, specially because people will later double click those CSVs, and Excel (or libre office or google docs, which all follow the what excel does standard), and if we didn't follow that, the CSVs will display wrong.
