export to excel missing data

Permalink
After exporting form data to excel, there are missing fields, was working fine, no changes were made to the form. What could have happened? Anyone out there have any ideas?

Maureen
View Replies:
frz replied on at Permalink Reply
frz
I can create the same issue using the multiple check box element. It looks like the data is being stored correctly just not spit out to excel correctly.


Yup. It's a bug.
Maureen replied on at Permalink Reply
Maureen
My website depends on the form data, When can I expect a fix for this?
Maureen
balloondog replied on at Permalink Reply
Can someone upload what the fix is for this, I need to manually update as we are not in a position to do a core update.

The problem I have is the data for multiple checkbox element is not being output correctly in xls, data is missing.
andrew replied on at Permalink Reply
andrew
Try opening concrete/controllers/dashboard/reports/forms.php, and look for these lines:

if (in_array($options[$i-1], $subanswers))
// echo "\t\t\t".$options[$i-1]."\r\n";
echo "✔";

and try changing it to

if (in_array(trim($options[$i-1]), $subanswers))
// echo "\t\t\t".$options[$i-1]."\r\n";
echo "x";


---
Does that help?
Maureen replied on at Permalink Reply
Maureen
Andrew,
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?
Shotster replied on at Permalink Reply
Shotster
I just started playing with the Excel export feature in the reports section of the dashboard, and it's unclear to me what is SUPPOSED to happen. What IS happening is that the form data is being output in an HTML table into a file with a ".xls" file name extension. What does an HTML table have to do with Excel, and is that the intended behavior?

-Steve
Shotster replied on at Permalink Reply
Shotster
Ok, I decided to look at the code, and apparently it is the intended behavior, but it still doesn't make sense to me. HTML is NOT Excel. Excel might be able to import an HTML table, but it makes no sense to give the file an XLS extension. Other programs that can import XLS data do not necessarily understand HTML.

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.

-Steve
julehti replied on at Permalink Reply
julehti
Sorry posting to so old thread, but Steve's thoughts about missleading .XLS extension are still valid.

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
<td <b>Name</b></td>


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?
julehti replied on at Permalink Reply 1 Attachment
julehti
I made a modified forms.php file, that exports form results as a html file. Export file contains html header with a proper encoding information (UTF-8), body tag and a proper table with <th> tags at table header line cells and corresponding end tags.

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.
balloondog replied on at Permalink Reply
This is still not fixed!
frz replied on at Permalink Reply
frz
The bug this thread started with was fixed a long time ago.

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.
julehti replied on at Permalink Reply
julehti
I think the real issue is now, how should we describe the exported file and what should it contain. It is surely not an Excel file, because a normal Excel file does not start with <table> etc. etc. It is also not a proper html file, which should start with <html> etc. etc.

I would suggest, that the exported file should contain a formal html table structure, beginning maybe like this:
<html xmlns=\"http://www.w3.org/1999/xhtml\">
<head>
<meta content=\"text/html; charset=utf-8\" http-equiv=\"content-type\" />
<title>Form Data Results</title>
</head>
<body>
<table>
...
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.
Shotster replied on at Permalink Reply
Shotster
I've implemented a CSV export. If I get a chance, I'll put it on Github and submit a pull request.

-Steve
balloondog replied on at Permalink Reply
Im not fussed about whether its an html table or not.

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.

B
CaptainPanda replied on at Permalink Reply
I know this is an old thread but it applies to my current issue

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.