CSV export apostrophe '

futuron · Apr 26, 2017

Hi,
I have a client that organises a conference for European Institutions. Registration process is set up in Fabrik. The registrations hold all sorts of weird character shit like:

Universit?tsstra?e
Universit? Ca' Foscari
Marcor?
Tr?a?ka

They must be able to export to Excel or CSV. The closest I got to a problem free export was EXCEL and UTF8
But still the apostrophe translates into '

Unfortunately this is unacceptable for the client. Is there any way i could solve this. I know it's a very tricky with them apostrophes.

Any ideas?

cheesegrits · Apr 26, 2017

Theoretically, this should handle that, at line 110 in ./components/com_fabrik/models/csvexport.php ...

Code:

            // with UTF8 Excel needs BOM
            $str = ( $input->get('excel') == 1 && $this->getEncoding() == 'UTF-8' ) ? "\xEF\xBB\xBF" : '';

... unless the apostrophe has already been converted to an HTML entity somewhere.

What you could try is at line 248, just before writing the file out, htmlspecialchars_decode it ...

Code:

        $str = htmlspecialchars_decode($str, 'ENT_QUOTES');
        $ok = JFile::write($filePath, $str);

Let me know.

-- hugh

futuron · May 1, 2017

Tried that but that resulted in an empty file of zero bytes

troester · May 1, 2017

With Joomla3.7, Fabrik3.6, Database collation UTF8
CSV export settings: CSV, file encoding UTF8
it's working fine here with Excel365 (which seems to be able to open a regular CSV file directly meanwhile

)
Edit: no, Excel can't handle standard CSV, I opened the wrong file...

So
What are your element types?
DB collation?
Excel version?

futuron · May 1, 2017

The table the registration data is stored in (and where i'm making the export from) is

"latin1_swedish_ci"

Guess i should change this to UTF8 aswell, can i 'just' do that in phpmyadmin?

>> Tried to set it to utf8_unicode_ci in phpmyadmin. No luck. Or should i try to enter the values again first? (instead of just exporting what was there?)
>>> No luck, added a new record with ' in it and still rendered as '

Elements used in form are:

calc
date
dropdown
field
internalid
radiobutton
textarea
yesno

It's a 'field' where users type that ' so that's the one i'm focussing on.

troester · May 1, 2017

It's working on my site with a simple 'field' element.

Check in phpMyAdmin if the column collation of your field element is UTF8 (I always use UTF8_general_ci, I don't know what is the difference to ...unicode...)
Changing the collation of DB or table doesn't automatically change the (existing) columns.

futuron · May 1, 2017

I changed the coalition for the table and then all the columns manually to UTF8_general_ci. The textfields type is varchar(255). Added a new record but still no luck...

troester · May 1, 2017

Which exact Fabrik and Joomla version?

futuron · May 1, 2017

Latest
J 3.7 and Fab 3.6

troester · May 1, 2017

Ok.
If you get ' this was inserted this way into the field. Did you copy/paste the names from some web-site?
Anyway:

What you could try is at line 248, just before writing the file out, htmlspecialchars_decode it ...

Code (Text):

$str = htmlspecialchars_decode($str, 'ENT_QUOTES')

Try
$str = html_entity_decode($str, ENT_QUOTES, 'UTF-8');

Then the export should do with setting "excelCSV" and file encoding = UTF-8

futuron · May 2, 2017

That did the trick!
You didn't have ENT_QUOTES within apostrophes btw, so i didn't do that eihter. Not sure if that was the problem initially... But this worked.

So what was done:

Fabrik export settings: EXCEL and UTF8
Table coalition: UTF8_general_ci
Individual columns coalition: UTF8_general_ci
The textfields type: varchar(255).
Change line 248 in ./components/com_fabrik/models/csvexport.php

Code:

replace :
       $ok = JFile::write($filePath, $str);
with :
       $str = html_entity_decode($str, ENT_QUOTES, 'UTF-8');
       $ok = JFile::write($filePath, $str);

So will this be changed in github or is this just solving my specific problem (and should i secure the file)?

troester · May 2, 2017

I'm not sure if in
html_entity_decode($str, ENT_QUOTES, 'UTF-8');
UTF-8 has to be replace by the file encoding for general use.

I'll leave the GitHub to @cheesegrits

cheesegrits · May 2, 2017

I'm not sure that I want to put that in the code. As we've both said, if it's rendering an entity, that means the entity is what is stored in the data. And I can see valid reasons where people might have entities in their raw data they want to keep as entities. Any quotes entered through normal Fabrik usage should already be decoded before storing. So any entities in the data could well be deliberate, and shouldn't be decoded.

So it would almost certainly need YAFO. And I'm just not wild about adding yet another option to the CSV handling, for something this corner case.

We have a CSV export hook you can access through a plugin (I think I added export hooks), and that might be the way to handle this.

Sent from my HTC6545LVW using Tapatalk

futuron · May 3, 2017

You mean like a list plugin? I tried to 'discover' plugins but wasn't able to find anything.

troester · May 3, 2017

It's the listcsv plugin (although the description in Fabrik Downloads only mentions import it has hooks for export, too, meanwhile).

futuron · May 3, 2017

So i should grab that ./components/com_fabrik/models/csvexport.php file and place it in ./plugins/fabrik_list/listcsv/scripts/ ?

And select it as export script?

troester · May 3, 2017

No, "discover" and install the listcsv plugin (or get it from the downloads) and add it to your list.
Then in
Export Row PHP Code
"Code to run before each rows is exported. Modifying $listModel->csvExportRow['yourtable___yourelement'] will modify the data. Returning false will skip the row. Will be run in addition to any selected file, so can call functions defined in the file(s)"
you can add something like

$listModel->csvExportRow['yourtable___yourelement'] = html_entity_decode($listModel->csvExportRow['yourtable___yourelement'], ENT_QUOTES, 'UTF-8');

futuron · May 3, 2017

Ok, reverted that file to its original state.
Added this in that code box for the list element (so each field element in that form basically)

Code:

$listModel->csvExportRow['fab_conference_registrations___title'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___title'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___first_name'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___first_name'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___last_name'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___last_name'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___jobtitle'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___jobtitle'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___organization'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___organization'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___address'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___address'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___address_2'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___address_2'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___city'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___city'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___dietary_remarks'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___dietary_remarks'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___zip'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___zip'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___Country'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___Country'], ENT_QUOTES, 'UTF-8');
$listModel->csvExportRow['fab_conference_registrations___email'] = html_entity_decode($listModel->csvExportRow['fab_conference_registrations___email'], ENT_QUOTES, 'UTF-8');

Did export and it worked as expected. As soon as i turn off the list plugin it converts the apostrophe to ' again. So this is working.

Thanks a lot for the help on this guys!

CSV export apostrophe '

futuron

Member

cheesegrits

Support Gopher

futuron

Member

troester

Administrator

futuron

Member

troester

Administrator

futuron

Member

troester

Administrator

futuron

Member

troester

Administrator

futuron

Member

troester

Administrator

cheesegrits

Support Gopher

futuron

Member

troester

Administrator

futuron

Member

troester

Administrator

futuron

Member

Useful links

Members online

CSV export apostrophe &#039;

Member

Support Gopher

Member

Administrator

Member

Administrator

Member

Administrator

Member

Administrator

Member

Administrator

Support Gopher

Member

Administrator

Member

Administrator

Member

Useful links

CSV export apostrophe '