Line breaks in MySQL field breaking Excel download file.

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Line breaks in MySQL field breaking Excel download file.

Jim Sheffer-3
Evening all,

I have a simple text field on a form.  There are several thousand  
records in a MySQL database entered from the input of this form, most  
containing several paragraphs each.  The paragraphs each have a space  
between them, seeming to contain line returns.
I have another page that pulls these records from MySQL into a  
variable, that in turn is added to other information to form a  
delimited file that is downloaded using the fie_serve tag.

When I print this to the html page, there are no line breaks and  
everything looks fine.  But when the file is downloaded and then  
imported into Excel, the spreadsheet goes askew.

It seems this is caused by the line breaks, as the first set of lines,  
which contain only one paragraph for this field are fine.  As soon as  
a line that contains this field with multiple paragraphs, the columns  
in Excel are all thrown off.

I don't need to have the paragraph breaks imported into the Excel  
file, but I don't know how to check for them and remove them before  
they are added to the variable that makes up the downloaded file.

Since there are several thousand records, removing the line breaks for  
the paragraphs is out of the question
Any ideas or help would be great!

Mac OSX 10.4
Lasso 6.x

Thanks!

James

James Sheffer          [hidden email]
Lasso Developer        http://www.higherpowered.com
phone:  469-256-0268   Toll Free: 1-866-301-2545



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

stevepiercy
Dejá vu!

http://www.nabble.com/Dealing-with-text-to20662321.html#a20864833

In summary, use any one or a combination of the following.
* Tell users to use OpenOffice instead of Excel.
* Normalize line breaks before they go into MySQL.
* Normalize line breaks as they are exported into Excel.

Details are contained in the thread, linked above.

--steve


On Friday, December 19, 2008, [hidden email] (James Sheffer) pronounced:

>Evening all,
>
>I have a simple text field on a form.  There are several thousand  
>records in a MySQL database entered from the input of this form, most  
>containing several paragraphs each.  The paragraphs each have a space  
>between them, seeming to contain line returns.
>I have another page that pulls these records from MySQL into a  
>variable, that in turn is added to other information to form a  
>delimited file that is downloaded using the fie_serve tag.
>
>When I print this to the html page, there are no line breaks and  
>everything looks fine.  But when the file is downloaded and then  
>imported into Excel, the spreadsheet goes askew.
>
>It seems this is caused by the line breaks, as the first set of lines,  
>which contain only one paragraph for this field are fine.  As soon as  
>a line that contains this field with multiple paragraphs, the columns  
>in Excel are all thrown off.
>
>I don't need to have the paragraph breaks imported into the Excel  
>file, but I don't know how to check for them and remove them before  
>they are added to the variable that makes up the downloaded file.
>
>Since there are several thousand records, removing the line breaks for  
>the paragraphs is out of the question
>Any ideas or help would be great!
>
>Mac OSX 10.4
>Lasso 6.x
>
>Thanks!
>
>James
>
>James Sheffer          [hidden email]
>Lasso Developer        http://www.higherpowered.com
>phone:  469-256-0268   Toll Free: 1-866-301-2545
>
>
>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

Bil Corry-3
Steve Piercy - Web Site Builder wrote on 12/20/2008 12:05 AM:

> Dejá vu!
>
> http://www.nabble.com/Dealing-with-text-to20662321.html#a20864833
>
> In summary, use any one or a combination of the following.
> * Tell users to use OpenOffice instead of Excel.
> * Normalize line breaks before they go into MySQL.
> * Normalize line breaks as they are exported into Excel.
>
> Details are contained in the thread, linked above.

I leave to get a sandwich, come back and am scooped...


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

stevepiercy
On Saturday, December 20, 2008, [hidden email] (Bil Corry) pronounced:

>I leave to get a sandwich, come back and am scooped...

The sandwiches and I are working as a team.  "psst, pastrami on rye with mustard, call Bil away from his computer for a few minutes..."

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                  <http://www.StevePiercy.com/>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

Jim Sheffer-3
In reply to this post by stevepiercy
Thanks Steve- I looked, but must have missed this thread!

James

On Dec 20, 2008, at 12:05 AM, Steve Piercy - Web Site Builder wrote:

> Dejá vu!
>
> http://www.nabble.com/Dealing-with-text-to20662321.html#a20864833
>
> In summary, use any one or a combination of the following.
> * Tell users to use OpenOffice instead of Excel.
> * Normalize line breaks before they go into MySQL.
> * Normalize line breaks as they are exported into Excel.
>
> Details are contained in the thread, linked above.
>
> --steve
>
>
> On Friday, December 19, 2008, [hidden email] (James Sheffer)  
> pronounced:
>
>> Evening all,
>>
>> I have a simple text field on a form.  There are several thousand
>> records in a MySQL database entered from the input of this form, most
>> containing several paragraphs each.  The paragraphs each have a space
>> between them, seeming to contain line returns.
>> I have another page that pulls these records from MySQL into a
>> variable, that in turn is added to other information to form a
>> delimited file that is downloaded using the fie_serve tag.
>>
>> When I print this to the html page, there are no line breaks and
>> everything looks fine.  But when the file is downloaded and then
>> imported into Excel, the spreadsheet goes askew.
>>
>> It seems this is caused by the line breaks, as the first set of  
>> lines,
>> which contain only one paragraph for this field are fine.  As soon as
>> a line that contains this field with multiple paragraphs, the columns
>> in Excel are all thrown off.
>>
>> I don't need to have the paragraph breaks imported into the Excel
>> file, but I don't know how to check for them and remove them before
>> they are added to the variable that makes up the downloaded file.
>>
>> Since there are several thousand records, removing the line breaks  
>> for
>> the paragraphs is out of the question
>> Any ideas or help would be great!
>>
>> Mac OSX 10.4
>> Lasso 6.x
>>
>> Thanks!
>>
>> James
>>
>> James Sheffer          [hidden email]
>> Lasso Developer        http://www.higherpowered.com
>> phone:  469-256-0268   Toll Free: 1-866-301-2545
>>
>>
>>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy               Web Site Builder               Soquel, CA
> <[hidden email]>                  <http://www.StevePiercy.com/>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

Jim Sheffer-3
In reply to this post by Bil Corry-3
If it helps anyone, I used this to replace the "\r" with 2 spaces in  
the field before adding to my string for the export file:

var:'desc'=(string:(field:'description'));
       
        if:($desc->find:'\r');
                ($desc->(replace: '\r', '  '));
        else;
        /if;

James

James Sheffer          [hidden email]
Lasso Developer        http://www.higherpowered.com
phone:  469-256-0268   Toll Free: 1-866-301-2545


On Dec 20, 2008, at 12:12 AM, Bil Corry wrote:

> Steve Piercy - Web Site Builder wrote on 12/20/2008 12:05 AM:
>> Dejá vu!
>>
>> http://www.nabble.com/Dealing-with-text-to20662321.html#a20864833
>>
>> In summary, use any one or a combination of the following.
>> * Tell users to use OpenOffice instead of Excel.
>> * Normalize line breaks before they go into MySQL.
>> * Normalize line breaks as they are exported into Excel.
>>
>> Details are contained in the thread, linked above.
>
> I leave to get a sandwich, come back and am scooped...
>
>
> - Bil
>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>





--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

Steve Upton
In reply to this post by Jim Sheffer-3
At 11:45 PM -0600 12/19/08, James Sheffer wrote:

>Evening all,
>
>I have a simple text field on a form.  There are several thousand records in a MySQL database entered from the input of this form, most containing several paragraphs each.  The paragraphs each have a space between them, seeming to contain line returns.
>I have another page that pulls these records from MySQL into a variable, that in turn is added to other information to form a delimited file that is downloaded using the fie_serve tag.
>
>When I print this to the html page, there are no line breaks and everything looks fine.  But when the file is downloaded and then imported into Excel, the spreadsheet goes askew.
>
>It seems this is caused by the line breaks, as the first set of lines, which contain only one paragraph for this field are fine.  As soon as a line that contains this field with multiple paragraphs, the columns in Excel are all thrown off.
>
>I don't need to have the paragraph breaks imported into the Excel file, but I don't know how to check for them and remove them before they are added to the variable that makes up the downloaded file.

I don't know if you've tried this but it sounds like Excel is trying to "help" you and doing a poor job.

You might to try enclosing the entire text passage in double quotes "xxxx". I find that Excel will leave the contents alone and not split it across cells or rows if I enclose text in this manner...

hope this helps,

Steve


--


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

decorior
In reply to this post by Jim Sheffer-3
Hi,

Why do you even "search" to see if the line break is there? We always  
assume the worst :-)

We do a bunch of replaces on text we get to ensure no gremlins :-)

Deco

Note: We tried regexp but that has a size limit.

On Dec 20, 2008, at 10:49 AM, James Sheffer wrote:

> If it helps anyone, I used this to replace the "\r" with 2 spaces in  
> the field before adding to my string for the export file:
>
> var:'desc'=(string:(field:'description'));
>
> if:($desc->find:'\r');
> ($desc->(replace: '\r', '  '));
> else;
> /if;
>
> James
>
> James Sheffer          [hidden email]
> Lasso Developer        http://www.higherpowered.com
> phone:  469-256-0268   Toll Free: 1-866-301-2545
>
>
> On Dec 20, 2008, at 12:12 AM, Bil Corry wrote:
>
>> Steve Piercy - Web Site Builder wrote on 12/20/2008 12:05 AM:
>>> Dejá vu!
>>>
>>> http://www.nabble.com/Dealing-with-text-to20662321.html#a20864833
>>>
>>> In summary, use any one or a combination of the following.
>>> * Tell users to use OpenOffice instead of Excel.
>>> * Normalize line breaks before they go into MySQL.
>>> * Normalize line breaks as they are exported into Excel.
>>>
>>> Details are contained in the thread, linked above.
>>
>> I leave to get a sandwich, come back and am scooped...
>>
>>
>> - Bil
>>
>>
>> --
>> This list is a free service of LassoSoft: http://www.LassoSoft.com/
>> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
>> Manage your subscription: http://www.ListSearch.com/Lasso/
>>
>
>
>
>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>




--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: Line breaks in MySQL field breaking Excel download file.

Bil Corry-3
In reply to this post by Jim Sheffer-3
James Sheffer wrote on 12/20/2008 11:49 AM:
> If it helps anyone, I used this to replace the "\r" with 2 spaces in the
> field before adding to my string for the export file:
>
> var:'desc'=(string:(field:'description'));
>    
>     if:($desc->find:'\r');
>         ($desc->(replace: '\r', '  '));
>     else;
>     /if;

If you want something that will handle all three types of line endings (\r, \n, \r\n), then you can do this instead:

        var:'desc'=(lp_string_linefeedSet:(field:'description'),'  ');

Tag here:

        http://tagswap.net/lp_string_linefeedSet


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/