VBA Text import advice

[hgus393]

Hi all,
I have serveral files that are stored daily in a directory with a filename that is always constant and a variable suffix that is the date. Well I am trying to import the file into a table in Access but I have encountered some difficulties, before that here is the code:

Sub GetRate()
Dim DateNr As Date
DoCmd.Hourglass True
    Dim path As String
    Dim s As String
    Dim MyFile As String
    Dim rs As Recordset
    Dim sql As String
DateNr = InputBox("Date", "Date")
    path = "C:\temp\Bob" & Format(CStr(DateNr), "YYYY-MM-DD") & ".txt"
       MyFile = Dir(path)
    If MyFile = "" Then 
        MsgBox path & " Missing!", , "File is missing"
        DoCmd.Hourglass False
        Exit Sub
    End If
 
    CurrentDb.Execute "DELETE * FROM Rates", dbFailOnError
 
    Set rs = CurrentDb.OpenRecordset("Rates") 'Output-table    
    Close #1 
    Open path For Input Lock Read As #1
    Do While Not EOF(1)
        Line Input #1, s 
        rs.AddNew 
        rs!Currency= Mid(s, 1, 3)
        rs!Value = Mid(s, 4, 9)
        rs!Covar = Mid(s, 13, 5)
        rs.Update 
    Loop
 
    Close #1 
DoCmd.Hourglass False
End Sub

My trouble here is that I don't know the actual length of the value rs.value it can be below zero, above zero it can be 1 or it can be 50 billion. My question is is there is a way I can trap this?
Bob

 

[DCrake]

Can you provide a few sample lines of input. This will help check for consitancies. Do all values have a decimal point? do they all have a currency prefix? Until seen hard to supply answer

David

 

[hgus393]

Can you provide a few sample lines of input. This will help check for consitancies. Do all values have a decimal point? do they all have a currency prefix? Until seen hard to supply answer

David

Sorry Mate,
Here's some examples all the values have points not decimals:
Currency Value Covar
AUD -1111.354 0.00961
GBP 100000000000.13 0.03165
EUR -0.001165 0.57686
USD 1.166577 -0.11355

Cheers
Bob

 

[DCrake]

It looks like the only thing that you could delimiter on is the space between them as it appears that it is not fixed width. You would need a simple function to extract the monetary value. Is the last variable (Covar) always the last 5 characters?

David

 

[hgus393]

It looks like the only thing that you could delimiter on is the space between them as it appears that it is not fixed width. You would need a simple function to extract the monetary value. Is the last variable (Covar) always the last 5 characters?

David

Ok, any clue for doing that? Covar is always 5 characters!
Cheers
Bob

 

[DCrake]

Ok, lets take the longest line in you code

GBP 100000000000.13 0.03165

What do we know?
We know that the first 3 characters are the currency prefix
We know that the last 5 are the covar
We also know that the first placeholder where the maony begins is the 5th character (This may even be a minus symbol if the value is a negative.

If we first look for the last space in the string. To do this we use InStrRev()

LastSpace = InStrRev(AnyString," ")

So the value is between the 5th placeholder and the lastSpace less 1

Money = Mid(AnyString,5,(LastSpace - 1))


To put this into a function the resulting function would appear as follows

Public Function ExtractMoney(AnyString As String) As Double

Dim LS As Integer
LS = InStrRev(AnyString," ")

ExtractMoney = Mid(AnyString,5,(LS-1))

End Function

Then in your code you would use it as follows

rs!Value = ExtractMoney(s)

Words of warning: Do not use field names such as Value as this is an Access reserved word. Also the above function has not been coded to trap any errors in the string such as missing elements.

David