["\r\n" new line command in CSV file]

C

Cathy

[Briefing]
I got an Project export Text into the CSV file type.

However i face a problem that if my CSV data contain multiple line(eg : "
sleep\r\nHello\r\n"), and open this in the CSV file. We notice that a empty
space is appended behind the new line.

[Result]
sleep<Space>
Hello<space>


If i open this CSV file with notepad, this issue doesnt happen.


[Research]
I had do some similar research

1. Open Outlook contact, at the address field, i break the address into a
few line and export the contact into the CSV file.
- The Outlook CSV file having the same behavior as mention above

2. Open a Notepad and type below text into the notepad.
"CAV

CAV@

CAV@"

and save the notepad into the csv type and then open the CSV file with excel.

- The notepad CSV file also having the same behavior as mention about.


[Question]
why excel appended an empty space behind the "\r\n"? It is a bug for excel?
 
C

Chip Pearson

Does the CSV file actually contain the sequence of characters

sleep\r\nHello\r\n

or are you just using \n a shorthand in the message to indicate a new
line?

If the file contains the actual character string above, Excel doesn't
recognize \n as a new line indicator and thus then entire character
string will appear in one cell since there is no comma.

Also, do you know what character the Project export procedure uses to
indicate a new line? Is it Chr(10) or Chr(13) or Chr(10)Chr(13) ?

You might have to change the file extension from .csv to .txt and use
the text import wizard to import the file. Or, you can simply write
your own import procedure and deal with line terminators and spaces as
necessary. The code at http://www.cpearson.com/Excel/ImpText.aspx
should give you a starting point.

You might try posting a few lines of the actual data in the exported
file.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top