How does Excel make it's guess as to what type of file a CSV file is?

  • Thread starter Thread starter tharpa
  • Start date Start date
T

tharpa

I have a CSV file (not created with Excel.) If a user double-clicks
on the file, Excel wrongly guesses that it's not a true Comma-
Separated-Value file, but a fixed-width file. Thus to the user it
will display incorrectly.

How does Excel decide what to make it's first guess as to what kind of
file a CSV file is?

If I knew, I could probably tailor the file so Excel would guess
correctly.

Thanks.
 
Do you have quotes in the .csv file around each field of text?

I have always imagined that Excel treats a double-clicked CSV file as
if you were typing very quickly on the keyboard, and a comma (where it
is not contained within quotes) is interpreted as equivalent to the
<enter> key being pressed, i.e. make sense of what has just been typed
then move to the next cell horizontally to get the next piece of
typing until the end of record marker is encountered. Thus a text
value like 01234 in a column is treated as if you typed it in, and
Excel's conclusion is that as this only contains numeric digits then
it must be a number and so it gets rid of the leading zero. Similar
(wrong) action is taken when it encounters something like 08/06 (which
might be some stock code in reality), but which is interpreted by
Excel as a date.

I don't know if this is a correct analogy, but it helps me to
understand what goes on.

I haven't come across a situation where it treats the data as fixed
width, but one way around it is to rename the file as .txt, then from
within Excel do File | Open and select the file from the browser. This
will take you automatically into the Text Import Wizard, where you
have far more control over how Excel imports the data. If this is a
regular and frequent occurrence for you, then you can record a macro
while you do it once and then replay it when required in future.

Hope this helps.

Pete
 
CSV stands for comma separated values and it's a file type MS designated
many years ago. It is not fixed width and you cannot give that extension to
a fixed width file and declare it to be a CSV. You have to play by the
rules. Try another extension like TXT or PRN but then you'll have to go
through the Text Import Wizard or use a macro that uses the OpenText method.

--
Jim
|I have a CSV file (not created with Excel.) If a user double-clicks
| on the file, Excel wrongly guesses that it's not a true Comma-
| Separated-Value file, but a fixed-width file. Thus to the user it
| will display incorrectly.
|
| How does Excel decide what to make it's first guess as to what kind of
| file a CSV file is?
|
| If I knew, I could probably tailor the file so Excel would guess
| correctly.
|
| Thanks.
 
CSV stands for comma separated values and it's a file type MS designated
many years ago.  It is not fixed width and you cannot give that extension to
a fixed width file and declare it to be a CSV.  You have to play by the
rules.  
Click to expand...

I figured out the problem. The problem is that I am playing by the
rules and Microsoft is not. The file is both a CSV (comma-separated-
values), and happens (but irrelevantly) to be a fixed width file.
Microsoft assumes that you are wrong, that it is not a true CSV file,
and then tries to rule that out by checking to see if it is fixed-
width. If it is fixed-width, MS assumes it is not a CSV file, which
may be correct in most cases but in this case is incorrect. So in
order to get Excel to handle it correctly, all we have to do is make
it so that it is not only a true CSV file (which it already is), but
also not a fixed width file.

Problem solved by bug workaround.

<Try another extension like TXT or PRN but then you'll have to go
through the Text Import Wizard or use a macro that uses the OpenText method.
Click to expand...

The user wants it as a .CSV file.

Thanks anyway.
 
A properly formed CSV file has to follow certain rules.
If the CSV file is improperly formed, Excel can guess at its structure.

For example, last year a well known financial firm, that was using an
imprpoperly formed CSV forat, suddenly changed their format to a very badly
formed CSV file.

I was able to modify my program to handle both formats.

Excel made some good guesses.
In your case, I suspect that the file is just badly formed.
 
Howard Kaikow said:
A properly formed CSV file has to follow certain rules.
If the CSV file is improperly formed, Excel can guess at its structure.

For example, last year a well known financial firm, that was using an
imprpoperly formed CSV forat, suddenly changed their format to a very badly
formed CSV file.

I was able to modify my program to handle both formats.

Excel made some good guesses.
In your case, I suspect that the file is just badly formed.
Click to expand...
 
I am having a problem with downloading watch lists and fund data from a "well
known financial firm". For a long time Excel handled watch lists properly,
but imported fund data as text. A couple days ago, my c: drive died and I
have started over on a new drive. I also upgraded to Windows XP. Now Excel
imports watch lists as text and fund data is loaded properly. The "well
known financial firm" was no help as there system handled both properly. I
noticed that the properties of fund data is file.csv and watch lists are
file.csv?. I added a file association for csv?, but it did not help.
I would like to load both files as Excel data (numbers/text in columns).
 
Back
Top