CiviCRM Community Forums (archive)

Hello Mathieu,

Thanks for this release. I've tested it with the 4.4.6 and 4.5 beta 7 versions of civicrm on Wordpress 9.3.2.

In both case I found out that instead of exporting all the results of a report (I used Constituent Summary), it only exported the first 50 results (the one displayed on the first page of the report). I'm not sure if this is the expected behavior but I'd prefer to be able to export all results once, instead of generating as many xlsx files as my report has pages.

The 50 first results were correctly exported with following columns : Contact Name; Contact Type; Contact ID; Email; Street Address; City; Country; Phone. It went smooth and fast, no warning when opening on Excel and special characters correctly managed (file is encoded in ANSI, not UTF-8).

I don't know how much work it represents but having this "Export" to xlsx function for "Search results" would be great as today csv format is really a pain in the ass + we, French people with special characters in our alphabet, need to change every csv extract from "utf-8 without bom" to "utf-8" using Notepad++ (step not required with your extension -> it saves time).

Hope this helps.
Thanks again for sharing

Aha, found/fixed the 50 row limit issue. I pushed a new release to github, which includes also the experimental code for search export.

Thanks for the exhaustive testing!

Regarding the 255 character limit: I tested with the equivalent of 4 pages of text and opened the exported result in LibreOffice. It made LibreOffice freeze a few times, but the entire text was there. I don't have a copy of MS Excel to test with at the moment.

Does the column have HTML in it?

When searching on forums, there are some mentions that the export library should call "$export->setVersion(", however that seems to refer to the BIFF8 format of Excel < 2003 (binary format).

I've tested to export the "Notes" associated to a contact. One of them was 287 characters length and it has been correcly exported without being truncated at 255. The difference between the "note" (from civicrm_note table) field and my custom field is the type : text (no length specified) vs. varchar (530).

It seems then that the default varchar value (255) is the root cause of the limitation, not the export to Excel tool itself

Two options :
-> can it be bypassed ?
-> can I modified my field type in the custom field table to "text" instead of "varchar" without harming the content already existing ?

EDIT : I've added a new custom field VARCHAR(500). I've put a value for this field with 499 characters and exported it. It worked well when opening the file in Excel. Difference is that I'm using Excel 2013, vs. 2007 so I'm wondering if there is not a Excel version related issue... I'll check and keep you posted.

For both 2007 and 2013, the total number of characters that a cell can contain is: 32,767 characters.
see :
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx

Seems to be a bug in the search->export code, but looking only from the code itself, it seems OK.

In short, the search-export creates a temporary mysql table and guesses mysql column types based on the type of custom field. Ex: a "Textfield" will become a varchar(x), while a "Note" will become a "longtext" column.

CRM/Export/BAO/Export.php :

        case CRM_Utils_Type::T_STRING:
          if (isset($query->_fields[$field]['maxlength'])) {
            $sqlColumns[$fieldName] = "$fieldName varchar({$query->_fields[$field]['maxlength']})";
          }
          else {
            $sqlColumns[$fieldName] = "$fieldName varchar(64)";
          }
          break;

However, I would recommend changing your custom field to a "note" field. It can be subject to debate, but a varchar will always reserve the specified amount of space for each row, while a 'text' column will store a pointer to where the data really is. This means that lookups can be slightly slower, but it will use less space on disk, especially if that field is not always used (which can also make disk access faster).

There may be simpler ways of converting a field, but I usually create a new field and use an SQL query to copy the data, ex:

update civicrm_my_custom_value_table set newfield = oldfield;

Once that is done, you can disable the old field, do a few tests, and eventually delete the old one.

If I'm really lazy, I might do a "alter table" on the field, and update the corresponding civicrm_custom_field entry, but that is much more risky.

What is the value taken by "x" in varchar(x) ?

The "x" is the length that was defined for the textfield. So if you put 500, it should be a varchar(500).

Why would there be a difference between 4.4.6 and 4.5 ?

I managed to reproduce the error in 4.4.6, but not in 4.5.

Edit: Seems like it has been fixed: https://issues.civicrm.org/jira/browse/CRM-15048

I edited my last comment: it has been fixed in 4.5 already.

It's pretty easy to backport if you don't want to wait:
https://github.com/civicrm/civicrm-core/pull/3790/files