I am using APEX 4.2.2 with APEX listener 2.0.2.
I have create a RESTful web service to retreieve some data from a table. The format is "CSV" (the other option is JSON, which I am not faimilar with).
then I tested and it generate with below sample data.
United States,"Zhang, Kevin",IT
Above data means: Country, fullname, department
As you can see value of fullname is double-quoted and also contains a comma (,) in it.
Then I create a web service reference in a test application. Then I create a a form and report on web service.
The problem is the fullname. The output looks like below:
Country fullname Department
United States "Zhang IT
As you can see, APEX is not able to get fullname correctly.
Anyone see this issue and how to resolve it?
I'm not sure I would classify it as a bug so much as an enhancement. We need the ability to set the "Text qualifier" as one can from Excel. This would allow the above to be seen a single a single field and, then, to do what you want. However, as I'm sure you realize, this just doesn't exist at the moment.
So; you could store the first and last names in separate fields in the table (don't forget that you could use virtual columns for this purpose) or simply alter your query to pull them out as if they were separate fields, i.e. use SubStr and InStr.
If you simply must see them in this format when the CSV is opened, you could create a formula in Excel to do this for you.
I still think it is a bug. I could have any column whose value contains comma in it. For example, product_description, which describe product and value is a string. This bug will cause lots of issues if column value contains comma.
Text qualifier is always mentioned whenever we deal with CSV format. The workaround is a pain. I hope APEX can enhance it in the next release.
Agreed, it ought to be fixed.
However, I think as it stands now, this may be all that can be done if you want to use the built-in restful API.
If you aren't wedded to this approach, you could try Scott's custom solution here.