Hello, I am currently trying to export database tables out in CSV format and am having a few problems. I would appreciate some assistance and advice in case I am missing something obvious.
The main issue seems to stem from some of the data within our tables potentially containing a comma. This is messing up the output of the CSV file with fields being misaligned with their headings, presumably due to the comma being treated as a separator here. In addition, one of the DB columns returned is the ID, which is being treated as a number and seems to be having a comma inserted into the value, further messing up the output.
I know it is possible to specify a different field delimiter character, however it is still possible that the data would contain this character as well, as the data is driven from user input and not something under my direct control. In addition, any delimiter other than a character makes it more difficult for end users to simply open in Excel and see properly formatted.
I have been looking at the Utility.php file and notice there is mention of $prefix and $suffix fields for data types of number and string, however from what I can see these are never set to anything other than blank values and don't seem to change the data at all.
What I am hoping to do is to be able to export the CSV file in a similar fashion to how a CSV export from phpMyAdmin would work. Here, the value of each column is returned in double quotes in order to prevent incorrect separation if the data contains a comma. In addition, any values already containing one double quote are replaced with 2, so that the end result still shows one of the double quotes. Hopefully this makes sense?
I have been able to "hack" this behaviour by directly modifying the Utility.php file to set the prefix and suffix values directly, as well as str_replace any single instances of double quotes with two of them. However this is obviously not ideal and may cause unexpected behaviour elsewhere. So ultimately I'm looking for advice on whether this is possible to do at the moment and maybe I've just missed something?
I also don't really like that the ID has been formatted as a number but this is less of an issue.