Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comma delimiter for csv downloads?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currency, Percent and Comma Style buttons | Excel Worksheet Functions | |||
Counting number of items being added in a formula | Excel Discussion (Misc queries) | |||
Importing a part of a big comma delimited csv-file | Excel Worksheet Functions | |||
Comma is not visible in Excel | Excel Discussion (Misc queries) | |||
HELP - I need to change space delimited to comma? | Excel Discussion (Misc queries) |