|
List Separator
I have the following problem:
London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran |
List Separator
Kieran,
I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "Kieran H" wrote in message oups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran |
List Separator
On Mar 8, 3:36 am, "NickHK" wrote:
Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message oups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran |
List Separator
Kieran,
Under ToolsOptionsInternational, there is a "Use system separators" settings. On my XL XP, this only indicates the Decimal and Thousands separators and that setting has no effect on Excel's handling of .csv files. Maybe their version of Excel is different ? I assume your "definately" does mean "definitely" ? Just check our Chinese XP/Chinese Excel XP and all work as expected. My suspicion would still on the format of the semi-colon text file. Don't suppose it has some extra non printing characters in it? Unicode ? Exactly what do you mean by "Yet it still fails to open correctly" ? NickHK "Kieran H" wrote in message oups.com... On Mar 8, 3:36 am, "NickHK" wrote: Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message oups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran |
List Separator
On Mar 8, 9:46 am, "NickHK" wrote:
Kieran, Under ToolsOptionsInternational, there is a "Use system separators" settings. On my XL XP, this only indicates the Decimal and Thousands separators and that setting has no effect on Excel's handling of .csv files. Maybe their version of Excel is different ? I assume your "definately" does mean "definitely" ? Just check our Chinese XP/Chinese Excel XP and all work as expected. My suspicion would still on the format of the semi-colon text file. Don't suppose it has some extra non printing characters in it? Unicode ? Exactly what do you mean by "Yet it still fails to open correctly" ? NickHK "KieranH" wrote in message oups.com... On Mar 8, 3:36 am, "NickHK" wrote: Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message roups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran- Hide quoted text - - Show quoted text - Nick, I am using Excel 2000 and do not have an international tab under options. In response to your point re non printable characters, I created a very short csv file (ansi) in note pad. The semicolon delimited file when opened on a London based PC with the list separator also set to semicolon breaks the fields into the correct columns If I do the same on a Spanish pc the list separator is ignored and the data is dumped into the left most cell despite the fact that Application.International(xlListSeparator) reports a semicolon delimiter The Spanish pc has the same build but has a Spanish language pack loaded Regards Kieran |
List Separator
Kieran,
I'm out of suggestions then. Seems strange that the Spanish version should behave differently to all/most others though. NickHK "Kieran H" wrote in message oups.com... On Mar 8, 9:46 am, "NickHK" wrote: Kieran, Under ToolsOptionsInternational, there is a "Use system separators" settings. On my XL XP, this only indicates the Decimal and Thousands separators and that setting has no effect on Excel's handling of .csv files. Maybe their version of Excel is different ? I assume your "definately" does mean "definitely" ? Just check our Chinese XP/Chinese Excel XP and all work as expected. My suspicion would still on the format of the semi-colon text file. Don't suppose it has some extra non printing characters in it? Unicode ? Exactly what do you mean by "Yet it still fails to open correctly" ? NickHK "KieranH" wrote in message oups.com... On Mar 8, 3:36 am, "NickHK" wrote: Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message roups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran- Hide quoted text - - Show quoted text - Nick, I am using Excel 2000 and do not have an international tab under options. In response to your point re non printable characters, I created a very short csv file (ansi) in note pad. The semicolon delimited file when opened on a London based PC with the list separator also set to semicolon breaks the fields into the correct columns If I do the same on a Spanish pc the list separator is ignored and the data is dumped into the left most cell despite the fact that Application.International(xlListSeparator) reports a semicolon delimiter The Spanish pc has the same build but has a Spanish language pack loaded Regards Kieran |
List Separator
On Mar 8, 10:55 am, "NickHK" wrote:
Kieran, I'm out of suggestions then. Seems strange that the Spanish version should behave differently to all/most others though. NickHK "KieranH" wrote in message oups.com... On Mar 8, 9:46 am, "NickHK" wrote: Kieran, Under ToolsOptionsInternational, there is a "Use system separators" settings. On my XL XP, this only indicates the Decimal and Thousands separators and that setting has no effect on Excel's handling of .csv files. Maybe their version of Excel is different ? I assume your "definately" does mean "definitely" ? Just check our Chinese XP/Chinese Excel XP and all work as expected. My suspicion would still on the format of the semi-colon text file. Don't suppose it has some extra non printing characters in it? Unicode ? Exactly what do you mean by "Yet it still fails to open correctly" ? NickHK "KieranH" wrote in message roups.com... On Mar 8, 3:36 am, "NickHK" wrote: Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message roups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran- Hide quoted text - - Show quoted text - Nick, I am using Excel 2000 and do not have an international tab under options. In response to your point re non printable characters, I created a very short csv file (ansi) in note pad. The semicolon delimited file when opened on a London based PC with the list separator also set to semicolon breaks the fields into the correct columns If I do the same on a Spanish pc the list separator is ignored and the data is dumped into the left most cell despite the fact that Application.International(xlListSeparator) reports a semicolon delimiter The Spanish pc has the same build but has a Spanish language pack loaded Regards Kieran- Hide quoted text - - Show quoted text - Nick, So am I I have requested access to a Spanish pc - hopefully that will turn up something. I actually believe this problem would occur in any of our sites that uses the semicolon as a delimiter (Continental Europe) - but this has not been confirmed Many thanks for your thoughts Cheers Kieran |
List Separator
HI all,
I would strongly advise to import the data and NEVER allow XL to open directly any CSV file, becuase of the autoformat'General cell format that XL uses. Better, use ADO and a schema file. Regards JY "Kieran H" wrote in message ups.com... On Mar 8, 10:55 am, "NickHK" wrote: Kieran, I'm out of suggestions then. Seems strange that the Spanish version should behave differently to all/most others though. NickHK "KieranH" wrote in message oups.com... On Mar 8, 9:46 am, "NickHK" wrote: Kieran, Under ToolsOptionsInternational, there is a "Use system separators" settings. On my XL XP, this only indicates the Decimal and Thousands separators and that setting has no effect on Excel's handling of .csv files. Maybe their version of Excel is different ? I assume your "definately" does mean "definitely" ? Just check our Chinese XP/Chinese Excel XP and all work as expected. My suspicion would still on the format of the semi-colon text file. Don't suppose it has some extra non printing characters in it? Unicode ? Exactly what do you mean by "Yet it still fails to open correctly" ? NickHK "KieranH" wrote in message roups.com... On Mar 8, 3:36 am, "NickHK" wrote: Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message roups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran- Hide quoted text - - Show quoted text - Nick, I am using Excel 2000 and do not have an international tab under options. In response to your point re non printable characters, I created a very short csv file (ansi) in note pad. The semicolon delimited file when opened on a London based PC with the list separator also set to semicolon breaks the fields into the correct columns If I do the same on a Spanish pc the list separator is ignored and the data is dumped into the left most cell despite the fact that Application.International(xlListSeparator) reports a semicolon delimiter The Spanish pc has the same build but has a Spanish language pack loaded Regards Kieran- Hide quoted text - - Show quoted text - Nick, So am I I have requested access to a Spanish pc - hopefully that will turn up something. I actually believe this problem would occur in any of our sites that uses the semicolon as a delimiter (Continental Europe) - but this has not been confirmed Many thanks for your thoughts Cheers Kieran |
List Separator
On Mar 8, 11:46 am, "Jean-Yves" wrote:
HI all, I would strongly advise to import the data and NEVER allow XL to open directly any CSV file, becuase of the autoformat'General cell format that XL uses. Better, use ADO and a schema file. Regards JY "KieranH" wrote in message ups.com... On Mar 8, 10:55 am, "NickHK" wrote: Kieran, I'm out of suggestions then. Seems strange that the Spanish version should behave differently to all/most others though. NickHK "KieranH" wrote in message groups.com... On Mar 8, 9:46 am, "NickHK" wrote: Kieran, Under ToolsOptionsInternational, there is a "Use system separators" settings. On my XL XP, this only indicates the Decimal and Thousands separators and that setting has no effect on Excel's handling of .csv files. Maybe their version of Excel is different ? I assume your "definately" does mean "definitely" ? Just check our Chinese XP/Chinese Excel XP and all work as expected. My suspicion would still on the format of the semi-colon text file. Don't suppose it has some extra non printing characters in it? Unicode ? Exactly what do you mean by "Yet it still fails to open correctly" ? NickHK "KieranH" wrote in message roups.com... On Mar 8, 3:36 am, "NickHK" wrote: Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message roups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran- Hide quoted text - - Show quoted text - Nick, I am using Excel 2000 and do not have an international tab under options. In response to your point re non printable characters, I created a very short csv file (ansi) in note pad. The semicolon delimited file when opened on a London based PC with the list separator also set to semicolon breaks the fields into the correct columns If I do the same on a Spanish pc the list separator is ignored and the data is dumped into the left most cell despite the fact that Application.International(xlListSeparator) reports a semicolon delimiter The Spanish pc has the same build but has a Spanish language pack loaded Regards Kieran- Hide quoted text - - Show quoted text - Nick, So am I I have requested access to a Spanish pc - hopefully that will turn up something. I actually believe this problem would occur in any of our sites that uses the semicolon as a delimiter (Continental Europe) - but this has not been confirmed Many thanks for your thoughts Cheers Kieran- Hide quoted text - - Show quoted text - Valid point - The csv files are created by a third party reporting app and the issue kicked off when users refused to use the import text wizard. I have already created a general import routine but the users are keen to allow Excel to directly open the csv Regards Kieran |
List Separator
I have change the file association for CSV files to Notepad.
That way it still opens correctly. if the extebnsion would be text, what would the user do ? The only way left is to proccess all .csv and save as XL, or to create an XL viewer with the required macros. Regards JY "Kieran H" wrote in message oups.com... On Mar 8, 11:46 am, "Jean-Yves" wrote: HI all, I would strongly advise to import the data and NEVER allow XL to open directly any CSV file, becuase of the autoformat'General cell format that XL uses. Better, use ADO and a schema file. Regards JY "KieranH" wrote in message ups.com... On Mar 8, 10:55 am, "NickHK" wrote: Kieran, I'm out of suggestions then. Seems strange that the Spanish version should behave differently to all/most others though. NickHK "KieranH" wrote in message groups.com... On Mar 8, 9:46 am, "NickHK" wrote: Kieran, Under ToolsOptionsInternational, there is a "Use system separators" settings. On my XL XP, this only indicates the Decimal and Thousands separators and that setting has no effect on Excel's handling of .csv files. Maybe their version of Excel is different ? I assume your "definately" does mean "definitely" ? Just check our Chinese XP/Chinese Excel XP and all work as expected. My suspicion would still on the format of the semi-colon text file. Don't suppose it has some extra non printing characters in it? Unicode ? Exactly what do you mean by "Yet it still fails to open correctly" ? NickHK "KieranH" wrote in message roups.com... On Mar 8, 3:36 am, "NickHK" wrote: Kieran, I can't reproduce this with my English W2K/Office XP. Excel follow the delimiter specified as the List Separator in the Regional Options for opening .csv files. NickHK "KieranH" wrote in message roups.com... I have the following problem: London based PC A comma delimited file will open correctly if opened with "Open With Microsoft Excel for Windows" (csv extension) Note the list separator within regional settings for UK specifies a comma Madrid PC On a Spanish PC the same file with a semicolon delimiter fails to open correctly even though the regional setting list separator specifies a semicolon. No code is running that might re-set the delimiter Not loading correctly means the delimiter is being ignored. I know that users could solve this by using the import text wizard but I would really like to understand why the list separator in regional settings is being ignored Many Thanks Kieran- Hide quoted text - - Show quoted text - Nick, I can't even replicate this problem on a London based pc changed to reflect a Spanish locale! Nevertheless on a PC located in Spain the delimiter is being ignored on "Open With" A debug.print on the Application.International(xlListSeparator) confirms that Excel believes the delimiter to be a semicolon! The file delimiter is definately a semicolon Yet it still fails to open correctly Maybe the installation of Excel is different in some way? Very frustrating!! Thanks for responding Cheers Kieran- Hide quoted text - - Show quoted text - Nick, I am using Excel 2000 and do not have an international tab under options. In response to your point re non printable characters, I created a very short csv file (ansi) in note pad. The semicolon delimited file when opened on a London based PC with the list separator also set to semicolon breaks the fields into the correct columns If I do the same on a Spanish pc the list separator is ignored and the data is dumped into the left most cell despite the fact that Application.International(xlListSeparator) reports a semicolon delimiter The Spanish pc has the same build but has a Spanish language pack loaded Regards Kieran- Hide quoted text - - Show quoted text - Nick, So am I I have requested access to a Spanish pc - hopefully that will turn up something. I actually believe this problem would occur in any of our sites that uses the semicolon as a delimiter (Continental Europe) - but this has not been confirmed Many thanks for your thoughts Cheers Kieran- Hide quoted text - - Show quoted text - Valid point - The csv files are created by a third party reporting app and the issue kicked off when users refused to use the import text wizard. I have already created a general import routine but the users are keen to allow Excel to directly open the csv Regards Kieran |
| All times are GMT +1. The time now is 01:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com