Problems while exporting data using pipe symbols

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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


First Name:       Mark [Text Field].
Second Name:  Heyden ]Text Field].
Address:           xyz  (Enter Key pressed)
                        abc, (Enter Key pressed)
                        def.  (Enter Key pressed)

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
              I would appreciate if some one can give me the solution
how i can get rid of the situation.

M Heyden.

Re: Problems while exporting data using pipe symbols wrote:

Quoted text here. Click to load it

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  
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.)

Erwin Moller

Site Timeline