Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CSV Regional Settings (Excel 2003 +)

    Hi All,

    I now have global users of an application who have their Windows Regional Settings set to something other than English/United States (e.g. Finnish, etc) ... this sets the Decimal Symbol as a comma and the List separator as a semicolon. The application we use downloads info to a Excel (via CSV) but comes out all in one column. (I know that the user could simply select the column and perform a Data > Text to Column using a comma but would rather change more permanently).

    If I change just the List separator to a comma -- it still doesn't work because the Decimal Symbol is also a comma. If I change the Decimal Symbol to something else it does -- but, of course, my Finnish users wish to continue using the comma as a Decimal Symbol.

    So, is there a more permanent way for Excel to change the List separator from a semi colon to a comma.

    As always, this forum's help is deeply appreciated!!!
    --cat

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    This is a PITA. One option is to provide VBA code to open the .csv file: VBA always uses US settings, regardless of the user's regional settings. Another is to change the extension to .txt. This will make Excel start the Import Text Wizard when the file is opened, so that the user can specify the delimiter.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    You could use some VBA code like this (not complete) to create the CSV file where rng is the range of cells to be written out:

    strDelimiter = ","

    'Create a file system object and use it to create a new
    'text file.
    Set fso = New FileSystemObject
    Set txs = fso.CreateTextFile(strFileName, True)

    'This loop is for looping from the top of the sheet to the bottom
    For lngRow = 1 To rng.Rows.Count
    For lngCol = 1 To rng.Columns.Count
    txs.Write Chr(34) & rng.Cells(lngRow, lngCol) & Chr(34) & strDelimiter
    Next lngCol
    txs.Write vbCr
    Next lngRow

    txs.Close

    Depending on the location you could have some code to change strDelimiter accordingly.

    Nick

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    Hi Hans ... sorry about the delay, I've been traveling and unable to connect. What is PITA?
    --cat

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    It's the abbreviation of (and euphemism for) "pain in the a**" <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    Or "pain in the <code>a***</code>", depending on your regional settings... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    <blushing now> I should have guessed!!!
    Thanks, guys
    --cat

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    Pain In The Antianterior region of the body! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    It's me again ... I really have to think of a work-around for this issue ...

    Givens (things I can't change but will be changed in the future by this company's designers/developers):
    --A CSV file is created as a pop-up that the user can either OPEN or Save ... usually a user wishes to simply open the file
    --currently CSV file type is opened with Excel (maybe I can do something here with switches???? )
    --If the user has any other setting other then English(United States) as their Regional Setting ...
    the data is placed all in one column ... with the important exception of the ALT+ENTER symbol (multiple lines in one cell)
    this comes over as a new row incorrectly. Actually separating the quotes that denote the text field. So, even applying the Data | Text to Columns option in Excel doesn't work correctly (due to ALT+Enter characters that are missing/forced into new row)

    So, I'm thinking that maybe there's a way to have CSV open with Excel (like it currently does),
    --but have Excel <somehow> automatically open a file that has a macro that does the work for the user after they click a keyboard combination that runs a macro that does:
    (1) looks for that place that the special character should be ... maybe by trying to count the quote symbols in a cell ... if there are not two than do not break the row, and then
    (2) do the text to columns using a comma.

    But, I want the end user to be able to open the CSV file and simply click, say, CTRL+SHIFT+P, to run the macro, to produce the CSV file that his US counterparts get.

    AS always, I really, really do appreciate all the help this forum gives me. I also feel a little guilty cause I know this can be done, and that I could probably learn how and do it, and I do usually do that approach ... but, I know that by simply asking this group ... I can save a lot of time ... I don't mean to be "using" you guys, but you really do help me a lot!!!

    Thanks,
    cat

    I've uploaded an excel file (I couldn't upload the csv files) ... one tab represents what the Finnish user sees (but of course, it is a CSV file not a XLS file) with comments in C3 and J2, and an English tab with comments in J2 cell.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    I don't understand the part "A CSV file is created as a pop-up that the user can either OPEN or Save". What does that mean?

    If the .csv file is actually created as a file on disk, the following small macro can be used to open it correctly:

    Sub ImportCSV()
    Dim fileToOpen As Variant
    fileToOpen = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
    If Not fileToOpen = False Then
    Workbooks.Open fileToOpen
    End If
    End Sub

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV Regional Settings (Excel 2003 +)

    I don't think it'll be easy to convert the file once it has been opened incorrectly within Excel, since it's virtually impossible to know whether a new row is actually intended as such or is the result of a line break in the original.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •