Forums

I'm using ChronoForms for a few forms on my website, no problems with most of them.
But when I export 2 of them to Excel, Excel comes up with a error message "Unreadable content, do you want to recover?". When I choose to do this a part of the database is exported but Excel gives me the error that the formulas en data are fixed but a few data may be lost.
The result is a Excel file which contains a part of the database, the end of the export:



The rows above this screens are proper filled with data and didn't contain that ? thing.
By the way, export to CSV works fine.

Hi jbs,

It looks like a character set problem. I'd guess that Excel is trying to interpret the random string in cf_id as UTF-8 characters - or something like that, I don't understand much about character sets or their interaction :-(

Bob

The uid of record 32 is "IYmZiNTdiNmEyYWQ2" as I can see in de database. When I copy the string with the ? and past it here the ?'s are gone and the uid looks like IYmZiNTdiNmE. It's cut of before the ? thing. Maybe this information is useful?

Another option is to change te export settings. I don't need that uid id and/or cf_id in the exported Excel file and I'm only interessed in the data that was filled in by the visitors. Is this possible?

Hi JBS,

the excel will try to save everything as UTF8 yes but I don't think this should cause any troubles, I'm not sure why this happens, you can't choose which data to download with excel too, it just backup the whole data table.

however you may try to create a new table for the form to save the data to it, and uncheck the uid column ONLY from the table columns when you create it!

Regards
Max

Hi,

The warming "Dont Change unless you know what you are doing" prevent me doing that at the time I created the tables. But is it possible to uncheck that column without any risks?
Creating a new table is not an option now because there are a few records in the table already and I can't lose them or take any risks with them.

I'm looking forward to an answer why this happens! 🙂

I removed the column UID from the database and tried to export to Excel again. But.. This time the export failed a varchar(255) field with " 0499 425051 " in it.
Am I the only one having such issues with exporting to Excel?

Hi JBS,

the export failed this number = you got "????" instead of it ?

if so then check if your database tables and tables' fields are utf8 ?

Max

Hi Max,

First of all, I really appreciate your help!

I have exported the database again but now I'm working on Windows 7 beta with the same Office 2007 but I don't see the ? in the phonenumber field. Instead I see an incomplete number.

The number entered in the form looks like "1234-567890" but in the exported Excel file you see "1234-56789", so without the last number. After that record the only cells that are displayed in the export are the cf_user_id, and 2 others varchar fields. The rest is blank. I think it's better to understand with a (modified) screenshots of the exported database:



As you can see the records till 37 are properly exported. When I take a look at the data in the database with phpmyadmin I don't see any difference between data in record 37 or records before...

Yes, the MySQL charset: UTF-8 Unicode (utf8)

The table structure:



\\Edit: The export of another form is going well till a phonenumber field (varchar255) again... Now the number is entered without a "-", so like 1234567890.

Hi JBS,

what about the table charset and the fields of this table ?

what are the fields names of this table ?

try to create a complete new test form, 1 or 2 fields, create a table using chronoforms and test the excel of this one and let me know the results, I'm assuming you have chronoforms version 3 or up

Cheers
Max

Hi Max,

3.0 STABLE :-)

I posted a screenshot of the table in my previous post. I have not changed any fieldnames and other things such as fieldtypes.
This is a screenshot from a table according to a form that has no problems with exporting to Excel. I don't see any difference (except the deleted uid row in the other table to find out if that row was the problem).



I have other forms which I can export to Excel without any problems.

Hi JBS,

it looks like your domain is not reachable from my PC and so I can't see the image, can you please attach the image to the post ?

Max

Hi Max,

I attached the screenshots. Are you able to match the text and the screenshots?

mmm, try to export the 2nd table (which has the problem) to excel through phpmyadmin, it works fine ?

Max

Exporting to Excel (of Word) via phpmyadmin failed with this error: "FATAL ERROR: Specified unsupported export type(htmlexcel)"
I think this is hosting side problem.

(Btw, when I'm using Excel 2007 on Windows 7 beta, opening the CSV has in a proper formatted Excel sheet result like exporting it directely to Excel.)

Exporting to Excel (of Word) via phpmyadmin failed with this error: "FATAL ERROR: Specified unsupported export type(htmlexcel)"
I think this is hosting side problem.

it does the same with any table ?

I think its better now to create a new table which works and export the old data to it! I'm not sure what exactly causing this and the excel export script is not written by me!

Regards,
Max

I have created a new form but I'm not sure if that's gonna work fine when a few forms are submitted...
I'll export the data to a CSV file, that doesn't give any problems.

Thanks for your support.

Litte bit late reply this time...

I have this problem with a few forms so creating a new form will work, but not every time. Still didn't figured out why... Converting the CSV to XLS now.

HI JBS, if you got a moment and a test place then you may try with RC4.11 and let us know!

Cheers
Max

Yes, I'm able to test the RC4 but it's hard to say which form gives a problem and check if RC4 fixes this issue. I have a few forms with about the same fields (only text). Form X export to Excel works fine, Form Y failed.

I'm using the forms on a live site so I have to copy them to a test location to upgrade to RC4.

I have installed a new Joomla environment to install RC4. After installing RC4 I have exported the form from the live site and imported it in the RC. I have exported/imported the table with the filled in data too. Exporting to Excel still fails...

Earlier in this topic we thought there was nothing wrong with the data users filled in, so I don't think it's wrong to import/export the table?

Hi JBS,

did you try RC "4" ? or "4.11" ? we need "4.11"

Cheers
Max

Yeah, 3.1 RC4.11

I'm not sure then why this occurs, may be try in the new environment with a new form and new data and see if it still happens, or look at the apache error log...

Max

I don't have enough time to create and fill in a new form 20 or more times. I'll wait for the official stable new version of ChronoForms and then try again.

do you use a script to insert to XL ?

I use this:

   <?php

// setter variabler

$a = $_REQUEST['a'];
$b = $_REQUEST['b'];
$c = $_REQUEST['c'];
$d = $_REQUEST['d'];
$e = $_REQUEST['e'];
$f = $_REQUEST['f'];
$g = $_REQUEST['g'];
$h = $_REQUEST['h'];
$i = $_REQUEST['i'];
$j = $_REQUEST['j'];
$k = $_REQUEST['k'];
$l = $_REQUEST['l'];
$m = $_REQUEST['m'];
$n = $_REQUEST['n'];
$o = $_REQUEST['o'];
$p = $_REQUEST['p'];
$q = $_REQUEST['q'];
$r = $_REQUEST['r'];
$s = $_REQUEST['s'];
$t = $_REQUEST['t'];
$u = $_REQUEST['u'];
$v = $_REQUEST['v'];
$w = $_REQUEST['w'];
$x = $_REQUEST['x'];
$y = $_REQUEST['y'];
$z = $_REQUEST['z'];

$data =
"$a\t$b\t$c\t$d\t$e\t$f\t$g\t$h\t$i\t$j\t$k\t$l\t$m\t$n\t$o\t$p\t$q\t$r\t$s\t$t\t$u\t$v\t$w\t$x\t$y\t$z\n";
$file = "svarstyrkep/svar.xls";
if (!$file_handle = fopen($file,"a")) { echo "<pre>Klarer ikke</pre>"; }
if (!fwrite($file_handle, $data)) { echo "<pre>Klarer ikke</pre>"; }

echo "<p class=\"text_black_medium\">      <br/><br/>";
fclose($file_handle);
?>

in the On Submit code - after sending email:
(PHP code with tags)

No, I export to Excel with the "Backup to Excel" feature when all the users have filled in the form.
What does your code do?

Hi JBS,

Thomas's code creates a .XLS file, he uses some request data but you may change this to a DB query to the table, I think that this may work for you, give it a try! the code uses tabs between cells which I'm not sure is the best way for handling different data fields types but it works ok with simple strings after testing it!

Thank you Thomas for sharing this!🙂

Regards,
Max

Hi JBS,

Being pedantic - I think Thomas' code creates a CSV file and gives it a .XLS suffix so tha Excel will open it. Very neat solution!

Bob

Hi..everyone

I have problem with exporting to Excel. So..first time i had been to test "back up to excel" on my computer, everything it ok, but after upload my web to hosting, then i'm test "back up to excel", file .xls will be broken, can't open...(0 bytes)

How can i do to fix this problem?

help me please,

Hi Jakky,

I've no real idea. My best guess is that you don't heve the correct permissions to save the temporary file that the Excel Writer needs. Check your site logs and see if an error shows up, your ISP may be able to help.

Bob

Hi, thank you for answers.

i had check my site logs but had not an error shows up. then i check and set permissions to same as my files on my computer, but can't fix this problem.

Do you have another ways more?.

Or can you tell me, where're folders must be set permissions to 777 or 755 follow excel writer needs?



sorry, If i don't skillfully in english language.

thank you

Hi Jackky,

755 is usually good - it will be the php temp folder that is needed I think. sometimes that's outside the website itself.

Bob

Thank you, Bob

I think someone had same problem as me in link, and them can't fix the problem..
http://www.chronoengine.com/forums.html?cont=posts&f=2&t=10715&p=11116&hilit=backup+excel#p11116

so i think maybe i must search for another ways..

Thank you so much for your answers.


thank you..
jackky

Hi Jackky,

please open this file:

administrator/components/com_chronocontact/admin.chronocontact.html.php

and find these lines:

$xls =& new Spreadsheet_Excel_Writer();
	$xls->setVersion(8); // this fixes the 255 limit issue!🙂
	$xls->send("ChronoForms - ".$formname." - ".date("j_n_Y").".xls");

replace with:

$xls =& new Spreadsheet_Excel_Writer('backup');
	$xls->setVersion(8); // this fixes the 255 limit issue!🙂
	$xls->send("ChronoForms_".$formname."_".date("j_n_Y").".xls");

Let me know!

Regards
Max

On the whole one good friend told about nice application-repair corrupt xlsx,which is free as far as I know,it has many features,also utility helped a lot of people and me too,software can repair .xlsx even without touching your keyboard, just left click your mouse several times and all of your data will be recovered,automatically and attempts to repair xlsx corrupt, that was considered lost,recover maximum percents of your document manually, than the entire Excel workbook.

A member of a Dutch Joomla forum has posted the solution for my problem:

Open admin.chronocontact.php in the folder administator/components/com_chronocontact

and remove

$sheet->setInputEncoding('utf-8');  

or make it a comment line like

 //$sheet->setInputEncoding('utf-8');  

.

Great, thanks for sharing!🙂

Max

Thx. Solves part of the problem. File can be opened by Excel (2003) but special characters are now not ok.
ë becomes ë
ö becomes ö
é becomes é

Hello everyone,

JBS wrote:

or make it a comment line like


Code: Select all
//$sheet->setInputEncoding('utf-8'); .

Unfortunately this didn't work.

Backup to Excel gives a blanc sheet. Backup to CSV first gives (like Astrid wrote) the problem with special characters.

If I backup with phpmyadmin and export the file to "Microsoft Excel 2000" everything is OK. So that is a workaround, but I would like to have a 'real' solution... If somebody has one😀 .

Thanks in advance

Hi yvet,

Please search further here, there was another thread about this a few months ago with some better solutions in it.

Bob

Hi Bob,

Thank you for your reply.

I thought I had seen al the given solutions and so far nothing worked. (I really searched a lot.) Maybe to can tell which post it was? Then I can try that again. Maybe I missed something.

Yvet

Hi Yvet,

Try this thread.

Bob