0

My application generates CSV file from some objects. These CSV files are used in many countries so I must use correct separator.

To seperate values (cells) in a file I use separator:

Dim separator As String = My.Computer.Info.InstalledUICulture.TextInfo.ListSeparator

This should be OK. But one column contains decimal numbers so I want to be sure that I use correct decimal separator (must be different than list separator).

I am converting decimal value to a string like this:

Dim intValue as Integer = 123456
Dim strValue as String = (intValue / 100).ToString()

In my country a list separator is a semicolon and decimal separator is a comma. In this combination it is OK. But I found out that in some country where the list separator is a comma, decimal separator is comma as well. And this is a problem. How I have to convert a decimal number to string if I want to use correct local decimal separator? Thanks!

DanielH
  • 953
  • 10
  • 30
  • The list-separator isn't a country-specific preference: if a semi-colon works for all possible data, I suggest you use it. – Andrew Morton Jun 11 '14 at 08:10
  • In my country semi-colon is OK. In some other countries a semi-colon is not separator (but comma). – DanielH Jun 11 '14 at 08:27

1 Answers1

1

How I have to convert a decimal number to string if I want to use correct local decimal separator?

By default the current culture's separator is used anyway. But you can use the overload of Decimal.ToString that takes a CultureInfo:

Dim localizedNumber = (intValue / 100).ToString( CultureInfo.CurrentCulture )

If you want to use a different culture:

Dim usCulture = new System.Globalization.CultureInfo("en-US")
localizedNumber = (intValue / 100).ToString( usCulture )

If you want to know the decimal separator of a given culture:

Dim separator As String = usCulture.NumberFormat.NumberDecimalSeparator

Edit So your customers don't want to use tab as separator since they have to specify the delimiter manually. You could either generate a real excel-file, for example by using EPPlus which doesn't even need an excel-license or you need to provide another solution.

I have checked it, there are 13 cultures where the decimal-separator is the same as the list-separator (used by excel):

Dim conflictCultures = CultureInfo.GetCultures(CultureTypes.SpecificCultures).
   Where(Function(c) c.NumberFormat.NumberDecimalSeparator = c.TextInfo.ListSeparator)

So you have to check it manually and then provide a different list-separator. If decimal/list-separator is comma you can use semicolon as list-separator and vice-versa:

Dim argentinaCulture = New CultureInfo("es-AR") ' uses same separator for both
Dim decimalSeparator = argentinaCulture.NumberFormat.NumberDecimalSeparator ' comma
Dim listSeparator = argentinaCulture.TextInfo.ListSeparator 'comma
If decimalSeparator = listSeparator Then
    listSeparator = If(listSeparator = ",", ";", ",")
End If
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • OK so if the current culture's separator is used while converting to string, the prolem must be somewhere else. Am I using this code correctly: My.Computer.Info.InstalledUICulture.TextInfo.ListSeparator ? Because in some country both separators are the same (comma). – DanielH Jun 11 '14 at 08:23
  • @DanielH: why do you assume that both are different at all? You either have to choose a different decimal separator (f.e. force `.` via `CultureInfo.InvariantCulture`) or a list-separator that is less problematic, for example the tab-character `vbTab`. – Tim Schmelter Jun 11 '14 at 08:28
  • My application doesn't open CSV files. Users open it with the Excel. Excel knows local countryInfo on the local PC and uses separators according to it. So I can't choose or force separators. I need to get them from a local system. If someone in China generates a CSV file, anybody in China must be able to open the file with Excel. – DanielH Jun 11 '14 at 08:38
  • @DanielH: so why don't you generate the csv file with tab-characters as column separator and the correct cultureinfo for your customer's country to get the appropriate decimal separator? – Tim Schmelter Jun 11 '14 at 08:40
  • If I use a tab-character as a column separator, the Excel will not create columns - he is expecting as a column separator comma or semi-colon. – DanielH Jun 11 '14 at 08:48
  • Thats not true. You can specify the separator in excel. – Tim Schmelter Jun 11 '14 at 08:54
  • Yes but this is not comportable for users. I think that this should be done automatically by application instead of forcing users to do some changes... – DanielH Jun 11 '14 at 08:56
  • Is it really uncomfortable? Maybe you should create a real excel file which is easy even without an excel license for your server, you can for example use `EPPLus`. But give me a minute, i'll edit my answer to give you another hint. – Tim Schmelter Jun 11 '14 at 09:07
  • Thank you for your comment. I naively assumed that column and decimal separators are different everytime. So in this case I have to set separator by myself and user have to change it in the Excel – DanielH Jun 11 '14 at 09:36
  • I've edited my answer. Btw, here is the step-by-step instruction for your customers: http://superuser.com/questions/291445/how-do-you-change-default-delimiter-in-the-text-import-in-excel – Tim Schmelter Jun 11 '14 at 09:36