boolean
Posts: 62
|
| Posted: 06/14/2007, 7:53 AM |
|
Hi,
I have a textarea field in my database and I export that field to an Excel file. The export works fine, but the LINE FORMAT (I guess) is not.
For example: in one record, a textarea has more than 1 line (as pressing Enter key) (in sql, I see \r\n). When I export that record, the Excel generates each NEW LINE as a ROW. That means 2 new lines in the textarea give out 2 record in Excel. BUT it is supposed to be only one record / row.
Would you please instruct how to fix it? Thank you.
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 06/14/2007, 9:36 AM |
|
First method:
Strip the \r\n out of the data.
In Mysql
SELECT replace('fieldname', '\r\n','') FROM tablename .....
will replace the \r\n by '' effectivly delete it
you can adjust the '' to cater for replacing it with any other character.
For instance a space ' ' or a newline character as Excel expects it.
Now you will have to Google for that character yourself.
ALT+Enter is the correct one if you are doing this in Excel.
chr(10) I think.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
esiahc
Posts: 8
|
| Posted: 09/09/2007, 2:26 AM |
|
Hello,
I have the same problem and I am surprised to see that this does not seem to be considered an "important" issue: Personally I find it rather embarrassing to have to tell to my customers that they just cannot use the return key when they are entering text in a text area if they want to export their database to Excel... That is weird.
Isn't a function called "Export to Excel" supposed to effectively export to Excel? In CodeCharge, this export function is very light: Instead of converting SQL to the actual Excel format, it just export it to html format which is then converted to Excel within the Excel program (because of this you cannot for instance open it as a spreadsheet in OpenOffice).
OK, that may be the "standard" easy way to export SQL to Excel... but at least, couldn't the "Export to Excel" function of CodeCharge convert the \r\n characters found in SQL into something that can be interpreted by Excel as a proper end of line?
That would be nice!  ...
|
|
|
|
|
