Option for opening Excel .csv in text vs. general

  • Thread starter Thread starter John Padden Racine
  • Start date Start date
J

John Padden Racine

As a software developer who integrates Microsoft Excel using .csv files, I'd
like to see the ability to specify an environment option which would cause
all .csv files to be loaded as text rather than general. I'm not talking
about "importing"; the way that works is fine. I'm talking about opening a
..csv file in Excel, which automatically uses general importation. This
strips leading zeroes which can be counter to what is desired from a download
from a server. These are often IDs/Keys to records. Once opened as text,
columns which are numeric could then be specified as such by the user, but
when opened as general, the leading data is lost. Opening .csv files is the
usual way when automating download and launch of a .csv in Excel; importation
is much more difficult to automate. Note: I am also a professor who teaches
Microsoft Office at Wesley College, DE.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...f-e27c35ad4c1e&dg=microsoft.public.excel.misc
 
Until that happens most of us simply change the CSV extension to TXT and go
from there.


Gord Dibben MS Excel MVP

On Tue, 6 Oct 2009 16:13:03 -0700, John Padden Racine <John Padden
 
It also strips the leading zeros in a .txt file.

Gord Dibben said:
Until that happens most of us simply change the CSV extension to TXT and go
from there.


Gord Dibben MS Excel MVP

On Tue, 6 Oct 2009 16:13:03 -0700, John Padden Racine <John Padden
Click to expand...
 
When you open a *.txt file in Excel the Text Import Wizard pops up and you
are able to specify the data type in step 3

Have you tried it?


Gord
 
John Padden Racine wrote on 10/06/2009 19:13 ET :
Hi, this is John, who posted the original question. I appreciate your
replies,
but none of you are really getting the gist of the problem. I'm interested
in automating the process of loading data into a spreadsheet from a network
application, not doing a manual process. I already know how to change file
types and change column types, etc. Since the data can be very different
from one download to another, i can't even use a macro to accomplish this.
The application downloads data into a .csv file, then automatically invokes
Excel to
open that .csv file. At that point, it loses the leading zeroes. I want to
minimize the work that the user has to do. Besides, the user may not be
completely aware which columns need to be adjusted to keep the leading
zeroes, until he or she has perused the data for a while. This may seem
like an obscure
problem to many of you, but I encounter it everywhere I go. This is about
office automation. Thanks!!!
Click to expand...

Hi John,

I'm a bit confused about your statement that "This is about office
automation." You claim that the network app "...invokes Excel to open
that .csv file." What's confusing is:

Does the network app run an automated instance of Excel?
OR
Does it use a command line statement like "Excel.exe my.csv"?

If the network app is automating its own instance of Excel then it
should be able to control the problem with code in the network app when
it writes to the csv.

If simply opening via command line statement then you might want to
consider a different approach; use a VBA solution to open the file and
manage the values being inserted. This way you can query the first
character of fields with suspect leading zeros.
 
Hi, this is John, who posted the original question.
 I appreciate your replies, but none of you are really
getting the gist of the problem.
Click to expand...

18 months later, and you are still grousing about the same problem!

I don't know who you think you are talking to, but participants in
these newsgroups have nothing to do with Microsoft and its product
policies.

AFAIK, Microsoft employees never participated in the Usenet
newsgroups, which is where your current posting went directly.

Even in the Microsoft Discussion Groups where you posted your original
complaint, there was never very much, if any, participation by
Microsoft product developers and planners.

The voting mechanism was a joke. I doubt that Microsoft ever paid
attention to it; and if they did, it took far too many votes for them
to act on a suggestion.

If you still teach Microsoft Office at the college level, you should
be aware by now that Microsoft has discontinued the original
Discussion Groups, which were mirrored in these Usenet newsgroups.
The DG has been replaced by the Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
The Answers Forums are no longer distributed to other systems, notably
not the Usenet newsgroups.

But don't get your hopes up. Even though I have seen some (very
little) participation by Microsoft support engineers, I doubt that
they will take any feedback back to the Excel development team.

IMHO, Microsoft does not care about its customers. I think that is
very apparent by the lack of relevant changes over the years and,
equally notable, by the number of irrelevant changes that result in a
new round of customer complaints each time.

(AFAIK, XL2010 is the first Excel version in many years in which
Microsoft implemented functionality changes specifically to address
the repeated complaints from the academic community.)

That said, of course we get the gist of the problem. We all have
suffered with it for many years. Most of us realize that we have no
influence over Microsoft, so we just live with it. There is nothing
else we can do.

Pursuant to a solution, you might look into using XML files instead of
CSV and TXT files.

I confess that I do not know much about XML files myself. But I
believe that they include tags that can correctly type text data so
that it is preserved.

Of course, whether or not Excel creates XML files with the correct
data types and whether or not Excel correctly handles data type tag
when a file is opened is another story. I don't know. I am skeptical
simply because Microsoft is Microsoft.

Another possible alternative: Open Office. Normally, I would not
recommend it as a matter of principle. I have no direct experience;
but based on comments from users, Open Office is not entirely
compatible with MS Excel.

But I do believe that I've read users comments that lead me to suspect
that Open Office preserves text data in CSV files.

Sounds like fodder for research by a professor who teaches Microsoft
Office. Good luck with that!

PS: __I__ can read, and I do note that you said quite clearly that
since "the data can be very different from one download to another, i
can't even use a macro to accomplish this" ;-).
 
Back
Top