List Separator

[Kieran H]

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

 

[NickHK]

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]

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














- 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

 

[NickHK]

Kieran,
Under Tools>Options>International, 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]

Kieran,
Under Tools>Options>International, 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













- 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

 

[NickHK]

Kieran,
I'm out of suggestions then. Seems strange that the Spanish version should
behave differently to all/most others though.

NickHK

 

[Kieran H]

Kieran,
I'm out of suggestions then. Seems strange that the Spanish version should
behave differently to all/most others though.

NickHK










- 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

 

[Jean-Yves]

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]

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








- 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

 

[Jean-Yves]

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