none
Regional settings and decimal separator RRS feed

  • Question

  • I use Excel 2010 vba code to read .txt files and make some calculations afterwards.

    The problem I have faced is that once, I have changed regional settings from Danish to UK, numbers that are imported to Excel sheet became Integer... No separator at all...

    This is how line of numbers looks like in text file:

    2015-09-28 11:20:30;174,4165;8;17,94556;17,94556;61,86356;29,52779;4974;1240,5;

    The same number should appear on Excel sheet disregard the regional settings.

    Does anybody know, how can I fix that problem?

    Thank you!

    Monday, October 5, 2015 8:44 AM

All replies

  • Not sure how you are importing your text files but in systems where the decimal separator is a dot probably best to replace the commas with dots and then possibly the semicolon separators with commas (though you may be able to specify the semicolon separator when importing).

    If not sure of the system's decimal separator you can check with

    Application.International(xlDecimalSeparator)

    Monday, October 5, 2015 11:08 AM
    Moderator
  • There is my code for importing text files... However, I haven't developed it entirely by myself, therefore I am a bit lost...

    Maybe you can come you with some idea?

    Private Sub LoadData_Click()
    
        Dim StartDate As Date
        Dim EndDate As Date
        Dim FS As New FileSearch
        Dim R As Range
        Dim ThisFile As Variant
        Dim ThisDate As Date
        Dim Data As Variant
        Dim Count As Long
    
        'Clear previous data
        Call ClearSh
    
        StartDate = CDate(Me.StartDateBox.Value)
        EndDate = CDate(Me.EndDateBox.Value)
        If Fix(EndDate) = EndDate Then
            'No include all files for this day
        EndDate = EndDate + TimeSerial(23, 59, 59)
        End If
        If StartDate > EndDate Then
            MsgBox "Wrong date selection"
                Application.DisplayAlerts = False
                ActiveWorkbook.Save
                Application.Quit
        End If
        
        With FS
            'Same path as our file
            .LookIn = ThisWorkbook.Path
            .FileName = "*DataLog.txt"
            'Search all files sort by file name
            If .Execute(msoSortByFileName, msoSortOrderAscending) = 0 Then
                MsgBox "No date files found in " & .LookIn
                Exit Sub
            End If
        
        DoEvents
        
        For Each ThisFile In .FoundFiles
          'Get the date from the file name
          ThisDate = Filename2Date(ThisFile)
          'Between our dates?
          If (ThisDate >= StartDate) And (ThisDate <= EndDate) Then
            'Import at the end of the data
            Set R = Range("A" & Rows.Count).End(xlUp).Offset(0)
            Debug.Print ThisFile, R.Row
            Data = ReadCSV(ThisFile)
            R.Resize(UBound(Data) + 1, UBound(Data, 2) + 1).Value2 = Data
            Count = Count + 1
          End If
        Next
      End With
      
      Call ColumnNames
      Call DataFilter
    
      'Done
      Application.Cursor = xlDefault
      If Count = 0 Then
        MsgBox "No files match your dates"
      End If
    
    End Sub
    
    
    Private Function Filename2Date(ByVal FullName As String) As Date
      'Convert e.g "C:\20130601142648DataLog.txt" to the date "01.06.2013 14:26:48"
      Dim i As Long, j As Long
      i = InStrRev(FullName, "\")
      If i > 0 Then FullName = Mid(FullName, i + 1)
      FullName = JustNumbers(FullName)
      If Len(FullName) <> 14 Then Exit Function
      Filename2Date = _
        DateSerial(Mid(FullName, 1, 4), Mid(FullName, 5, 2), Mid(FullName, 7, 2)) + _
        TimeSerial(Mid(FullName, 9, 2), Mid(FullName, 11, 2), Mid(FullName, 13, 2))
    End Function
    
    
    Private Function JustNumbers(ByVal What As String) As String
      'Return only numbers from What
      Dim i As Long, j As Long, Digit As String
      For i = 1 To Len(What)
        Digit = Mid$(What, i, 1)
        If Digit Like "#" Then
          j = j + 1
          Mid$(What, j, 1) = Digit
        End If
      Next
      JustNumbers = Left$(What, j)
    End Function
    
    
    Private Function ReadCSV(ByVal FullName As String) As Variant
      'Read a CSV file into an array
      Const LDelim = vbCrLf 'Line delimiter
      Const FDelim = ";"    'Field delimiter
      Dim hFile As Integer
      Dim Buffer As String
      Dim Lines, Line, Data
      Dim i As Long, j As Long
      'Be sure the file exists
      If Dir(FullName) = "" Then Exit Function
      'Open and read all data
      hFile = FreeFile
      Open FullName For Binary Access Read As #hFile
      Buffer = Space(LOF(hFile))
      Get #hFile, , Buffer
      Close #hFile
      'Split into lines
      Lines = Split(Buffer, LDelim)
      'Split the first line and prepare the output
      'Note: I assume that all lines have the same number of fields
      Line = Split(Lines(0), FDelim)
      ReDim Data(0 To UBound(Lines), 0 To UBound(Line))
      For i = 0 To UBound(Lines)
        Line = Split(Lines(i), FDelim)
        For j = 0 To UBound(Line)
          'Parse the fields
          If IsNumeric(Line(j)) Then
            Data(i, j) = CDbl(Line(j))
          Else
            Data(i, j) = Line(j)
          End If
        Next
      Next
      ReadCSV = Data
    End Function

    Monday, October 5, 2015 11:52 AM
  • There's a lot going on there and difficult to work out what's going on without knowing several other things including a sample of the text file. But just a guess try

    In Function ReadCSV first change the variable declared as Line to say arrLine, because Line is a keyword, then add the new line as indicated to change any commas to a a dot

      ReDim Data(0 To UBound(Lines), 0 To UBound(arrLine))
      For i = 0 To UBound(Lines)
        arrLine = Split(Lines(i), FDelim)
        For j = 0 To UBound(arrLine)
          'Parse the fields
          arrLine(j) = Replace(arrLine(j), ",", ".") ' << new line
          If IsNumeric(arrLine(j)) Then
            Data(i, j) = CDbl(arrLine(j))
          Else
            Data(i, j) = arrLine(j)
          End If
        Next
      Next

    alternatively this might also be OK

      'Split into lines
      Lines = Split(Buffer, LDelim)  ' << new line
      Lines = Replace(Lines, ",", ".")

    Obviously you will only want to do that if the decimal separator is a dot in the user's system.

    How many other places have you posted your question?

    Monday, October 5, 2015 4:31 PM
    Moderator