C
claryn via AccessMonster.com
Thanks in advance for any help.
Importing data from 1100 text files. each file has 5000 lines. each lines
is delimitted by pipes |
At this point I use split on the pipes to create an array, and then pull the
array into an existing table.
I have been successful in setting up the looping through the individual files,
and opening a file. Than I run into a problem. If I am stepping through the
code, the split works, and the places in the array assign to field values
into a table in access.
However, when I try to execute the code normally, I get the following.
Runtime error "9"
subscript out of range.
If I try to debug, I get the same thing into infiniti (or close to it, it
seems).
Sample line from txt file.
START SAMPLE:
263862|GA4054|lastname, mr. & Mrs.|1111111111|22222|3333|\\pcimg\smeadlnk\
Images\Vol_007\PP002M5P.tif|Purchased Documents (legal)|12/21/2000 11:40:20
AM|lastname, first & first|some company, inc.
END SAMPLE:
START CODE:
Private Sub Command1_Click()
Call ReadFiles
End Sub
Public Sub ReadFiles()
Dim strPath1 As String
Dim strPath2 As String
Dim strFile2 As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strFile As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("Tbldatfiles", dbOpenDynaset, dbAppendOnly)
strPath = "C:\Monitronics\*.dat"
strPath2 = "C:\Monitronics\"
strFile = Dir(strPath)
Debug.Print strFile
Do While strFile <> vbNullString
strFile2 = Left(strFile, (InStr(strFile, ".") - 1)) & ".txt"
Debug.Print strFile2
rst.AddNew
rst.Fields("datfiles") = strPath2 & strFile
rst.Fields("txtfiles") = strPath2 & strFile2
rst.Update
FileCopy strPath2 & strFile, strPath2 & strFile2
Call getrecords(strPath2 & strFile2)
strFile = Dir()
Loop
rst.Close
End Sub
Public Sub getrecords(sourcefile As String)
Dim iFile As Integer
Dim strLine As String
Dim strfld() As String
Dim db1 As DAO.Database
Dim rst3 As DAO.Recordset
Dim sd As String
Dim n1 As Integer
Dim n2 As Integer
Dim varRange As Variant
Dim myvalue
sd = "|"
Set db1 = CurrentDb()
Set rst3 = db1.OpenRecordset("Tbldatrecords", dbOpenDynaset)
iFile = FreeFile
Open sourcefile For Input As #iFile
Do While EOF(iFile) = False
Line Input #iFile, strLine
varRange = Split(strLine, sd, -1)
rst3.AddNew
'used for initial testing 'rst3.Fields("sourceline") = strLine
'used for initial testing 'rst3.Fields("datfilename") =
sourcefile
'used for testing 'myvalue = varRange(0)
'used for testing 'Debug.Print myvalue
rst3.Fields("ARcustomer") = varRange(0)
rst3.Fields("Accountsite") = varRange(1)
rst3.Fields("Subscribername") = varRange(2)
rst3.Fields("phone#") = varRange(3)
rst3.Fields("contract#") = varRange(4)
rst3.Fields("dealernumber") = varRange(5)
rst3.Fields("documenttitle") = Mid(varRange(6), (InStrRev
(varRange(6), "\") + 1))
rst3.Fields("documenttype") = varRange(7)
rst3.Fields("arcustomername") = varRange(9)
rst3.Fields("dealername") = varRange(10)
rst3.Fields("file") = varRange(6)
rst3.Fields("sourceline") = strLine
rst3.Fields("datfilename") = sourcefile
rst3.Update
Loop
Close iFile
End Sub
END CODE:
Thank you all for any correction or guidance. I think I have looked at this
so long I cannot see what I am doing wrong.
N8
Importing data from 1100 text files. each file has 5000 lines. each lines
is delimitted by pipes |
At this point I use split on the pipes to create an array, and then pull the
array into an existing table.
I have been successful in setting up the looping through the individual files,
and opening a file. Than I run into a problem. If I am stepping through the
code, the split works, and the places in the array assign to field values
into a table in access.
However, when I try to execute the code normally, I get the following.
Runtime error "9"
subscript out of range.
If I try to debug, I get the same thing into infiniti (or close to it, it
seems).
Sample line from txt file.
START SAMPLE:
263862|GA4054|lastname, mr. & Mrs.|1111111111|22222|3333|\\pcimg\smeadlnk\
Images\Vol_007\PP002M5P.tif|Purchased Documents (legal)|12/21/2000 11:40:20
AM|lastname, first & first|some company, inc.
END SAMPLE:
START CODE:
Private Sub Command1_Click()
Call ReadFiles
End Sub
Public Sub ReadFiles()
Dim strPath1 As String
Dim strPath2 As String
Dim strFile2 As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strFile As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("Tbldatfiles", dbOpenDynaset, dbAppendOnly)
strPath = "C:\Monitronics\*.dat"
strPath2 = "C:\Monitronics\"
strFile = Dir(strPath)
Debug.Print strFile
Do While strFile <> vbNullString
strFile2 = Left(strFile, (InStr(strFile, ".") - 1)) & ".txt"
Debug.Print strFile2
rst.AddNew
rst.Fields("datfiles") = strPath2 & strFile
rst.Fields("txtfiles") = strPath2 & strFile2
rst.Update
FileCopy strPath2 & strFile, strPath2 & strFile2
Call getrecords(strPath2 & strFile2)
strFile = Dir()
Loop
rst.Close
End Sub
Public Sub getrecords(sourcefile As String)
Dim iFile As Integer
Dim strLine As String
Dim strfld() As String
Dim db1 As DAO.Database
Dim rst3 As DAO.Recordset
Dim sd As String
Dim n1 As Integer
Dim n2 As Integer
Dim varRange As Variant
Dim myvalue
sd = "|"
Set db1 = CurrentDb()
Set rst3 = db1.OpenRecordset("Tbldatrecords", dbOpenDynaset)
iFile = FreeFile
Open sourcefile For Input As #iFile
Do While EOF(iFile) = False
Line Input #iFile, strLine
varRange = Split(strLine, sd, -1)
rst3.AddNew
'used for initial testing 'rst3.Fields("sourceline") = strLine
'used for initial testing 'rst3.Fields("datfilename") =
sourcefile
'used for testing 'myvalue = varRange(0)
'used for testing 'Debug.Print myvalue
rst3.Fields("ARcustomer") = varRange(0)
rst3.Fields("Accountsite") = varRange(1)
rst3.Fields("Subscribername") = varRange(2)
rst3.Fields("phone#") = varRange(3)
rst3.Fields("contract#") = varRange(4)
rst3.Fields("dealernumber") = varRange(5)
rst3.Fields("documenttitle") = Mid(varRange(6), (InStrRev
(varRange(6), "\") + 1))
rst3.Fields("documenttype") = varRange(7)
rst3.Fields("arcustomername") = varRange(9)
rst3.Fields("dealername") = varRange(10)
rst3.Fields("file") = varRange(6)
rst3.Fields("sourceline") = strLine
rst3.Fields("datfilename") = sourcefile
rst3.Update
Loop
Close iFile
End Sub
END CODE:
Thank you all for any correction or guidance. I think I have looked at this
so long I cannot see what I am doing wrong.
N8