Posted by Erwin Moller on 02/03/06 16:35

mark.heyden@gmail.com wrote:

> Hi Frends,
>
> I am facing problems while trying to export data into
> pipe format. In fact I am fetching records from mysql database and then
> trying to create a text file ( using pipe as delimeter) , intending to
> open in Excel.
>
> The data is been inserted from textarea fields, where the
> user can write text and may press enter key to format his text
> accordingly. Now when the data entered into the database it keeps some
> unidentified value so as to keep the formatting. Hence while displaying
> the text file I find that lines are seperated according to the format
> user gave. Thus creating the problem while opening up in the excel
> file.
>
> example:
>
> First Name: Mark [Text Field].
> Second Name: Heyden ]Text Field].
> Address: xyz (Enter Key pressed)
> abc, (Enter Key pressed)
> def. (Enter Key pressed)
> [TextArea]
>
> Hence the record I expect should be in the text file like,
>
> Mark | Heyden | xyz abc,def.
>
> But I am getting it as
>
> Mark | Heyden | xyz
> abc,
> def.
> I would appreciate if some one can give me the solution
> how i can get rid of the situation.
>
> Thanks,
> M Heyden.


Hi Marc,

I was in the same situation and solved it the poor-man's way. :-)
My problem was that different Excel-versions had different ideas about the
formatting.
My first solution was placing ALL fields between "", and I hoped the ""
could contain the newline.
It turned out that that strategy didn't work out as desired everywhere.
(Depending on Excelversion, import-settings, OS, etc)

I tried tab-delimiter: same problem.

So I changed strategy (to poor man's solution).

Before writing to file:
1) replace \n by **NEWLINE** (or whatever characters you do not expect in
the real textfields)
Now you do not have the annoying newline that makes Excel 'think' you import
a new row.

2) Just import them into excel as usual.

3) In Excel I made a miniscule macro that replace any **NEWLINE** with \n in
all fields. (Just a replace in VBscript)
That macro was placed under a button 'format me' in the excellsheet.

I know this solution sucks, but it worked.

I expected the customer to say: 'Your solution sucks, I do not want to press
your stupid button!', but they thought the button was actually cool, so I
got away with it. ;-)

Not sure if it helps in your situation.

If you know what the targetenvironment is (OS, excellversion,etc) you might
try to find an encoding and delimiter that works for that situation. (like
using , as delimitter and place all fields in ''). In some situation excell
accepts the newline within quotes, but I do not remember the details.)

Regards,
Erwin Moller

[Back to original message]