Losing decimal point when importing from text file

P

Pol2893

New member
Local time
Today, 16:42
Joined
Aug 31, 2015
Messages
2
Hi, I'm new here so I apologise if I've posted this in the wrong section.. here goes.

I'm importing from a text file to a table. The import seems to be fine except I lose my decimal point in all the figures I import. I don't mean that the figures are being rounded up, the decimal point just disappears. For instance 0.1 will change to 1, 1.5 will be 15, 12.4654 will be 124654 etc.

I'm importing from a text file that has a few lines of info at the start (that is ignored) and 2 or 3 columns of figures which are deliminated by a several spaces. I'm also importing from a text file (this is the OARs part in code) where several different sets of data are in one text file but the same problem happens with those imports too.

The code for the button I'm using is as follows if anyone is interested. Help would really be appreciated because I can't figure this out at all.



'****************************************************************
Private Sub Command0_Click()
'get the file name
Debug.Print "Getting File Name"
'Declare a variable as a FileDialog object.
Dim fd As FileDialog
Set dbs = CurrentDb

'Set the starting look location
Dim strComPath As String
strComPath = "C:Path.txt"

Dim strFilePath As String
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
.InitialFileName = strComPath
.AllowMultiSelect = True
.Filters.Clear
'Add filter to only show excel files.
.Filters.Add "Files", "*.txt", 1
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
strFilePath = .SelectedItems(1)
'Step through each string in the FileDialogSelectedItems collection.
'For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
' strFilePath: " & vrtSelectedItem

'Next vrtSelectedItem

Else
'The user pressed Cancel.
DoCmd.Hourglass (False)
MsgBox "No file(s) selected", vbOKOnly + vbExclamation, "No file Selected, exiting"
Set fd = Nothing
Exit Sub
End If
End With

Dim counter As Integer
Dim count As Integer
Dim Id As String
Dim split As Integer
Dim split1 As Integer
Dim tbl As String
Dim curLine As String
Dim trueID As String

Dim Dose As String
Dim Rel_Dose As String
Dim Ratio As String
Dim DDose As Double
Dim DRel_Dose As Double
Dim DRatio As Double

Dim col2pos As String
Dim col3pos As String

Dim struct As String
Dim code As String

For Each varFile In fd.SelectedItems
FileName = Mid(varFile, InStrRev(varFile, "\") + 1, InStrRev(varFile, ".") - InStrRev(varFile, "\") - 1)

split = InStr(FileName, "_")
Id = Left(FileName, split - 1)

tbl = Mid(FileName, split + 1, Len(FileName) - split)

curLine = ""
split1 = 0
Select Case tbl
Case "PTV1", "PTV2", "PTV3", "PTV4"
Open varFile For Input As #1

Do While Left(curLine, 2) <> "ID"
Line Input #1, curLine
Loop

split1 = InStr(curLine, ":")
trueID = Mid(curLine, split1 + 2)
If Id = trueID Then


Do While Left(Trim(curLine), 10) <> "Dose [cGy]"
Line Input #1, curLine
Loop

DoCmd.Hourglass True

Do Until EOF(1)
Line Input #1, curLine
If Len(Trim(curLine)) = 0 Then Exit Do
curLine = Trim(curLine)
col2pos = InStr(curLine, " ")
col3pos = InStr(col2pos + 1, Trim(Mid(curLine, col2pos)), " ")
Dose = Left(curLine, col2pos - 1)

curLine = Trim(Mid(curLine, col2pos))
Rel_Dose = Left(curLine, InStr(curLine, " ") - 1)
Ratio = Trim(Mid(curLine, InStr(curLine, " ")))

DDose = CDbl(Replace(Dose, ".", ","))
DRel_Dose = CDbl(Replace(Rel_Dose, ".", ","))
DRatio = CDbl(Replace(Ratio, ".", ","))


code = "INSERT INTO " & tbl & " (Dose,[Relative Dose],Volume) VALUES ('" & DDose & "', '" & DRel_Dose & "', '" & DRatio & "');"
dbs.Execute code, dbFailOnError

Loop

DoCmd.Hourglass False
Else
MsgBox ("IDs mismatch! Skiping file " & FileName)

End If

Close #1

Case "OARs"
DoCmd.Hourglass True
Open varFile For Input As #1

Do While Left(curLine, 2) <> "ID"
Line Input #1, curLine
Loop

split1 = InStr(curLine, ":")
trueID = Mid(curLine, split1 + 2)
If Id = trueID Then



Do While Left(Trim(curLine), 5) <> "% for"
Line Input #1, curLine
Loop
Line Input #1, curLine

For count = 1 To 4
Line Input #1, curLine
struct = Mid(curLine, InStr(curLine, " ") + 1)



Do While Left(Trim(curLine), 10) <> "Dose [cGy]"
Line Input #1, curLine
Loop

Do
Line Input #1, curLine
If Len(Trim(curLine)) = 0 Then Exit Do
curLine = Trim(curLine)
col2pos = InStr(curLine, " ")
Dose = Left(curLine, col2pos - 1)
Ratio = Trim(Mid(curLine, col2pos))

DDose = CDbl(Replace(Dose, ".", ","))
DRatio = CDbl(Replace(Ratio, ".", ","))

code = "INSERT INTO [" & struct & "] (Dose,Volume) VALUES ('" & DDose & "', '" & DRatio & "');"
dbs.Execute code, dbFailOnError


Loop Until EOF(1)

Next count
DoCmd.Hourglass False
Else
MsgBox ("IDs mismatch! Skiping file " & FileName)

End If
Close #1
End Select
Next
MsgBox ("File(s) imported successfully.")
Set fd = Nothing

End Sub
*********************************************
 
Can you provide your code in readable form with indentations - go to the advanced tab, paste your code and then surround with the code tags (use the # button).

It may be this bit of code is causing the problem

DDose = CDbl(Replace(Dose, ".", ","))
DRatio = CDbl(Replace(Ratio, ".", ","))
 
Code: 
Private Sub Command0_Click()
'************************************************************************
'Lets get the file name
    Debug.Print "Getting File Name"
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Set dbs = CurrentDb
 
    'Set the starting look location
    Dim strComPath As String
    strComPath = "C:\Users\SVYRYK\Documents\111163\New folder (2)\1.txt"
 
    Dim strFilePath As String
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
 
    'Use a With...End With block to reference the FileDialog object.
    With fd
        .InitialFileName = strComPath
        .AllowMultiSelect = True
        .Filters.Clear
        'Add filter to only show excel files.
        .Filters.Add "Files", "*.txt", 1
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then
                strFilePath = .SelectedItems(1)
            'Step through each string in the FileDialogSelectedItems collection.
            'For Each vrtSelectedItem In .SelectedItems
 
                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.
             '   strFilePath: " & vrtSelectedItem
 
            'Next vrtSelectedItem
 
        Else
            'The user pressed Cancel.
            DoCmd.Hourglass (False)
            MsgBox "No file(s) selected", vbOKOnly + vbExclamation, "No file Selected, exiting"
            Set fd = Nothing
            Exit Sub
        End If
    End With
 
 Dim counter As Integer
 Dim count As Integer
 Dim Id As String
 Dim split As Integer
 Dim split1 As Integer
 Dim tbl As String
 Dim curLine As String
 Dim trueID As String
 
 Dim Dose As String
 Dim Rel_Dose As String
 Dim Ratio As String
 Dim DDose As Double
 Dim DRel_Dose As Double
 Dim DRatio As Double
 
 Dim col2pos As String
 Dim col3pos As String
 
 Dim struct As String
 Dim code As String
 
  For Each varFile In fd.SelectedItems
    FileName = Mid(varFile, InStrRev(varFile, "\") + 1, InStrRev(varFile, ".") - InStrRev(varFile, "\") - 1)
    
    split = InStr(FileName, "_")
    Id = Left(FileName, split - 1)
    
    tbl = Mid(FileName, split + 1, Len(FileName) - split)
    
 curLine = ""
 split1 = 0
    Select Case tbl
        Case "PTV1", "PTV2", "PTV3", "PTV4"
            Open varFile For Input As #1
            
            Do While Left(curLine, 2) <> "ID"
             Line Input #1, curLine
            Loop
            
            split1 = InStr(curLine, ":")
            trueID = Mid(curLine, split1 + 2)
            If Id = trueID Then
             
            
            Do While Left(Trim(curLine), 10) <> "Dose [cGy]"
            Line Input #1, curLine
            Loop
            
            DoCmd.Hourglass True
            
            Do Until EOF(1)
            Line Input #1, curLine
            If Len(Trim(curLine)) = 0 Then Exit Do
            curLine = Trim(curLine)
            col2pos = InStr(curLine, " ")
            col3pos = InStr(col2pos + 1, Trim(Mid(curLine, col2pos)), " ")
            Dose = Left(curLine, col2pos - 1)
            
            curLine = Trim(Mid(curLine, col2pos))
            Rel_Dose = Left(curLine, InStr(curLine, " ") - 1)
            Ratio = Trim(Mid(curLine, InStr(curLine, " ")))
            
            DDose = CDbl(Replace(Dose, ".", ","))
            DRel_Dose = CDbl(Replace(Rel_Dose, ".", ","))
            DRatio = CDbl(Replace(Ratio, ".", ","))
            
            
             code = "INSERT INTO " & tbl & " (Dose,[Relative Dose],Volume) VALUES ('" & DDose & "', '" & DRel_Dose & "', '" & DRatio & "');"
             dbs.Execute code, dbFailOnError
            
            Loop
            
            DoCmd.Hourglass False
            Else
            MsgBox ("IDs mismatch! Skiping file " & FileName)
             
            End If
            
            Close #1
           
           Case "OARs"
           DoCmd.Hourglass True
             Open varFile For Input As #1
            
            Do While Left(curLine, 2) <> "ID"
             Line Input #1, curLine
            Loop
            
            split1 = InStr(curLine, ":")
            trueID = Mid(curLine, split1 + 2)
            If Id = trueID Then
             
            
            
            Do While Left(Trim(curLine), 5) <> "% for"
            Line Input #1, curLine
            Loop
            Line Input #1, curLine
            
            For count = 1 To 4
                Line Input #1, curLine
                struct = Mid(curLine, InStr(curLine, " ") + 1)
                
                
                
                Do While Left(Trim(curLine), 10) <> "Dose [cGy]"
                Line Input #1, curLine
                Loop
                
                Do
                    Line Input #1, curLine
                    If Len(Trim(curLine)) = 0 Then Exit Do
                    curLine = Trim(curLine)
                    col2pos = InStr(curLine, " ")
                    Dose = Left(curLine, col2pos - 1)
                    Ratio = Trim(Mid(curLine, col2pos))
                    
                     DDose = CDbl(Replace(Dose, ".", ","))
                     DRatio = CDbl(Replace(Ratio, ".", ","))
                     
                     code = "INSERT INTO [" & struct & "] (Dose,Volume) VALUES ('" & DDose & "', '" & DRatio & "');"
                     dbs.Execute code, dbFailOnError
                    
                    
                Loop Until EOF(1)
                
            Next count
            DoCmd.Hourglass False
            Else
            MsgBox ("IDs mismatch! Skiping file " & FileName)
             
            End If
            Close #1
        End Select
Next
    MsgBox ("File(s) imported successfully.")
    Set fd = Nothing

End Sub

I've changing an removing that part before but then I couldn't get anything to import.

So I feel rather foolish now. I've been trying to fix this for a while and I've just found a solution. I have to change my settings to use a comma as a decimal separator.

I followed the instructions at this address

blogmines.com/blog/how-to-change-the-decimal-separator-in-excel-2010/

I'm sorry I can't post it as a link as I don't have enough posts to allow it. I changed settings it both Excel and the Control Panel.
 
You seem to want to store all the decimal values as strings. That is unusual.

If your decimal separator is "," then wrap all decimal numbers in the function Str. It generates a decimal number with "." as separator(irrespective of locale settings), as required in the SQL.

Besides, since you already have the string representation with "." as separator directly from the file, then insert that into the SQL instead of the superfluos back-and-forth conversions.
 
Last edited:
You must log in or register to reply here.

Similar threads

M
DoCmd.TransferSpreadsheet no longer trims trailing spaces from field names after conversion to Office 365
Replies
12
Views
880
Gasman
Gasman
P
Create seperate PDF Files from a report Base on a query with more than 300 records
Replies
5
Views
944
Uncle Gizmo
Uncle Gizmo
H
Solved Import flower show class numbers into Entry table imultivalue field
Replies
17
Views
914
hedwig
H
Y
Solved How to copy a file when closing a File Dialog?
Replies
5
Views
1,455
YuvalH
Y
Cotswold
Solved Read Text File Into Table Issue
2
Replies
38
Views
5,325
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom