CSV Import in Excel v. X

[Steffen Poertner]

Hello ...

We want to switch to some Apple Powerbooks here in the company and have one
machine here with the Office X Trial version. The problem now is, that we
cant import csv files. When i try to open the file with Excel, ist grey and
unavailable. Even the Icon of file is no ms excel icon. When i save a normal
xls file as csv, it has the nice Excel v X icon, but when i simply open it
with text edit and save it again, the icon disappears. When i export an
Excel file from a pc i cant import it too. I dont know why. Can someone help
us? Importing csv data into excel v. x is most important for us.


Thanks alot,
Steffen

 

[J.E. McGimpsey]

We want to switch to some Apple Powerbooks here in the company and have one
machine here with the Office X Trial version. The problem now is, that we
cant import csv files. When i try to open the file with Excel, ist grey and
unavailable. Even the Icon of file is no ms excel icon. When i save a normal
xls file as csv, it has the nice Excel v X icon, but when i simply open it
with text edit and save it again, the icon disappears. When i export an
Excel file from a pc i cant import it too. I dont know why. Can someone help
us? Importing csv data into excel v. x is most important for us.

The icon really shouldn't matter - XLv.X can import text .csv files.

First, make sure the Show: dropdown at the top of the File/Open
dialog is set to All readable files.

If that doesn't work, can you use the import wizard (Data/Get
External Data/Import Text file)?

I don't think there's anything crippled about test drive that would
prevent reading .csv, but I don't know that for a fact.

 

[Jess Girard]

The icon really shouldn't matter - XLv.X can import text .csv files.

First, make sure the Show: dropdown at the top of the File/Open
dialog is set to All readable files.

If that doesn't work, can you use the import wizard (Data/Get
External Data/Import Text file)?

I don't think there's anything crippled about test drive that would
prevent reading .csv, but I don't know that for a fact.

This issue comes up repeatedly because for some reason Microsoft
doesn't make it clear how to open CSV files. Here's the way I do it:

Within the File/Open dialog dropdown, set the file type to "All
Documents", NOT "All Readable Documents" - that won't work, I just
tried again a minute ago to be sure. The "All Documents" option is the
very last one listed in the dropdown.

The name of the CSV file will still be grayed out, but double-clicking
WILL open it.

By the way, I notice that CSV files created by Ecxcel v.X will open
properly without the workaround mentioned above. I wish Microsoft
would make an effort to standardize the way their products work; it
can be quite confusing sometimes!

Jess Girard

 

[J.E. McGimpsey]

This issue comes up repeatedly because for some reason Microsoft
doesn't make it clear how to open CSV files. Here's the way I do it:

Within the File/Open dialog dropdown, set the file type to "All
Documents", NOT "All Readable Documents" - that won't work, I just
tried again a minute ago to be sure. The "All Documents" option is the
very last one listed in the dropdown.

The name of the CSV file will still be grayed out, but double-clicking
WILL open it.

By the way, I notice that CSV files created by Ecxcel v.X will open
properly without the workaround mentioned above. I wish Microsoft
would make an effort to standardize the way their products work; it
can be quite confusing sometimes!

Hmm.. I can't quite replicate your results. Any text file I make
comes up in All Readable Documents, and it's clearly selectable,
rather than grayed out. Makes me wonder if it isn't the creator code
rather than the extension that's the key, in which case, I don't
know whether to blame MS or Apple, since Office v.X uses standard OS
X dialogs...

 

[Jess Girard]

Hmm.. I can't quite replicate your results. Any text file I make
comes up in All Readable Documents, and it's clearly selectable,
rather than grayed out. Makes me wonder if it isn't the creator code
rather than the extension that's the key, in which case, I don't
know whether to blame MS or Apple, since Office v.X uses standard OS
X dialogs...

As I mentioned, when I use Excel v.X to create a CSV file it opens it
immediately as if it were a native Excel-formatted file. But not if
the CSV file was created by a non-Mac version of Excel.

We receive CSV files daily from a firm who creates them using a
mainframe system. I've created VBA code to sort them, reformat them,
do some "@LOOKUP" operations, print daily reports and save them as XLS
files, each with a name based on the date the incoming CSV file was
created.

Several references I've checked today say that when moving files from
Windows to MacOS systems it is not unusual that creator codes are
absent or damaged. I understand Apple's intent is to make the system
easier to use, but it remains frustrating to many people when these
things happen. I noticed numerous references going back through time
of the very same question with various suggested workarounds in this
very forum, a few of which work on my system while others simply do
not. FYI I'm running a 15" 500MHz G4 Titanium PowerBook with lots of
RAM.

Over the years I've used three or four generations of Windows-based
Excel software and all easily open any CSV file I have ever
encountered, while Mac v.X Excel will not open received CSV files
unless treated as I mentioned. This seems to be a typical problem for
users of Office v.X. Almost anything I do with Word v.X, Excel v.X
and others has a different look and works differently from Windows
versions of Excel. Seems either a stunning mistake or a deliberate
policy by MS. Presumably MS doesn't make too many stunning mistakes,
so one is forced to the latter conclusion but we can only speculate
about their intent. Maybe it's as simple as the thought that Mac users
want to see something that looks different from what the "masses" use.

 

[J.E. McGimpsey]

Over the years I've used three or four generations of Windows-based
Excel software and all easily open any CSV file I have ever
encountered, while Mac v.X Excel will not open received CSV files
unless treated as I mentioned. This seems to be a typical problem for
users of Office v.X. Almost anything I do with Word v.X, Excel v.X
and others has a different look and works differently from Windows
versions of Excel. Seems either a stunning mistake or a deliberate
policy by MS. Presumably MS doesn't make too many stunning mistakes,
so one is forced to the latter conclusion but we can only speculate
about their intent. Maybe it's as simple as the thought that Mac users
want to see something that looks different from what the "masses" use.

I don't presume to know what "MS" is thinking - though I know some
of the people who work at MacBU and I can't say I've ever detected
any deliberate policy to disenfranchise Mac users.

As far as Office v.X goes, I know there was a deliberate effort to
make Office a Mac product, complying with the Aqua Human Interface
Guidelines and to use native OSX dialogs for interfacing with the OS
(with some successes and some failures). So of course there was some
sacrifice of directly comparable operation. Feedback sent to MacBu
(Help/Feedback on Excel...) will help them calibrate that tension in
future versions, certainly.

 

[Jim Gordon]

Hi

I just tested the following scenario on my mac to try to replicate your
situation. I created a csv file in Excel 2003 (Windows XP) and saved it
directly to a shared folder on my Mac. I closed the file in Windows. The
file in MacOS automatically had the Excel icon and opened in Excel when I
double-clicked it.

The I made a csv file from XL Windows and saved it to to the Windows XP
desktop. I closed XL then I copied the file to the Macintosh. In MacOS the
file had the Excel icon and opened in Mac XL simply by double-clicking.

So there's nothing inherent about XL for Windows and MacOS not being able to
recognize the files made in Windows.

Mac OSX has the ability to assign file type associations. Try this: On one
of the csv files that is not associated with Mac Excel single click on the
icon in Finder. From the menu choose File > Get Info. In the Open With
dialog set it so that MacOS uses Excel for csv files then click the "change
all" button. MacOS will then use the file extension to associate csv files
with Excel. It may take a while for all the file icons to be changed.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

----------

 

[Roger Morris]

Was the CSV file created by Excel? There was a mention of it coming
from a mainframe.

If a csv file is created by a text editor (or presumably any non Excel
program) then I find it can be opened by excel either by dragging to the
dock icon or by using File > Open.

If the filename has the extension ".csv" then it opens properly
whichever of these two methods is used.

If there is no extension then dragging to dock opens it as a text file
ie ignoring comma delimited fields but with File > Open, Excel will
offer delimiter options to open the file correctly.

- Roger

 

[Jim Gordon]

Hi Roger,

I just downloaded a csv file that's generated by a web server and now I see
what they are talking about.

MacOS will not let you set the file association for csv to Excel! How
ironic.

This needs to be reported to both Microsoft and Apple. I can't tell which
one needs to do what to fix this, but it sure is odd.

Anyone who sees this should report it ASAP. The URLs to report (use both)

Microsoft:
http://register.microsoft.com/mswish/suggestion.asp

Apple:
http://www.apple.com/macosx/feedback/

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

----------

 

[Roger Morris]

Hi Roger,

I just downloaded a csv file that's generated by a web server and now I see
what they are talking about.

MacOS will not let you set the file association for csv to Excel! How
ironic.

Really?

hadn't tried that before but now that I have tried it I find that it
does work. (Using Get Info > Open with ...)

Perhaps we are using different OSs? - I have Mac OS 10.2.3 and
MS Office 10.1.5

So
Make extension ".csv" (without the quotes) and double click to open in
Excel.

[snip]

[snipped a lot]

Roger

 

[J.E. McGimpsey]

Hi Roger,

I just downloaded a csv file that's generated by a web server and now I see
what they are talking about.

MacOS will not let you set the file association for csv to Excel! How
ironic.

This needs to be reported to both Microsoft and Apple. I can't tell which
one needs to do what to fix this, but it sure is odd.

Jim - could you send me a copy of that file? I can set .csv to open
in XL just fine from MacOS, but it doesn't then (necessarily) show
up as active with "All readable files" selected in XL's File/Open
dialog.

I've gotten myself wrapped around the axle on this - it appears to
me that the dialog uses the creator code to determine what's
readable, but I'm not sure. I'll have to set up a test matrix.

 

[Jim Gordon]

John, this is weird.

Earlier in the day I set MacOS to always open .csv files with TextEdit. I
tried to then set them to Excel, but it would not let me.

Just now I tried to change the association of the stubborn .csv file to
Excel and MacOS let me do it without a problem!

So maybe that's the solution. Change the csv file association to TextEdit in
GetInfo. Change them all to TextEdit. A few hours later do it again and
change them to Excel. It worked for me for some reason.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

----------