export to excel missing data
Yup. It's a bug.
The problem I have is the data for multiple checkbox element is not being output correctly in xls, data is missing.
if (in_array($options[$i-1], $subanswers))
// echo "\t\t\t".$options[$i-1]."\r\n";
and try changing it to
if (in_array(trim($options[$i-1]), $subanswers))
// echo "\t\t\t".$options[$i-1]."\r\n";
Does that help?
Your change did not help, not all fields exporting out still, what shall I do now. It had worked fine before, was the version of excel upgraded or something?
What I was expecting was for the data to be output into a standard delimited format of some kind that could be easily identified and imported into virtually any spreadsheet or DB application.
Anyway, it should be easy enough to hack the exporter to get the data into a format that's useful to me, but I remain perplexed at the decision to make HTML the "native" format.
It would be nice not to speak about Excel export because the exported file seems to be an invalid html table. It is in that respect invalid, that it does not contain normal html start and end tag, it just begins with the tag <TABLE> and continuing with invalid table tags on table header lines as
Please note also the missing closing mark on "<td". I have not altered concrete5 code, so this seems to be a quite serious bug.
I am no surely not to only one, who has been thinking that I have made something wrong when making forms on my concrete5 sites.
Because the "exported Excel" has no headers or no other information about encoding, the application which reads this "Excel file" must quess the encoding (and be able to parse also invalid td-tags). That's why some people seems to have trouble with e.g. Korean or Greek characters.
My suggestion: this export feature should be named properly, e.g. "html export" or "xml export" and the file structure should respect html or xml standards. Of course it would be nice to have a real Excel export feature, as well.
Edit: when studying a little bit, it is possible to fix this "excel export" by modifying the file concrete/controllers/dashboard/reports/forms.php. Now I'm testing to change the file extesion to ".html" adding a proper html header with UTF-8 encoding info and replacing <td><b> sequences with a normal <th> tags. Is any one interested on results?
You'll find the modified file attached here as a zip file.
Use: rename the original /concrete/controllers/dashboard/reports/forms.php to e.g. forms_original.php and add this new forms.php in this directory.
Please comment, if you find errors or something to improve.
The export does still spit out a HTML table, which excel can load fine... so that's - what it is... Should it be CSV instead? prolly. is it? Nope.
I would suggest, that the exported file should contain a formal html table structure, beginning maybe like this:
<meta content=\"text/html; charset=utf-8\" http-equiv=\"content-type\" />
<title>Form Data Results</title>
Content-type should be accordingly "Content-Type: text/html" and a file name suffix ".html". Not: Content-Type: application/vnd.ms-excel and suffix ".xls".
If the exported file is a standard html file, you can easily open the form results in a new browser window or tab. When I have used a true html structure in form results, the layman could easily open the results in a nice browser window without a need to open it on Excel or LibreOffice/OpenOffice Calc. This in convenient, because a large form data flows over the edges of the current Concrete5 control panel window.
In current form results are tag sequences like <td><b>xyz</b></td>. Why not simplier with <th>xyz</th>? Currently there are also several tab and CR+LF sequences, which might be OK in Windows environment but cause funny results on Mac OS X or Linux environments, which does not need CR+LF's.
One possible improvment would be, that instead of the current "Export to Excel" command there could be alternatives, e.g. 1) Export to (true) html table; 2) Export to (semi-true) Excel file; 3) Export to xml file. The last alternative might be useful, if the form results are handled by a database application.
What bothers me is that the data does not match the database!!!!!!!
When users are clicking multiple checkbox options, what it output on the .xls is no representation of what the clicked. The output on the form report page is correct, just not the exported excel.
Where can I find the function or file in the c5 code that controls what file type it makes the file? I have modified the export_row.php to produce .xml data, it works but I'd like to auto-save as .xml as opposed to having to manually change file types later.
The code on 'dashboard/core_commerce/orders/search/' points to 'dashboard/core_commerce/orders/search/export/' where is this exactly?
Thanks in advance.