Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option for opening Excel .csv in text vs. general
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/comm...lic.excel.misc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option for opening Excel .csv in text vs. general
It also strips the leading zeros in a .txt file.
"Gord Dibben" wrote: 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 wrote: 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/comm...lic.excel.misc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option for opening Excel .csv in text vs. general
Not if you specify the relevant columns as text, which was the point which
the OP was making. -- David Biddulph marcuslayton wrote: It also strips the leading zeros in a .txt file. "Gord Dibben" wrote: 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 wrote: 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/comm...lic.excel.misc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option for opening Excel .csv in text vs. general
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 On Mon, 2 Nov 2009 11:36:01 -0800, marcuslayton wrote: It also strips the leading zeros in a .txt file. "Gord Dibben" wrote: 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 wrote: 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/comm...lic.excel.misc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option for opening Excel .csv in text vs. general
John Padden Racine wrote on 10/06/2009 19:13 ET :
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/comm...lic.excel.misc 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!!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option for opening Excel .csv in text vs. general
on 4/24/2011, paddy supposed :
John Padden Racine wrote on 10/06/2009 19:13 ET : 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/comm...lic.excel.misc 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!!! 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option for opening Excel .csv in text vs. general
On Apr 24, 12:11*pm, paddy wrote:
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. 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/...ry/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" ;-). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to disable General formatting when opening CSV | Excel Discussion (Misc queries) | |||
General option option not available | Excel Discussion (Misc queries) | |||
Set default column data format in Excel to text NOT general | Setting up and Configuration of Excel | |||
Format: General - Text - General | Excel Worksheet Functions | |||
No option to password protect opening an excel / word file | Excel Discussion (Misc queries) |