Mac Excel Tab-Delimitied Text Files

[scott boettcher]

We¹re having issues with Mac Excel (X and 2004) saving tab-delimited text
files that are also used by PCs.
The issue is that when the PCs open them and try to upload them to a server
for data import, there are extra characters inserted by the Mac versions of
Excel that cause errors.

I can¹t even see these characters unless I view them on a PC.

Can someone shed some light on this? It would seem that this very simple
operation would work ­ and no errors are found in the built-in compatibility
checker...

Many thanks,
Scott

 

[Fredrik Wahlgren]

Mac Excel Tab-Delimitied Text Files
We're having issues with Mac Excel (X and 2004) saving tab-delimited text files that are also used by PCs.
The issue is that when the PCs open them and try to upload them to a server for data import, there are extra characters inserted by the Mac versions of Excel that cause errors.

I can't even see these characters unless I view them on a PC.

Can someone shed some light on this? It would seem that this very simple operation would work - and no errors are found in the built-in compatibility checker...

Many thanks,
Scott

What is there in the cells where you get these extra charactes? I believe these are newline characters. The easiest way to get rid of them is trobably by opening the files in notepad and do a Find & Replace.
/Fredrik

 

[Bob Greenblatt]

Try saving as Text (MS DOS).

 

[scott boettcher]

Thanks guys.
They get lists of email addresses either embedded in emails, or in PC Excel
files.
It might look like this (below) this is copied into Excel and saved. On the
PC, it¹s fine, on the Mac, it adds characters ­ but you can only see those
extra characters when opening on a PC.
I will try the DOS TXT format next. (addresses are phony)

email
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)



On4/8/05 7:34 AM, Bob Greenblatt said:BE7C0EAC.39BE0%[email protected]

 

[Bernard Rey]

There is a (slight) difference in the Asc Codes added (though they seem to
bear the same ASCI code when pasted in Excel).

In fact, you'll have the exact equivalent to the file obtained in Windows
when saving your file as a Windows TEXT file. A DOS TEXT file will give you
the same (bad) result you have already had. The difference can be seen in
Windows Notepad, as in one case (Tab delimited or DOS formatted text) there
is no line feed after each address, just a "square" character, where there
is a line feed in the other (Windows Formatted text or Excel 2003 created
tab delimited file).

But I have to say that opening the different files with Excel 2003 (in a
Virtual PC Windows XP Pro machine) made no difference and there were no
"invisible characters" to be found in any case. So it may be different from
one version of Windows/Office to another...

 

[Francesca]

When I import or open .txt files from Mac on Excel for Windows in
Windows: I change on the first Textassistant-Question-Windows the
"Dateiursprung" (i.e where the file comes from) to *Macintosh*, then all
the characters are OK on Excel for Windows. (viceversa when I open on
Mac .txt from Windows)
Francesca

 

[Michael Vilain]

When I import or open .txt files from Mac on Excel for Windows in
Windows: I change on the first Textassistant-Question-Windows the
"Dateiursprung" (i.e where the file comes from) to *Macintosh*, then all
the characters are OK on Excel for Windows. (viceversa when I open on
Mac .txt from Windows)
Francesca

when I code php, I have to determine the client I'm creating the Excel
file for so I can output the correctly EOL.

On MacOS, it's "\r".
On Unix, it's "\n".
On DOS/Windows, it's "\r\n".

 

[scott boettcher]

Correct, Bernard.
Here's the deal - these Excel, or Tab-delimited files are being uploaded to
a website for metadata creation - the extra "square" characters cause the
site to error. I don't know what the back end is, or how the data is
captured, but those squares are the problem.
I did discover yesterday that saving as Text (Windows) works - as you
stated.
I do find it odd though that a Tab-delimited save adds this extra character
only on the Mac. It happens in Office X and Office 2004>

Thanks again - I'd like to know why this does this.

SB


On4/9/05 2:51 AM, Bernard Rey said:BE7D722A.FC90%[email protected]

 

[Paul Berkowitz]

Correct, Bernard.
Here's the deal - these Excel, or Tab-delimited files are being uploaded to a
website for metadata creation - the extra "square" characters cause the site
to error. I don't know what the back end is, or how the data is captured, but
those squares are the problem.
I did discover yesterday that saving as Text (Windows) works - as you stated.
I do find it odd though that a Tab-delimited save adds this extra character
only on the Mac. It happens in Office X and Office 2004>

Thanks again - I'd like to know why this does this.

When saving to tab text in Excel Mac, the standard Mac end-of-line character
- namely the carriage return, ASCII 13 - is used. When this text file is
read on Windows, these characters do NOT represent end-of-line characters:
on Windows, text files (but not Word documents) require the combined CR LF
(ASCII 13 & ASCII 10) character sequence to demarcate lines (paragraphs). So
the single ASCII 13 character is just a "non-printing" character. The square
(rectangle) how some non-printing characters appear on screen in a text
editor app such as Notepad on Windows. If you tried printing the text file
on Windows, you wouldn't see the squares nor would you see separate lines:
all the text would just run on in one long line. Your server is expecting
separate lines, and will cough on this immense single line with dozens of @
characters.

When you save the text file as Text (Windows) in Excel Mac, the LF (ASCII
10) character is appended to each CR (ASCII 13) character. If you then open
this file in a non-Cocoa traditional Mac app such as Tex-Edit Plus, you will
now see rectangles at the beginning of each line starting with the second
line. Of course on the Mac, the CR characters still make separate paragraphs
(lines), but each new line now begins with a non-printing character (ASCII
10) displayed as a rectangle. If you print the Tex-Edit document, you won't
see the rectangles. If you open the Windows file in TextEdit, you won't see
the rectangles since TextEdit is a Unix app that uses only LF (ASCII 10)
characters itself, and is "cosmopolitan": it will accept and display any of
LF, CR, CRLF as line-ends and not show any squares.

Nevertheless, both the CR and LF characters remain in the file, and that's
what your Windows uploading app wants and expects. It doesn't know what to
do with the "single paragraph" you're handing it when you give it a
Mac-formatted text file.


If you ever end up with these Mac text files on Windows, try opening them in
Word, saving as Word documents, re-opening in Word and saving as Text Only.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[scott boettcher]

Paul,

Many thanks for the excellent explanation!
That¹s what I needed to understand AND explain to others...
I hope you don¹t mind me cutting and pasting into my personal FM Pro
database of those wonderful things I learned and didn¹t know?!

Best,
ScottB


On4/12/05 12:46 AM, Paul Berkowitz
said:BE80CB00.92CBC%berkowit@spoof_silcom.com