DonkeyKong
Registered User.
- Local time
- Today, 09:23
- Joined
- Jan 24, 2013
- Messages
- 61
Alright so I'm at the next stage in my noob database/thing. I want to import a text file and I came across this after a couple searches:
Set cncurrent = CurrentProject.Connection
Set rsDiag = New ADODB.Recordset
' Open the text file
Open "F:\Documents and Settings\DFinnie\Desktop\v21icd9_diag.txt" For Input As #1
' Open the table to insert the text file into
strsql = "Select * from tblICD9"
rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData
ICDraw = Trim(Left(LineData, 6))
ICDDesc = Trim(Mid(LineData, 7))
rsDiag.AddNew
rsDiag!ICD9raw = ICDraw
rsDiag!Description = ICDDesc
rsDiag.Update
Now, I can't tell what's a variable and what's an sql statement of some kind. Seeing as I don't know sql this is made difficult. So I bolded some of the stuff that I need help defining.
Any help is greatly appreciated.
Here is what I've got so far that I need to adapt this to. The file is in fixed format.
Sub ImportTextFile()
Dim LineData As String
Dim CUSIP As String ' Holder for Account Number in text file
Dim Account As String ' Holder for the Cusip Number
Dim Reg As Integer ' Registration Code
Dim Payable As Date ' PayDate
Dim Balance As Double ' Holder for value of security
' Open the text file
Open "D:\Documents and Settings\............txt" For Input As #1
' Open the table to insert the text file into
DoCmd.OpenTable "tblTextFile", acNormal, acEdit
Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData
'timpAN = Left(LineData, 8)
'tblTextFile.AcctNumber = timpAN
CUSIP = Mid(LineData, 6, 9)
Account = Mid(LineData, 20, 5)
Reg = Mid(LineData, 33, 4)
Payable = Mid(LineData, 58, 8)
Balance = Mid(LineData, 66, 18)
Loop
End Sub
If you see something that's wrong in that code as well I would appreciate the feedback. Thanks.
Set cncurrent = CurrentProject.Connection
Set rsDiag = New ADODB.Recordset
' Open the text file
Open "F:\Documents and Settings\DFinnie\Desktop\v21icd9_diag.txt" For Input As #1
' Open the table to insert the text file into
strsql = "Select * from tblICD9"
rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData
ICDraw = Trim(Left(LineData, 6))
ICDDesc = Trim(Mid(LineData, 7))
rsDiag.AddNew
rsDiag!ICD9raw = ICDraw
rsDiag!Description = ICDDesc
rsDiag.Update
Now, I can't tell what's a variable and what's an sql statement of some kind. Seeing as I don't know sql this is made difficult. So I bolded some of the stuff that I need help defining.
Any help is greatly appreciated.
Here is what I've got so far that I need to adapt this to. The file is in fixed format.
Sub ImportTextFile()
Dim LineData As String
Dim CUSIP As String ' Holder for Account Number in text file
Dim Account As String ' Holder for the Cusip Number
Dim Reg As Integer ' Registration Code
Dim Payable As Date ' PayDate
Dim Balance As Double ' Holder for value of security
' Open the text file
Open "D:\Documents and Settings\............txt" For Input As #1
' Open the table to insert the text file into
DoCmd.OpenTable "tblTextFile", acNormal, acEdit
Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData
'timpAN = Left(LineData, 8)
'tblTextFile.AcctNumber = timpAN
CUSIP = Mid(LineData, 6, 9)
Account = Mid(LineData, 20, 5)
Reg = Mid(LineData, 33, 4)
Payable = Mid(LineData, 58, 8)
Balance = Mid(LineData, 66, 18)
Loop
End Sub
If you see something that's wrong in that code as well I would appreciate the feedback. Thanks.