Hi,
I am trying to implement a system that will import data from a text file whose data is in a fixed width format i.e Field 1 is from 1-2, field 2 is from 13-20 etc
I have inserted message boxes to confirm that the code is reading the data but my problem lies in picking the fields and in the right length then saving it in the table named letters.
Will greatly appreciate your help.
See the code I have below and advice. Pls note I have commented some code as I was testing import of the 1st field
Public Function ImportData(FileName As String, ImportMonth As Date) As Integer
Dim LineOfData As String, OneChar As String, Counter As Integer
' Import a text file extract for a given month. The file should be in the current
' directory with the name YYYYMM.TXT where YYYY is the year and MM is the month.
On Error GoTo subError
LineOfData = vbNullString
Counter = 0
Open FileName For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
OneChar = Input(1, #1) ' Get one character.
If OneChar <> Chr(13) And OneChar <> Chr(10) Then
LineOfData = LineOfData & OneChar
End If
If OneChar = Chr(13) Then
If Counter > 0 Then
ParseLineOfData LineOfData, ImportMonth
End If
Counter = Counter + 1
LineOfData = vbNullString
End If
Loop
Close #1 ' Close file.
ImportData = Counter - 1
subExit:
Exit Function
subError:
ErrMsg Err.Description
Resume subExit
End Function
Public Function ParseLineOfData(LineOfData As String, ImportMonth As Date)
Dim strBrAcNumber As String, strShortName As String, strAmount As String
Dim strDateClass As String, strMarketSeg As String, strAccType As String
Dim strDOpened As String, strLastCR As String, strStreamCode As String
Dim tempStr As String, Separator As String, SQLStr As String
Dim pos As Integer
On Error GoTo subError
tempStr = LineOfData
' Separator = "\"
' 'Extract branch and account number
' pos = InStr(1, tempStr)
' MsgBox pos
' If pos > 0 Then
strBrAcNumber = Mid(tempStr, 1, 12)
' tempStr = Mid(tempStr, pos + 1)
' End If
MsgBox strBrAcNumber
' 'Extract Short Name
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strShortName = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract Amount
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strAmount = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract DateClass
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strDateClass = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract MarketSeg
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strMarketSeg = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract AccType
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strAccType = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract DOpened
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strDOpened = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract LastCR
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strLastCR = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract streamCode
' strStreamCode = tempStr
' tempStr = vbNullString
' ' Append data into the table MAIN for later etraction into Accounts and MothlyData tables.
SQLStr = "INSERT INTO Letters (Letter_Code) VALUES (" & _
strBrAcNumber & ")"
'
' DoCmd.SetWarnings True
DoCmd.RunSQL SQLStr
'
' DoCmd.SetWarnings True
subExit:
Exit Function
subError:
ErrMsg Err.Description
Resume subExit
End Function
I am trying to implement a system that will import data from a text file whose data is in a fixed width format i.e Field 1 is from 1-2, field 2 is from 13-20 etc
I have inserted message boxes to confirm that the code is reading the data but my problem lies in picking the fields and in the right length then saving it in the table named letters.
Will greatly appreciate your help.
See the code I have below and advice. Pls note I have commented some code as I was testing import of the 1st field
Public Function ImportData(FileName As String, ImportMonth As Date) As Integer
Dim LineOfData As String, OneChar As String, Counter As Integer
' Import a text file extract for a given month. The file should be in the current
' directory with the name YYYYMM.TXT where YYYY is the year and MM is the month.
On Error GoTo subError
LineOfData = vbNullString
Counter = 0
Open FileName For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
OneChar = Input(1, #1) ' Get one character.
If OneChar <> Chr(13) And OneChar <> Chr(10) Then
LineOfData = LineOfData & OneChar
End If
If OneChar = Chr(13) Then
If Counter > 0 Then
ParseLineOfData LineOfData, ImportMonth
End If
Counter = Counter + 1
LineOfData = vbNullString
End If
Loop
Close #1 ' Close file.
ImportData = Counter - 1
subExit:
Exit Function
subError:
ErrMsg Err.Description
Resume subExit
End Function
Public Function ParseLineOfData(LineOfData As String, ImportMonth As Date)
Dim strBrAcNumber As String, strShortName As String, strAmount As String
Dim strDateClass As String, strMarketSeg As String, strAccType As String
Dim strDOpened As String, strLastCR As String, strStreamCode As String
Dim tempStr As String, Separator As String, SQLStr As String
Dim pos As Integer
On Error GoTo subError
tempStr = LineOfData
' Separator = "\"
' 'Extract branch and account number
' pos = InStr(1, tempStr)
' MsgBox pos
' If pos > 0 Then
strBrAcNumber = Mid(tempStr, 1, 12)
' tempStr = Mid(tempStr, pos + 1)
' End If
MsgBox strBrAcNumber
' 'Extract Short Name
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strShortName = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract Amount
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strAmount = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract DateClass
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strDateClass = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract MarketSeg
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strMarketSeg = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract AccType
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strAccType = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract DOpened
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strDOpened = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract LastCR
' pos = InStr(1, tempStr, Separator)
' If pos > 0 Then
' strLastCR = Mid(tempStr, 1, pos - 1)
' tempStr = Mid(tempStr, pos + 1)
' End If
'
' 'Extract streamCode
' strStreamCode = tempStr
' tempStr = vbNullString
' ' Append data into the table MAIN for later etraction into Accounts and MothlyData tables.
SQLStr = "INSERT INTO Letters (Letter_Code) VALUES (" & _
strBrAcNumber & ")"
'
' DoCmd.SetWarnings True
DoCmd.RunSQL SQLStr
'
' DoCmd.SetWarnings True
subExit:
Exit Function
subError:
ErrMsg Err.Description
Resume subExit
End Function