Comma delimiter for csv downloads?

[Les M]

I have recently upgraded to Windows XP Home edition and have reinstalled all
my applications. I regularly download csv files from one particular site and
use them in Excel, but now they always download with all data in column A (in
the past they downloaded correctly as csv files, using separate columns).

In Control panel/Regional settings, comma is specified as the list
separator, but this is not having the required effect.

I can go through the "Data/Text to Columns/Delimited/comma" process each
time I download (and that process works), but I would like the default to
operate as it should.

Any helpful advice would be appreciated.

[Nick B]

If you open one of the files in Notepad, before opening in Excel, can you
verify that the files have commas and not another delimiter?
Are the files being saved with the .csv extension, or by any chance are they
being saved with .csv.txt extension? (i.e. the actual extension is being
hidden -- open My Computer, Tools->Folder Options, View tab and verify that
Hide extensions for known file types is NOT selected.)

"Les M" wrote:

> I have recently upgraded to Windows XP Home edition and have reinstalled all
> my applications. I regularly download csv files from one particular site and
> use them in Excel, but now they always download with all data in column A (in
> the past they downloaded correctly as csv files, using separate columns).
>
> In Control panel/Regional settings, comma is specified as the list
> separator, but this is not having the required effect.
>
> I can go through the "Data/Text to Columns/Delimited/comma" process each
> time I download (and that process works), but I would like the default to
> operate as it should.
>
> Any helpful advice would be appreciated.

[Les M]

Nick,

The files have commas, and when Excel is using comma as delimiter all is
well. But I have to go through the process of Data/Text to
Columns/Delimited/comma to get Excel to recognise the commas as delimiters -
even though comma is specified in Regional settings as the list separator.

I normally download and open the files without saving them, so at the time
of opening, there isn't a file extension. However, if I download and try to
save, they try to save as "Text (Tab Delimited)". If I override that and
force them to save as .csv, then they will open correctly in future.

What I have always done is simply download and open, use the info (stock
prices) and then exit without saving the file at all. And that's what I'd
like to continue to do. But since moving to Windows XP and reinstalling
Office, it seems that Excel just won't work that way.

"Nick B" wrote:

> If you open one of the files in Notepad, before opening in Excel, can you
> verify that the files have commas and not another delimiter?
> Are the files being saved with the .csv extension, or by any chance are they
> being saved with .csv.txt extension? (i.e. the actual extension is being
> hidden -- open My Computer, Tools->Folder Options, View tab and verify that
> Hide extensions for known file types is NOT selected.)
>
> "Les M" wrote:
>
> > I have recently upgraded to Windows XP Home edition and have reinstalled all
> > my applications. I regularly download csv files from one particular site and
> > use them in Excel, but now they always download with all data in column A (in
> > the past they downloaded correctly as csv files, using separate columns).
> >
> > In Control panel/Regional settings, comma is specified as the list
> > separator, but this is not having the required effect.
> >
> > I can go through the "Data/Text to Columns/Delimited/comma" process each
> > time I download (and that process works), but I would like the default to
> > operate as it should.
> >
> > Any helpful advice would be appreciated.

[Nick B]

Interesting. Sounds like, perhaps, a change on the server. Can you provide us
with a link to a sample file? If the file does not have an extension, then it
should be prompting you asking what program to open the file with. If it has
a CSV, then all should be well. Sounds like the files on the server have a
..txt extension instead perhaps. Any chance you can supply us with a link?

"Les M" wrote:

> Nick,
>
> The files have commas, and when Excel is using comma as delimiter all is
> well. But I have to go through the process of Data/Text to
> Columns/Delimited/comma to get Excel to recognise the commas as delimiters -
> even though comma is specified in Regional settings as the list separator.
>
> I normally download and open the files without saving them, so at the time
> of opening, there isn't a file extension. However, if I download and try to
> save, they try to save as "Text (Tab Delimited)". If I override that and
> force them to save as .csv, then they will open correctly in future.
>
> What I have always done is simply download and open, use the info (stock
> prices) and then exit without saving the file at all. And that's what I'd
> like to continue to do. But since moving to Windows XP and reinstalling
> Office, it seems that Excel just won't work that way.
>
> "Nick B" wrote:
>
> > If you open one of the files in Notepad, before opening in Excel, can you
> > verify that the files have commas and not another delimiter?
> > Are the files being saved with the .csv extension, or by any chance are they
> > being saved with .csv.txt extension? (i.e. the actual extension is being
> > hidden -- open My Computer, Tools->Folder Options, View tab and verify that
> > Hide extensions for known file types is NOT selected.)
> >
> > "Les M" wrote:
> >
> > > I have recently upgraded to Windows XP Home edition and have reinstalled all
> > > my applications. I regularly download csv files from one particular site and
> > > use them in Excel, but now they always download with all data in column A (in
> > > the past they downloaded correctly as csv files, using separate columns).
> > >
> > > In Control panel/Regional settings, comma is specified as the list
> > > separator, but this is not having the required effect.
> > >
> > > I can go through the "Data/Text to Columns/Delimited/comma" process each
> > > time I download (and that process works), but I would like the default to
> > > operate as it should.
> > >
> > > Any helpful advice would be appreciated.

[Les M]

Nick,

Thanks for your attention. I'm sorry but I expect the following will not be
much help.

The site is a major stockbroker in Australia, and one needs to be a
registered user to logon and download watchlist information, but I really
doubt that there has been a change on the server at the same time I upgraded
and reinstalled everything.

When downloading these files, the downloaded file normally opens in Excel
within an IE6 window and displays no filetype, etc in the heading. The first
indication I can get of filetype is if I try to save it and, as mentioned
earlier, it tries to save it as Text (Tab Delimited).

However, I have just tried the download with a "Save As" option, and before
I specify a file type, it has selected "csv". In fact, if I proceed with that
and save as csv, and then open the saved file, Excel works just fine!

So I obviously have another "workaround", but not the real thing (I just
want to view the file without saving it).

Thanks again for your interest.


"Nick B" wrote:

> Interesting. Sounds like, perhaps, a change on the server. Can you provide us
> with a link to a sample file? If the file does not have an extension, then it
> should be prompting you asking what program to open the file with. If it has
> a CSV, then all should be well. Sounds like the files on the server have a
> .txt extension instead perhaps. Any chance you can supply us with a link?
>
> "Les M" wrote:
>
> > Nick,
> >
> > The files have commas, and when Excel is using comma as delimiter all is
> > well. But I have to go through the process of Data/Text to
> > Columns/Delimited/comma to get Excel to recognise the commas as delimiters -
> > even though comma is specified in Regional settings as the list separator.
> >
> > I normally download and open the files without saving them, so at the time
> > of opening, there isn't a file extension. However, if I download and try to
> > save, they try to save as "Text (Tab Delimited)". If I override that and
> > force them to save as .csv, then they will open correctly in future.
> >
> > What I have always done is simply download and open, use the info (stock
> > prices) and then exit without saving the file at all. And that's what I'd
> > like to continue to do. But since moving to Windows XP and reinstalling
> > Office, it seems that Excel just won't work that way.
> >
> > "Nick B" wrote:
> >
> > > If you open one of the files in Notepad, before opening in Excel, can you
> > > verify that the files have commas and not another delimiter?
> > > Are the files being saved with the .csv extension, or by any chance are they
> > > being saved with .csv.txt extension? (i.e. the actual extension is being
> > > hidden -- open My Computer, Tools->Folder Options, View tab and verify that
> > > Hide extensions for known file types is NOT selected.)
> > >
> > > "Les M" wrote:
> > >
> > > > I have recently upgraded to Windows XP Home edition and have reinstalled all
> > > > my applications. I regularly download csv files from one particular site and
> > > > use them in Excel, but now they always download with all data in column A (in
> > > > the past they downloaded correctly as csv files, using separate columns).
> > > >
> > > > In Control panel/Regional settings, comma is specified as the list
> > > > separator, but this is not having the required effect.
> > > >
> > > > I can go through the "Data/Text to Columns/Delimited/comma" process each
> > > > time I download (and that process works), but I would like the default to
> > > > operate as it should.
> > > >
> > > > Any helpful advice would be appreciated.

[Dave Peterson]

Just some comments/questions...

First, I'd double check the regional settings list separator character--no extra
trailing space characters/junk(?).

Second, if you save the file to a .csv file on your harddrive and then
doubleclick on that filename in windows explorer, does excel open it correctly
(separate columns for each field)?

Third (just a guess), I think I'd try to reregister excel so that it knows that
it owns the .csv extension.

Close Excel and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to excel's
factory defaults.

Then test it out.

Good luck,


Les M wrote:
>
> I have recently upgraded to Windows XP Home edition and have reinstalled all
> my applications. I regularly download csv files from one particular site and
> use them in Excel, but now they always download with all data in column A (in
> the past they downloaded correctly as csv files, using separate columns).
>
> In Control panel/Regional settings, comma is specified as the list
> separator, but this is not having the required effect.
>
> I can go through the "Data/Text to Columns/Delimited/comma" process each
> time I download (and that process works), but I would like the default to
> operate as it should.
>
> Any helpful advice would be appreciated.

--

Dave Peterson

[Les M]

Dave,

Firstly, thanks for your interest. I think I've made some progress, but I'm
not sure where to go next. Following are answers to your questions, plus new
information that, I think, makes the real problem much clearer.

1. Comma is definitely the list separator (no spaces, etc). Also the
/unregserver and /regserver things didn't make any difference.

2. When I'm using IE6 and download using the "Open in a New Window" option,
all the data arrives in column A. When I try to "Save As" it wants to use
"Text (Tab Delimited)". When I change that to "CSV" and save, then reopen it,
I have the same problem. Reason for this is now obvious because I have opened
the file with Notepad and following are the first couple of rows....
"Name,Stock,Bid $,Offer $,Last $,Change,%,Open $,High $,Low $,Volume"
"M-W Fund - M-W
Fund,ABS,7.420,7.430,7.420,-0.140,-1.85,7.510,7.520,7.410,221210"

It's obvious, even to me, that the quotes are the problem. But that's not
the whole story...

3. When I'm using IE6 and I download using the "Save As" option, it saves a
csv file. Opening it in Windows Explorer opens in Excel, with everythinhg in
the correct columns. Following are the first two rows of the file saved that
way. There are no quotes.
Name,Stock,Bid $,Offer $,Last $,Change,%,Open $,High $,Low $,Volume
M-W Fund - M-W Fund,ABS,7.660,7.450,7.560,0.000,0.00,0.000,0.000,0.000,0

At this point I'm suspecting the download site, but then....

4. Perhaps I shouldn't mention this in this particular site, but I then
repeated 2. above using the Netscape browser. I downloaded using the "Open in
New Window" option, and used "Save As". It saved as a csv file (never
mentioned anything else), and when displayed using Notepad, there were no
quotes.

This implies to me that the quotes aren't there when downloaded. but are
being added as part of the IE6/Excel process, but not as part of the
IE6/Netyscape process.

Does any of this make any sense?







"Dave Peterson" wrote:

> Just some comments/questions...
>
> First, I'd double check the regional settings list separator character--no extra
> trailing space characters/junk(?).
>
> Second, if you save the file to a .csv file on your harddrive and then
> doubleclick on that filename in windows explorer, does excel open it correctly
> (separate columns for each field)?
>
> Third (just a guess), I think I'd try to reregister excel so that it knows that
> it owns the .csv extension.
>
> Close Excel and
> Windows Start Button|Run
> excel /unregserver
> then
> Windows Start Button|Run
> excel /regserver
>
> The /unregserver & /regserver stuff resets the windows registry to excel's
> factory defaults.
>
> Then test it out.
>
> Good luck,
>
>
> Les M wrote:
> >
> > I have recently upgraded to Windows XP Home edition and have reinstalled all
> > my applications. I regularly download csv files from one particular site and
> > use them in Excel, but now they always download with all data in column A (in
> > the past they downloaded correctly as csv files, using separate columns).
> >
> > In Control panel/Regional settings, comma is specified as the list
> > separator, but this is not having the required effect.
> >
> > I can go through the "Data/Text to Columns/Delimited/comma" process each
> > time I download (and that process works), but I would like the default to
> > operate as it should.
> >
> > Any helpful advice would be appreciated.
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Your message makes complete sense to me--but it's nothing that I know enough to
help.

I think I'd post in one of the internet explorer newsgroups and see if anyone
has a thought...

Or it could be the web page that looks at what browser you're using and does
things differently--again, that's over my head.

Les M wrote:
>
> Dave,
>
> Firstly, thanks for your interest. I think I've made some progress, but I'm
> not sure where to go next. Following are answers to your questions, plus new
> information that, I think, makes the real problem much clearer.
>
> 1. Comma is definitely the list separator (no spaces, etc). Also the
> /unregserver and /regserver things didn't make any difference.
>
> 2. When I'm using IE6 and download using the "Open in a New Window" option,
> all the data arrives in column A. When I try to "Save As" it wants to use
> "Text (Tab Delimited)". When I change that to "CSV" and save, then reopen it,
> I have the same problem. Reason for this is now obvious because I have opened
> the file with Notepad and following are the first couple of rows....
> "Name,Stock,Bid $,Offer $,Last $,Change,%,Open $,High $,Low $,Volume"
> "M-W Fund - M-W
> Fund,ABS,7.420,7.430,7.420,-0.140,-1.85,7.510,7.520,7.410,221210"
>
> It's obvious, even to me, that the quotes are the problem. But that's not
> the whole story...
>
> 3. When I'm using IE6 and I download using the "Save As" option, it saves a
> csv file. Opening it in Windows Explorer opens in Excel, with everythinhg in
> the correct columns. Following are the first two rows of the file saved that
> way. There are no quotes.
> Name,Stock,Bid $,Offer $,Last $,Change,%,Open $,High $,Low $,Volume
> M-W Fund - M-W Fund,ABS,7.660,7.450,7.560,0.000,0.00,0.000,0.000,0.000,0
>
> At this point I'm suspecting the download site, but then....
>
> 4. Perhaps I shouldn't mention this in this particular site, but I then
> repeated 2. above using the Netscape browser. I downloaded using the "Open in
> New Window" option, and used "Save As". It saved as a csv file (never
> mentioned anything else), and when displayed using Notepad, there were no
> quotes.
>
> This implies to me that the quotes aren't there when downloaded. but are
> being added as part of the IE6/Excel process, but not as part of the
> IE6/Netyscape process.
>
> Does any of this make any sense?
>
> "Dave Peterson" wrote:
>
> > Just some comments/questions...
> >
> > First, I'd double check the regional settings list separator character--no extra
> > trailing space characters/junk(?).
> >
> > Second, if you save the file to a .csv file on your harddrive and then
> > doubleclick on that filename in windows explorer, does excel open it correctly
> > (separate columns for each field)?
> >
> > Third (just a guess), I think I'd try to reregister excel so that it knows that
> > it owns the .csv extension.
> >
> > Close Excel and
> > Windows Start Button|Run
> > excel /unregserver
> > then
> > Windows Start Button|Run
> > excel /regserver
> >
> > The /unregserver & /regserver stuff resets the windows registry to excel's
> > factory defaults.
> >
> > Then test it out.
> >
> > Good luck,
> >
> >
> > Les M wrote:
> > >
> > > I have recently upgraded to Windows XP Home edition and have reinstalled all
> > > my applications. I regularly download csv files from one particular site and
> > > use them in Excel, but now they always download with all data in column A (in
> > > the past they downloaded correctly as csv files, using separate columns).
> > >
> > > In Control panel/Regional settings, comma is specified as the list
> > > separator, but this is not having the required effect.
> > >
> > > I can go through the "Data/Text to Columns/Delimited/comma" process each
> > > time I download (and that process works), but I would like the default to
> > > operate as it should.
> > >
> > > Any helpful advice would be appreciated.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson